DbSchema - FK's to lookup tables 1

[1DMF]

Hi,

Is it correct practice to build physical links with FK's for fields that use 'lookup' tables?

In the DB schema software I am using you can create 'virtual' FK's which I have been using to define links between table columns and their lookup tables, but I was wondering if it is correct to define concrete links.

I'm assuming this isn't possible if the parent record doesn't have data in the column where the FK is defined.

So what is the correct way to show/define 'lookup' table relationships?

Thanks,
1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

It is better to create real FK's. I assume a "Virtual FK" only exists for the purposes of the diagram and is not enforced in the database. When you create a real foreign key, the database will prevent you from having orphaned child rows.

In the case of a lookup table, the only values you want in the main table is a lookup value to the lookup table or NULL. This is exactly what foreign keys were designed for.

Again, you need to be careful implementing FK's. If you allow users to delete lookup table values, you will get an error if the lookup value is used in the main table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Yes, I'm already having problems with creating them as the lookup table doesn't contain some of the codes that are in the child (main) record.

I certainly don't want to cascade deletes , but it might be worth cascading updates so any change to a lookup code is reflected in the main child record.

I assume I'm creating the FK the correct way round.

The lookup table is the parent and the column in the main record is the child?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

I assume I'm creating the FK the correct way round.

The lookup table is the parent and the column in the main record is the child?

Yes.

I certainly don't want to cascade deletes , but it might be worth cascading updates so any change to a lookup code is reflected in the main child record.

This depends on your table structure. I am a big fan of surrogate keys. What this means is that my primary key would be an int and the lookup table would have a "description" column. Like this:

[tt]
Colors
ColorId Description
------- -----------
1 Red
2 Green
3 Blue
[/tt]

The main table would store the ColorId (and not the description). In this situation, you would not need to cascade update because the user could change the description but not the ID, and it's the ID that is stored in the other table(s). Put another way... changing the description would not require an update to any other tables.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Make sense?

Yes perfectly...grr hindsight is a wonderfull thing!

But unfortunately even though I have a similar lookup table it's not enumerated like you have it :-(

ACC	Accounts
COM	Compliance
GEN	General
ITS	I.T. Support
MAR	Marketing
MEM	Membership

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download