Uncertain on What to Use as Foreign Key of a Table
-
Hi all, I am trying to design a database to hold data for a job portal. Basically I have a database called SiteUsers which contains five tables (UsersTable, JobSeekersTable, ResumesTable, EmployersTable, and JobsTable). The following are the table schema for UsersTable, ResumesTable, and JobsTable. The UsersTable has a Primary Key (PK) of UserID and a Foreign Key (FK) of Username. The ResumesTable has a PK of ResumeID and the JobsTable has a PK of JobID. The JobSeekersTable has a PK of Username. The EmployersTable has a PK of Username. The question I have is should the JobSeekersTable FK also be Username, or ResumeID, or a composite of Username and ResumeID. I think that the FK should be Username because it's unique but because it's not the primary key of the JobsTable I am uncertain. Also I did not want to use ResumeID as the FK in the JobSeekersTable because although it will be unique a job seeker can have more than one resume. Whether you agree or disagree with my assumption that Username should be the PK and FK in the JobSeekersTable, please elaborate your answer . Thanks in advance for your help.
-
Hi all, I am trying to design a database to hold data for a job portal. Basically I have a database called SiteUsers which contains five tables (UsersTable, JobSeekersTable, ResumesTable, EmployersTable, and JobsTable). The following are the table schema for UsersTable, ResumesTable, and JobsTable. The UsersTable has a Primary Key (PK) of UserID and a Foreign Key (FK) of Username. The ResumesTable has a PK of ResumeID and the JobsTable has a PK of JobID. The JobSeekersTable has a PK of Username. The EmployersTable has a PK of Username. The question I have is should the JobSeekersTable FK also be Username, or ResumeID, or a composite of Username and ResumeID. I think that the FK should be Username because it's unique but because it's not the primary key of the JobsTable I am uncertain. Also I did not want to use ResumeID as the FK in the JobSeekersTable because although it will be unique a job seeker can have more than one resume. Whether you agree or disagree with my assumption that Username should be the PK and FK in the JobSeekersTable, please elaborate your answer . Thanks in advance for your help.
- You don't actually define a foreign key on a table 1) You may want to define a unique index on UsersTable, but don't use it as a foreign key 2) The other tables should be related to UsersTable on UserID, not on Username
-
Hi all, I am trying to design a database to hold data for a job portal. Basically I have a database called SiteUsers which contains five tables (UsersTable, JobSeekersTable, ResumesTable, EmployersTable, and JobsTable). The following are the table schema for UsersTable, ResumesTable, and JobsTable. The UsersTable has a Primary Key (PK) of UserID and a Foreign Key (FK) of Username. The ResumesTable has a PK of ResumeID and the JobsTable has a PK of JobID. The JobSeekersTable has a PK of Username. The EmployersTable has a PK of Username. The question I have is should the JobSeekersTable FK also be Username, or ResumeID, or a composite of Username and ResumeID. I think that the FK should be Username because it's unique but because it's not the primary key of the JobsTable I am uncertain. Also I did not want to use ResumeID as the FK in the JobSeekersTable because although it will be unique a job seeker can have more than one resume. Whether you agree or disagree with my assumption that Username should be the PK and FK in the JobSeekersTable, please elaborate your answer . Thanks in advance for your help.
A foreign key is a primary key from another table. The "username" in the Users table (calling it userstable is redundant, all those things are tables and not chairs with little chance of confusing those) is not a foreign key - but it might be candidate-primary key. Also, the artificial autonumber may be unique, but it does not mean that one can assign it blindly to be the primary key without asking oneself what the user will be using to uniquely identify a record. You should look into normalization.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
A foreign key is a primary key from another table. The "username" in the Users table (calling it userstable is redundant, all those things are tables and not chairs with little chance of confusing those) is not a foreign key - but it might be candidate-primary key. Also, the artificial autonumber may be unique, but it does not mean that one can assign it blindly to be the primary key without asking oneself what the user will be using to uniquely identify a record. You should look into normalization.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]