Is it Considered Good SQL practice to use GUID to link multiple tables to same Id field?

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.

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.

I have comments on your current data model.

1 – You should have a separate m-m to each relationship and not 1 table as you currently show in the model. One reason is that an occurence of the m-m table could be for one or more FK values. A cascade delete may delete several rows in the m-m table that hold FK values you don’t want to delete.

2 – Your relationship from the one-side to junction table is probably optional from the one-side not mandatory as you model it. Optional relationship causes a nullable FK to be created and this prevents cascading from taking place by removing the non-null value and replacing it with null (if this is what you want).

3- Your relationship from the m-m to the Location table should probably not be mandatory as this would delete locations on the delete of a Card for example. You may want this to be optional (see 2 above).

4 – Do you really need m-m? Are you sure? Your business is not clear from the question, but maybe you only need a 1-m. Prove it to yourself by an example before you model it.

5 – You may avoid having 4 tables that would probably have the same columns by creating 1 Resource table with a type column. The type would be:Card, Illustration, etc. This way you save 3 tables and 3 junction tables.

6 – It is not good to use plural table names. Microsoft used to do this, but it is not good! Prefer singular table names. Plural names are good for naming collections.

Now as per your question about GUID, you can use GUID but it will make testing very difficult. It would also make finding a row using GUID a nightmare for the user if that is an expected function in your system. Performance may suffer for huge databases. Consider using int type when possible. Coding Horor – GUID as a PK Pros and Cons.

a GUID is usually represented as a hexadecimal string, but it’s more appropriate to store it in a binary format (rather than varchar) if space is a consideration; it usually is in an RDBMS.

There’s nothing wrong with using a GUID as a foreign key, or primary key, for that matter. If you ensure that your tables have the correct indices, it shouldn’t affect performance at all.

I don’t see anything inherently wrong with this design, but you need to note that you are implicitly associating a Card, Illustration, Photograph and Vector by having them share a key.

Your location will have multiple sets of attributes. Each set will contain zero or one Card, Illustration, etc. Are these Card and Illustration any more related than any other card or illustration that is linked to the location? If not then your schema does not model your data accurately.

What you’ve got here isn’t a ‘many-to-many’ relationship. The Location can correctly have many Vectors (and so on), but each Vector can only have one Location. That’s just a many-one relationship. If that is what you need, then you can do it far simpler: the Location table has the primary key, and the other tables simply have a foreign key back to the Location table, without a unique constraint on that foreign key column.

Finally, if you are collecting a number of attributes about a location, why not just have a LocationAttribute table which stores

LocationId
AttributeTypeId
Value (varchar or whatever)

and then a defined set or table of attribute types – 1 = Card, 2 = Photograph and so on.

The purpose of Guid is to ensure unique values across distributed databases. For example if you are generating FooBars in 2000 separate databases you would use a Guid to guarantee they are unique without needing to cross check 2000 databases to see if the value exists. The Guid algorithm has the mac address baked into the algorithm to ensure no 2 machines generate the same value.

The downside of a Guid is it’s big. That means indexes will be bigger and may not fit into memory. That means matching the Guid value is slow. Only use a Guid when you need a guid.

If the data is generated in a central location (not distributed) you will want to use an integer. Make it auto_increment. This will keep things small on disk and memory. And matching is faster.

You are trying to use 1 mapping table for all the tables. But with that design you can’t put a foreign key in the mapping table. Instead you should have a separate mapping for each.

You “could” create a Master table with Cards, Vectors, Illustrations, etc as sub-types via foreign key. Then use the master table in the mapping to location. But only do that if the things really are sub-types of a master type.

Filed under: softwareengineering - @ 18:50

Thẻ: data-modeling, modeling, mysql, sql

Thiết kế website giá rẻ

Danh mục

Is it Considered Good SQL practice to use GUID to link multiple tables to same Id field?

UPDATE

Edit