|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.
modified 30-Oct-14 12:35pm.