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.
