 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / April 2005Composite Primary Key (CPK) and relationships
I have a table TBL_Tech that has its PK as the TechID. This is the FK for two other tables. I just found out that after a tech is terminated, the supervisor will then assign his "old TechID" to the next "new hire" that comes along. Now that ID is no longer unique. In the tables that have the TechID as its FK, there is no other field that relates to the TBL_Tech. My question: If I add the status field to the PK in the TBL_Tech, then how do I relate this to the other tables? I also have the option to use the "PayrollID" (which is unique to each employee regardless if they are active or not) but this is not known by all the users of the db. Does anyone have a suggestion as to what I should use? Thanks in advance, Lee Use the PayrollID or an autonumber as your primary key and add a status field to your TBL_Tech. Who has to know the values? I use autonumber primary keys all the time and nobody knows the values. Employees whould be selected from a combo box on forms.  SignatureDuane Hookom MS Access MVP -- >I have a table TBL_Tech that has its PK as the TechID. This is the FK for > two other tables. I just found out that after a tech is terminated, the [quoted text clipped - 13 lines] > > Lee I have a similar situation that may be arising in the future. My question about it is how to re-establish related records when the foreign key is changed. Old records will contain the old PK as their FKs. How to make them contain the new PK? > Use the PayrollID or an autonumber as your primary key and add a status > field to your TBL_Tech. [quoted text clipped - 20 lines] > > > > Lee Hi Bruce, You can add the new field that will be the new PK to the main table and populate the values (Autonumber being the easiest, but populate with whatever the new key will be. Then, add a similar field to the related table(s). Update the new field values in the related tables based on the new field values in the main table (tables are linked by the old, existing keys in the query). Then, your new keys match and you can delete the old relationship and define the new one. At that point you can delete the old key fields, or keep them if you need them for future ref or something. HTH, Ted Allen > I have a similar situation that may be arising in the future. My question > about it is how to re-establish related records when the foreign key is [quoted text clipped - 25 lines] > > > > > > Lee Thanks for the great response... SignatureDuane Hookom MS Access MVP -- > Hi Bruce, > [quoted text clipped - 55 lines] >> > > >> > > Lee My pleasure, thanks Duane. -Ted Allen > Thanks for the great response... > [quoted text clipped - 57 lines] > >> > > > >> > > Lee > I have a table TBL_Tech that has its PK as the TechID. This is the FK > for two other tables. I just found out that after a tech is > terminated, the supervisor will then assign his "old TechID" to the > next "new hire" that comes along. Now that ID is no longer unique. Well, if it's the Primary Key then it has to be unique. To me, the question is what is a "Tech"? If it's a person (with a name and an address and a car and an employment record etc) then it has to be a new ID for every person, no re-allocations. If it's a role (like Dept 32 Undermanager, or NorthWest Sales Team Leader) then it has a new person stuck into it with every change of personnel. Or perhaps you need two tables, one for the person, one for the post. In any case, you as the designer need to make up your mind. If you have a table of people, then the db model is very similar to what happens at work: you line up all the projects and say, "All of you that used to belong to Jack, now belong to Eric". In the database it's a straight update query: UPDATE Projects SET Owner = "Eric" WHERE Owner = "Jack" If you have a table of roles, then you don't have to do anything, because all of the Sales Team Leader's projects still belong to the Sales Team Leader. It's up to you... Hope that helps Tim F Thanks, that helps a lot. The reason I didn't think I could use the Autonumber was because each time a new Tech is added, I thought they would need to know the ID, but I guess I just needed a little more sleep that day. AFter reading your response, Duane, I quickly realized that I could use an Autonumber. My supervisors aren't all that original. They will re-use the TechID whenever someone is "let go". Unfortunately I have to use whatever info they give me to work with. I will use a little of everyone's responses. Thanks again, Lee |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.