Best Practice for relationships shared among multiple tables

Question

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?

Answer 1

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

Answer 2

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?

Answer 3

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.

Answer 4

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

PeopleTable

PeopleID
NoteID
.....

DogTable

DogID
NoteID
...

NoteTable

NoteID

NoteDetailTable

NoteDetailID
NoteID
NoteText
...

Answer 5

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

masterId
id
1  
2  
3  
4  

notes
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.