Best practice for relationships spread across multiple tables
I run into this design problem all the time, and so far I'm not happy with my solution. The problem is this:
I have two or more entities like "People" and "Dogs" and both of them are related to the "Notes" table that stores the post field and some metadata about the post, like maybe the author.
1) The first option is not to use a foreign key. This way I can store FK as peopleId or dogId (whatever it is) in the same general FK field as fkId. Then I would store the tableId in a different column - hopefully get the table id from the RDMS metadata, but you can also have a dirty hack and explicitly make the table with tables you have to manually update.This is really sloppy and I just mentioned it for completeness ...
2) Clone the Notes table for every table it needs like PeopleNotes, DogNotes, CatNotes, etc. This poses a rather serious normalization problem.
What are other people doing in these situations?
source to share
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 relationships stored 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 to the convention of using the alphabetical order of my models to refer to relationship tables.
source to share
How about two new tables - Dog2Notes and People2Notes? Dogs, People, and Notes are all with Keys that are related to each other. Dogs and humans can have more than one note, and notes can be split.
If dogs and humans can only have one note, then add NOteID to each of these tables?
source to share
Wouldn't it be a better solution than the one currently suggested to have a table of core IDs?
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 will make scaling easier because if you need to add a new entity type (like cat) you won't need to add another table (cat_note), this is especially useful if you add a new type of note (like a book), because then you you will need to add new tables for all entity types (person_book, dog_book, etc.). Finally, you can directly link any entity table to the notes table.
The only "problem" is that you will need to run a procedure that will automatically add a new record to the masterId table when a new record is added to the entity table and associate it with a new record.
PS I know this answer is like nine months after the fact. Just happened to this while doing other research and thought I was putting my two cents in.
source to share