SQL Modeling – Is Using GUID to Link Multiple Tables Good Practice?

data modelingmodelingMySQLsql

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?

Diagram example of proposed structure

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 called LocationRelationship and you make the primary key of the relationship tables (e.g. CardLocationId) also a foreign key that references the LocationRelationshipId. So, each record in CardLocation has a corresponding record in LocationRelationship. 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 it Locatable). So you create a new table called Locatable with primary key LocatableId. Then CardId is both a primary key and should also be a foreign key that references LocatableId, and PhotographId is a foreign key to LocatableId as well. Then you just have one many-to-many table called LocatableLocation with foreign keys to LocatableId and LocationId.

That's fewer tables and seems to imply the intent better.

Related Topic