Question about foreign key replication

[Wesstun]

Hi everyone. I'm a beginner at MS SQL Server. This question is probably something simple but I just can't figure it out.

For the sake of simplicity, let's say I have two tables. Person_List and Position_List. The Person_List table would look like this:

Person_Name...P erson_ID...Othe r_Column
Aaron.......... .........1..... ............Bla h
Jack........... ..........2.... .............Bl ah
Ann............ ..........3.... .............Bl ah
etc . . .

The Person_ID is an integer identity/primary key that auto-increments as each new name is added to the table.

Now let's say the Position_List table would look like this:

Company_Postion ...PID
Engineer....... ...........5
Technician..... ..........2
Technician..... ..........4
etc . . .

This time the PID is a foreign key that connects to the Person_ID in the first table.

Here's my problem: Let's say that I wanted to manually add a new record to the Position_List table to record the information that Rob is an engineer. I'm not going to remember that his Person_ID/PID is 3. So I'd end up finding myself looking into the Person_List table to find his Person_ID. That takes time and I'd like to automate the process. What I'd really like the Position_List table to look like is this:

Company_Postion ...Person_Name. ..PID
Engineer....... ..........Frank ............... ..5
Technician..... .........Jack.. ............... ..2
Technician..... .........Jill.. ............... .....4
etc . . .

I want it to be so that after the Person_Name field is entered, that field will automatically be compared to the Person_Name fields in the first table and the Person_ID will be fetched and automatically entered into column 3. You might think it's bad database design to store the exact same field in two different tables and it probably is, but I'd like it that way to improve readability and make manual data entry easier. I guess it would also be possible to just make Person_Name the primary key instead of Person_ID, but the real tables that I'm working with are so big that I need the query performance boost that you get from using integer keys.

Can this be done with triggers or something similar? It seems like this sort of thing should be pretty easy but I'm stumped. Anyone got an idea?

Thanks!

[ck9663]

I stop on the part that you want to use the Name as the search key and even considering it as the primary key. Names are not always recommended to be the primary key. Because two people can have exactly the same name. Your table looks fine to me, and the Person ID is still the best key for it.

-- CK

[Wesstun]

CK, thanks for the response.

I know that Person_ID is the best key to use, and that's why I'm trying to use it. I just want to use it more easily. The first table already knows which ID belongs to which person, so I'm wondering if it's possible to set a trigger or something so that when I type in the name of the actual person in the second table, a quick query or something is run that fetches that person's ID and automatically enters it for me.

Basically, I want to make it so that whenever a new record (or a large chunk of records all at once) are added to the second table, they can be entered in such a way that I don't have to even deal with what the Person_ID and it's just taken care of for me in the background. Is something like this even possible or will I just have to take care of stuff like that on the front end data entry forms?

Anyways, I've given up on it for the weekend. Have a good one yourself.

[ck9663]

CK, thanks for the response.

I know that Person_ID is the best key to use, and that's why I'm trying to use it. I just want to use it more easily. The first table already knows which ID belongs to which person, so I'm wondering if it's possible to set a trigger or something so that when I type in the name of the actual person in the second table, a quick query or something is run that fetches that person's ID and automatically enters it for me.

Basically, I want to make it so that whenever a new record (or a large chunk of records all at once) are added to the second table, they can be entered in such a way that I don't have to even deal with what the Person_ID and it's just taken care of for me in the background. Is something like this even possible or will I just have to take care of stuff like that on the front end data entry forms?

Anyways, I've given up on it for the weekend. Have a good one yourself.

For the first part, you might be able to handle that from your front-end apps.

For the second part, you still can do that as long as the name is unique.

-- CK