I want to link several tables to a many-to-many(m2m) table.
One table would be called location and this table would always be on one side of the m2m table.
But I will have a list of several tables for example:
- Cards
- Photographs
- Illustrations
- Vectors
Would using GUID's between these tables to link it to a single column in another table be considered 'Good Practice'? Will Mysql let me to have it automatically cascade updates and delete? If so, would multiple cascades lead to an issues?
UPDATE
I've read that GUID (a hex number)
Generally takes up more space in a database and slows queries down. However I could still generate 'unique' ids by just having the table initial's as part of the id so that the table card's id would be c0001, and then Illustrations be I001. Regardless of this change, the questions still stands.
Best Answer
By default databases don't support this kind of relationship (as you've drawn it). I have used it before without enforcing the foreign key that goes to multiple different tables. I had to take care of all the relationships in code. Generally it's considered a bad idea.
The "right" way is to have a
CardLocation
,PhotographLocation
, etc. tables each with its own Guid primary key (e.g.CardLocationId
). If you happen to need to hang common data off each relationship, then you create another table calledLocationRelationship
and you make the primary key of the relationship tables (e.g.CardLocationId
) also a foreign key that references theLocationRelationshipId
. So, each record inCardLocation
has a corresponding record inLocationRelationship
. That way you can enforce all the relationships in the relational database. Unfortunately it's a lot more complicated.Edit
After thinking a bit more, the other way to do it is to have
Card
,Photograph
, etc., all derive from a common base (let's call itLocatable
). So you create a new table calledLocatable
with primary keyLocatableId
. ThenCardId
is both a primary key and should also be a foreign key that referencesLocatableId
, andPhotographId
is a foreign key toLocatableId
as well. Then you just have one many-to-many table calledLocatableLocation
with foreign keys toLocatableId
andLocationId
.That's fewer tables and seems to imply the intent better.