Best Practice for relationships shared among multiple tables

I keep running into this design problem, and I'm not happy with my solution so far. The problem is this:

I have two or more entities, like People and Dogs, and they both have a relationship with a Notes table, that stores a message field and some meta data about the message like maybe the author.

1) First option is to not enforce the Foreign Key. That way I can store the FK like peopleId or dogId (no matter what it is) in the same generic FK field like fkId. Then I'd store the tableId in another column--one can hope to get the table id from the RDMS meta data, but you could also have a dirty hack and explicitly make a table full of tables that you'd have to update manually. This is really sloppy and I just mention it for completeness.

2) Clone the Notes table for each table that needs it, like PeopleNotes, DogNotes, CatNotes, etc. This creates a pretty major normalization problem.

What have other people done in situations like this?

13.10.2009 18:42:52

If these are your 'model' tables:

dog Table:
id | name | ...
1  | Rex
2  | Fido

people Table:
id | name | ...
1  | Bob
2  | Alice

notes Table:
id | text | ...
1  | A nice dog.
2  | A bad dog.
3  | A nice person.

You can have the relationships kept in separate tables:

dog_note Table:
dog_id | note_id
1      | 1
2      | 2

note_people Table:
person_id | note_id
1         | 3
2         | 3

I usually stick with the convention of using the alphabetical order of my models for naming the relationship tables.

12.02.2010 04:11:35

I prefer the idea of storing the owner of the note in two columns, one for ID, and one for class/table.

13.10.2009 18:45:37

How about two new tables - Dog2Notes and People2Notes? Dogs, People, and Notes are all entiries with Keys that relate to each other. Dogs and People can have more than one note, and notes can be shared.

If Dogs and People can only have ONE note each then add a NOteID to each of those tables?

13.10.2009 18:45:45

it really depends on how you query your data, but how about something like this, assumes there are multiple notes per person/dog:








13.10.2009 18:51:05

Wouldn't a better solution than the one currently suggested be to have a master id table?

dog Table:
id | name | masterId
1  | Rex  |  1
2  | Fido |  4

people Table:
id | name | masterId
1  | Bob  |  2
2  | Alice|  3


id | note       | masterId
1  | "Hi"       | 3
2  | "Good day" | 2

This would make scalability easier because if you needed to add a new entity type (e.g. cat), you wouldn't need to add another table (cat_note), it is particularly useful if you add a new note type (e.g. book) because then you would need to add new tables for all your entity types (person_book, dog_book, etc.). Lastly you could directly associate any entity table with the note table.

The only "issue" would be you would need to have a procedure run that would automatically add a new record to the masterId table when a new record is added to an entity table and associate it with the new entry.

P.S. I Know this answer is like nine months after the fact. Just happened upon this while doing other research and thought I put my own two cents in.

4.06.2010 21:32:23