2

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.

Improve this question
13
  • 3
    On a side note, an INT cannot hold a GUID. So if you need to use an INT instead of a 128-bit field (sizeof GUID), I'd called it just ID.
    – Andrew T Finnell
    Nov 13, 2012 at 1:35
  • @AndrewFinnell Thanks, I just made the diagram as fast as I could, I didn't actually check the vartype too much. Although, I think you mean instead of an INT I would need a Varchar??
    – Mallow
    Nov 13, 2012 at 1:38
  • 4
    There is only 1 reason to use a guid for id's (in any technology not just databases) so you can identify that thing as unique globally, which specifically means across multiple computers. If your data needs to be related from a central server to client machines, then it's valid to use a guid. If your data needs to be related from across multiple databases where they could be created on either side (one for logins and one for profiles where either could be created first). Otherwise save the disk space, memory space, and search/insert times by using smaller data types.
    – Jimmy Hoffa
    Nov 13, 2012 at 2:15
  • @Mallow I specifically meant you need 128-bits. If you want to be efficient you can use CHAR(16) collate utf8_binary, BINARY(16), or a VARCHAR(36) if you want to use UUID() from mysql.
    – Andrew T Finnell
    Nov 13, 2012 at 2:16
  • @Mallow further reading: sqlmag.com/article/performance/…
    – Jimmy Hoffa
    Nov 13, 2012 at 2:29

5 Answers 5

Reset to default
4

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.

Improve this answer
1
  • I like your edit ^_^ A new way to do it.
    – Mallow
    Nov 13, 2012 at 23:07
2

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.

Improve this answer
4
  • Lots to think about. I can answer number 4 with the most confidence in that yes it is m-m. One picture might be located in two different locations. (Back up, original, "customer's" computer) and a location (aka most probably a folder) can harbor many pictures. #5 would solve that problem easily. As for my question #1 indicates that if I combined the fk then cascading would delete things that I wouldn't want deleted, but wouldn't that be the point of using GUID. I guess I wanted to solve having four tables vs one and it seems that either I use GUID or put a type (#5) on my original table.
    – Mallow
    Nov 13, 2012 at 4:28
  • "but wouldn't that be the point of using GUID", using GUID will not prevent cascade effects. One way is to have all FKs optional and ensure that only 1 is filled in any row. This way cascade delete will not affect more than 1 child row of m-m side. The most clean way I see to solve the 4 tables problem is go with #5 with any choice of Primary Key datatype.
    – NoChance
    Nov 13, 2012 at 13:15
  • Why no plural table names? Ruby's ActiveRecord also uses plural table names. Is it the irregularity of English plurals, e.g. person / people ?
    – kevin cline
    Nov 13, 2012 at 16:10
  • In reading an association between two tables in an ERD (for example, Customer and Order) you must prefix the statement with the word "Each", so you read the association as "Each Customer places 0,1, or more Order(s)). If you name your table Customers then Each would be odd. Also in some cases your table name becomes your class name and finally, it is stated that you should use singular nouns in IDEF1X - December 21, 1993 - Section:3.1.2: Entity Syntax3.1.2. Having said that, its still a choice.
    – NoChance
    Nov 13, 2012 at 20:57
1

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.

Improve this answer
7
  • Does this mean there is no issue binding multiple tables to one column.
    – Mallow
    Nov 13, 2012 at 1:56
  • 1
    It most definitely does effect performance. Putting a GUID into your primary key means that you've got a huge index that's going to kill inserts and make queries against that index considerably slower. To put it into perspective, a table whose primary key is a GUID will have a (defaultly) clustered index 4 times the size of the same table with an integer. That said, there is one (and only one) reason to use GUIDs as identifiers (basically ever): To identify data that exists in multiple places. This is why guids exist, if your data is in one database only you don't need them for ids.
    – Jimmy Hoffa
    Nov 13, 2012 at 2:08
  • 2
    @JimmyHoffa The other reason is to be able to remove an entry and insert it back into the database at any point in time, as is. You won't have to worry about updating references or fixing the ID's. Think about doing an export of a db, clean the db, fill it, export the db, clean the db. Now you want to merge those two exports of the db. It's all still the same database but it makes merging easier.
    – Andrew T Finnell
    Nov 13, 2012 at 2:20
  • 1
    @AndrewFinnell good point, kind of the same thing, it exists in the database and outside of it (when removed).
    – Jimmy Hoffa
    Nov 13, 2012 at 2:22
  • Jimmy, it's 128 bits.
    – Paul Sanwald
    Nov 13, 2012 at 14:17
1

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.

Improve this answer
1
  • Woops, you pointed out another mistake I made in my communication. I do intend intend to have multiple locations for each attribute in each table. (Back-ups, derivative copies, etc...) So I really do mean many-to-many As for why I don't use an AttributeType is a good question. Thanks for asking, I will have to think about this. For some reason I instinctively want to compartmentalize each type.
    – Mallow
    Nov 13, 2012 at 3:57
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.