How would you handle this and why??? (Common Data)

• dan_jf

  SSC-Addicted

  Points: 496

  More actions

  September 17, 2003 at 2:28 am

  #474574

  Hi,

  I have done something very similar for a large CMS here. It isn't for phone numbers, but for other content that relates to one of 2 different types of content. For the relationship I use...

  link_id int

  link_type int

  Where the link_type specifies which table the content relates to. Works extremly well, especialy when I am doing some global updates on the data.

  Make sure you put some strong checks on the data though, otherwise you can end up with major problems!

• stax68

  SSChampion

  Points: 11711

  More actions

  September 17, 2003 at 3:09 am

  #474578

  quote:

  ---

  But I wonder how many people have thought about making a single Phone Numbers table with maybe a field

  tbl_From

  to know which table to link back to

  ---

  I would avoid 'polymorphic keys' as they mean you can't use foreign key constraints, which I always insist on - both for built-in documentation purposes and a safeguard against the (unthinkable!) possibility of error in app or sp coding leading to data corruption).

  I would just go with multiple nullable foreign keys and a check constraint which permits only one to be populated, e.g.:

  sign(isnull(FK1,0))

  +sign(isnull(FK2,0))

  +sign(isnull(FK3,0))

  = 1

  This has always worked for me and is a pretty robust method. The extra coding required by normalisation is a non-issue in my book. Relational databases need joins to reassemble data - live with it!

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• cneuhold

  Hall of Fame

  Points: 3375

  More actions

  September 17, 2003 at 4:43 am

  #474598

  hi!

  did something similar for a sort of corporate directory application and used a desigen quite common in OOP: put up a master table for all different sort of "contacts" containing common data, detail tables for each contact type links to to that table in a 1:1 fashion.

  you could see this as a sort of "base class" and "descendant classes". however, all attributes like addresses and phone numbers are stored in separate tables that are linked to the "base class" table via n:m connector tables, to be able to reuse them.

  imagine you've got two persons that work for the same company and therefore have the same company addresses. in our model you'd end up with three rows in the "contacts" table (1 for the company, one for each person), 1 row in the "companies" table, 2 rows in the "persons" table.

  for addresses or phone numbers there would be separate tables called eg. "numbers" and "addresses", and link tables having a parent key pointing to the respective attribute table ("numbers" and "addresses") and to the "contacts" table.

  now the advantage of that model is that you'll have "clean" foreign keys all over your tables, address and number reuse is no problem due to the n:m link, contacts can be easily extended to "impersonate" multiple different types without changing the attribute table structure (eg. you could add a "businessunits" table and link it to the same addresses and phone numbers without doing anything else but just create the table).

  hope i could describe our approach properly,

  best regards,

  chris.