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.

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.