One lookup table many relationships?

[sparlaman]

Is it bad design to have one generic lookup table related to more than one main table? see attached.

[SteveF]

It's not necessarily bad to have one lookup table -- I've done it many times myself. When I do, I usually don't establish a formal relationship as you've done; I let my lookup combos and joined queries handle the links explicitly instead.

The potential danger here is if you turn on referential integrity, especially cascade deletes. If you change or delete an entry in the lookup table, your main-table data could start disappearing too!

Steve

[sparlaman]

I see what you are saying. I never use update deletes. I'm a data horder. But if I use cascade updates will I run into errors?

[SteveF]

Cascade Updates should be okay. Cascade Deletes from the lookup table to any tables that use the lookup codes are the danger.

Steve

[sparlaman]

cool beans. thanks for your help.