One primary key linked to foreign keys in multiple tables...

[MeisoT]

Hello all,

I have one main table - Table1, and 3 separate tables - Table2, Table3, Table4 that need to be linked to Table1. If Table1 has an autonumber PK, can I use it to link to a FK in each of the three related tables?

This is my question in a nutshell - Is it good database design to link the SAME PK in one table to different FKs in seperate tables - or is there a better way to link the three tables to the main table?

Any advice would be greatly appreciated...

 

[BNPMike]

Normally a foreign key goes from one data item to the key of another (foreign) table. You seem to be linking the primary key to other primary keys. That looks like a one-to-one relationship which raises questions about your data model.

You are free to join whatever you like to whatever you like in the relational database model. Obviously you can't have an autonumber field in the other tables but apart from that you're ok.

Maybe you'd like to give us more detail...

 

[dhookom]

Zooloo88,
Assuming something like
tblEmployees
============
EmpID PrimaryKey

It would be ok to have tables like
tblEmployeeKids
===============
KidID
EmpID 'fk to tblEmployees

tblEmployeeVehicles
===================
vehID
EmpID 'fk to tblEmployees


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MeisoT]

If I'm following-up on the responses to my post incorrectly, please let me know - I'm not sure if I need to start a new post or reply to my own post - I'm a newbie!

dhookom, using your example showing employee table, employee kids table, and employee vehicles table I have a few questions -

1)Is this the best setup or is there a more efficient way to accomplish this?

2)Will doing it this way have a negative effect on performance or can I relate as many FKs as I need to one PK?

3)Can I still set kidID and VehID as autonumber fields? I see that BNPMike is saying that this is not possible.

Thanks

 

[dhookom]

1) I don't think there is a better method for setting up tables like this
2) There should be no negative effect on performance
3) KidID and VehID would both be the primary keys/autonumbers in those tables.

There are many arguments for and against using autonumbers as primary keys vs using natural keys (for instance using a combination of State and License Plate number for a primary key for the tblVehicles).

I almost always have an autonumber primary on all of my tables. I have never regretted this method.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

Zooloo

check out

http://www.alphabetcitydataworks.com

in the developers sections there is a paper 'Fundamentals of Relational Database Design' check it out, it will help.

(Thanks JeremyNYC for this link!)

Leslie