Results 1 to 6 of 6
  1. #1
    DaveG is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    4

    Primary key/ foreign key question

    Hello everyone, I just have a very basic question that seems to not be answered anywhere I could find online.

    I have two tables tblEmployee(name, address, phone, etc.) and tblEmployeeDates(this is for a start date, probation end date, termination date). I have employee ID as the primary key for tblEmployee, I also have employee ID as the primary key for tblEmployeeDates. I know it will work but is it good practice? I could make a field in tblEmployeeDates called dateRecordID or something like that as the primary key and use employee ID as a foreign key I suppose.



    Any input would be greatly appreciated.

    -Dave

  2. #2
    DaveG is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    4
    This is a project I'm doing for college. I'll also add that I have two other tables called tblPosition(this is a list of the different positions that exist in the company, the primary key is position ID) and tblEmployeePositions(this is a record of what position an employee holds, when they started the position etc., they can have many positions over time). So like I said I have positionID as the primary key for tblPosition logically. For tblEmployee position I have the option of employeeID or positionID as the primary key which are both primary keys in different tables. What would I do in this case?

    Thanks in advance.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am confused as to what your question is. As for post #1, the scenario you describe sounds appropriate. It all boils down to "Business Rules"; what data are you going to store and what are the relations.

  4. #4
    DaveG is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    4
    My question is can I have the same field as a primary key in two different tables? It would only make sense to look up employees by employee ID in tblEmployeeDates right?

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by DaveG View Post
    ..My question is can I have the same field as a primary key in two different tables?...
    That is a tricky question. In Access, yes, you can. However, I do not believe doing this, literally as you describe it, is what you are after.

    So let's just answer that question as, "No, you cannot."

    Give tblEmployeeDates its own Primary Key field/column. Include an additional column for employee ID. This additional column in tblEmployeeDates will be a Foreign Key to the Primary Key in your tblEmployee. In other words, FK to the PK field employee ID. So, employee ID is a field name in tblEmployee where said field is the PK and employee ID is a field name in tblEmployeeDates where said field is an FK.

    Best practice is to have one PK for each table. Some tables may have no FK fields where other tables have one FK field, and others have many FK fields. Multiple PK fields in a single table would be considered a compound key. It is not, from a purist's perspective, multiple Primary Keys. It is a compound key. If you are creating compound keys it is because you are managing multiple sources of relations. I will leave that at that.

  6. #6
    DaveG is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    4
    That's great! Thanks for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Primary Key and Foreign Key Question
    By chrisbas in forum Access
    Replies: 1
    Last Post: 05-07-2012, 04:01 PM
  2. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  3. Primary and foreign key in relationships?
    By Fatbot in forum Access
    Replies: 1
    Last Post: 04-12-2011, 10:11 AM
  4. import the foreign key into the primary key
    By vCallNSPF in forum Forms
    Replies: 3
    Last Post: 01-14-2010, 06:51 PM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums