Composite Primary Key (CPK) and relationships

[Lee Stafford via AccessMonster.com]

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

 

[Duane Hookom]

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.

 

[BruceM]

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?

 

[Tim Ferguson]

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

 

[Ted Allen]

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

 

[Duane Hookom]

Thanks for the great response...

 

[Lee Stafford via AccessMonster.com]

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

 

[Ted Allen]

My pleasure, thanks Duane.

-Ted Allen

Thanks for the great response...