what's the best way to capture relating one table to many other tables

Say I have four tables:

1. people

2. products

3. images

each of which can be joined in a 1 to many relationship with a fourth table

4. notes

I can add n notes to each row in any of the above tables.

The options of which I am aware:

a) have a separate notes table for each of the 3 tables (peoplenotes, productsnotes... etc.).

b) create a join table for each relationship:

peopletonotes (with a compound key of peopleid and noteid), etc.

c) in the notes table create a generic foreignkey which along with a tabletype can tell you which is the right parent table

notes table has: noteid, foreignkeyid, tabletype (which can be either: 'people', 'products' or 'images'), so you might have two rows in notes:

noteid = 1, foreignkey = 1, tabletype = 'people'

noteid = 2, foreignkey = 1, tabletype = 'products'

...etc. and so would know that if tabletype = 'people' the foreignkey joins to the primarykey of the people table.

I have assumed that option (a) is not the right way to go as i want all my notes to be in one place -- they are identical regardless of which table they are related to. (c) I like because it requires creating the fewest numbers of tables (as I could have notes associated with many different tables I would end up with a separate join table for each, but something about it makes me nervous. my gut tells me that (b) is the right answer but I'm hoping someone can weigh and provide a more informed opinion than that of my intestines.

thanks in advance.



Answer this question

what's the best way to capture relating one table to many other tables

  • Alastair Q

    There is a hybrid of one more variation on this which can give you the best of both worlds. Using views. Create a core Notes table and individual notes join tables. Then create views for each of the different kinds of joins. This will simplify querying the structure and because views can be indexed you can gain performance benefits. Using the views for updating is also possible, but it will require a bit of work with triggers.



  • julianenrique

    Yes, Exactly

    In my opinion (b) is the right answer.

    Thanks for your question and thanks to MSDN.



  • what's the best way to capture relating one table to many other tables