Foreign keys (1 Viewer)

[Bee]

Hi,

While designing a database, I came up with 3 different foreign keys in one table. I am afraid this is not right!

Is it possible to have this number of FK in one table or does that suggest a design flaw?

Any help will be very much appreciated.
B

 

[FoFa]

Any number of foreign keys can be had in a table, Primary keys are limited

 

[KeithG]

Having more than one Foreign key is not a design flaw at all.

 

[Bee]

alright. Thanks guys.

 

[WindSailor]

Having no more than two foreign keys in a table is more from a *purists* point of view of trying to obtain fifth normal form in your design.
This also applies to 'Look-up' tables used in a table, so that will dramatically change your design.
I believe taking it to that level really only helps for future changes to your application (adding functionality in your applications life cycle - existing table structure used is still functional and doesn't have to be changed - only new tables have to be created to add functionality), and may help in write conflicts.
The join statements get ugly in some cases...
I have only pushed one of my applications that far. But it helped me in my understanding of database normalization better.
What ever works for you...

 

[Bee]

Having no more than two foreign keys in a table is more from a *purists* point of view of trying to obtain fifth normal form in your design.
This also applies to 'Look-up' tables used in a table, so that will dramatically change your design.
I believe taking it to that level really only helps for future changes to your application (adding functionality in your applications life cycle - existing table structure used is still functional and doesn't have to be changed - only new tables have to be created to add functionality), and may help in write conflicts.
The join statements get ugly in some cases...
I have only pushed one of my applications that far. But it helped me in my understanding of database normalization better.
What ever works for you...

Hi WindSailer,

I have a house and phase tables and the relationship between the two is a many to one (many houses can be in one phase); however, both these tables take the same jobs that an employee does. So, there are 2 more tables employee and jobs.

The relationship is as follows:
one employee may do many jobs to one house and one house may get many jobs done by many employees.

If we take the relationship above and replace house with phase, it will work exactly the same. So, I thought of joining phase and house; however, I could not because the relationship between the two is a many to one.

So, I put employee_ID, House_ID, and Phase_No as FK in the jobs table.

 

[WindSailor]

Hello Bee,

The only suggestion I have is to make sure your tables abide by some level of the Normal Form rules (taking it to 3rd normal form is generally not a bad idea).
I think I can generically tell by your design you don’t have any repeating columns, and every column is required…

Here are a few links regarding examples of database normalization (each link contains multiple examples).

http://www.access-programmers.co.uk/forums/showthread.php?t=100211

http://www.utteraccess.com/forums/showflat.php?Board=8&Number=1203148

That should show you more than I could ever tell you about...

 

[Bee]

Hello Bee,

The only suggestion I have is to make sure your tables abide by some level of the Normal Form rules (taking it to 3rd normal form is generally not a bad idea).
I think I can generically tell by your design you don’t have any repeating columns, and every column is required…

Here are a few links regarding examples of database normalization (each link contains multiple examples).

http://www.access-programmers.co.uk/forums/showthread.php?t=100211

http://www.utteraccess.com/forums/showflat.php?Board=8&Number=1203148

That should show you more than I could ever tell you about...

Thanks for the links.