4

In general, would it be a bad idea to design a database with M:M relationships where ever things have a relationship, instead of using, say, 1:M instead?

My reason for doing this is because later on if I decide that I no longer want a 1:M relationship but want a M:M instead, I don't have to modify the database at all; all I would have to do is change my business logic to handle "many" instead of just "one".

For instance, say I have these tables:

  • user
  • image

A user should only have one profile image, however I handle the relationship with a table like this:

  • user_profile_image

That table would have user_id and image_id foreign keys. The alternative would be to embed a image_id foreign key in the user table.

In my business logic, I explicitly only allow one profile image to be set for a user. But as I stated before, my theory is that later on if I want to allow multiple profile images for a user, the database doesn't need a single change and I can just update my business logic to handle many instead of one.

Is this a good idea, or is it frowned upon? Please explain? I have been doing this all throughout my app, so I don't have a single 1:M relationship defined in the schema layer, though my business logic has basically 80% 1:M relationships.

4

It would work - but it would be relying on the application logic to maintain relational integrity. You probably want to limit the relationship to 1:1 initially at the database level - which can be done without sacrificing flexibility.

Normally, the following relationships can be handled in the following ways:

  • 1:1 - data can be maintained in the same table
  • 1:many - "many" data is in a separate table, and uses the primary key from the "1" table to link the many back to the one
  • many:many - a "bridge" table is used, with the primary keys from both of the "many" tables, so each row from table A can be tied to many rows from table B, and each row from table B can be tied to many rows form table A.

You can "downgrade" from 1:many to 1:1, or from many:many to 1:many or to 1:1, by using unique constraints.

Let's take your example: You've got user, image, and user_profile_image. Currently, you want to have a 1:1 relationship - each user has (at most) one profile image, and each profile image is tied to (at most) one user.

Let's assume that user_id is the primary key of user, and image_id is the primary key of image. In user_profile_image, as you note, those fields are foreign keys, linking each row to a specific user and image.

The table would look something like this:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
     )

Therefore, for a many-to-many relationship, there could be many rows with the same user_id, connecting that user to many different images, and many rows with the same image_id, tying that image to many different users.

If we want to change this many-to-many set-up, restricting it so it only allows a 1:1 relationship, we change our table, adding unique constraints to both of our foreign keys:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__user_id (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__iamge_id (image_id)
     )

(Actual syntax may vary for your DBMS)

With these constraints in place, each user_id can only appear in one user_profile_image row, as can each image_id - a 1:1 relationship. So, each user can have only one image, and each image can be tied to only one user.

Note that you must have both UNIQUE constraints for this to work. I you had one UNIQUE constraint that covered both user_id and image_id, then users could have multiple image and images could be tied to multiple users. Such a constraint would only prevent the same user/image relationship from appearing twice in the table. That can be a useful constraint to have, admittedly; but it won't do what you want.

Now, if you get to a point where you want a user to be able to have multiple profile images (a 1:many relationship), you would drop the UNIQUE constraint on the user_id. At that point, user_id could appear in multiple user_profile_image rows, but each image could still only be tied to one user.

If, on the other hand, you still want each user to have one profile image, but you find that 90% of your users are using the same three profile images, you may want to keep the UNIQUE constraint on user_id, and remove the UNIQUE constraint on image_id. Then, each user would still have one image - but you could just keep one copy of each of those 3 images, and tie all the users who want one to one instance of the image, saving a fair amount of space. you've flipped which table is the one and which is the many, but this is still a 1:many relationship.

Finally, if you want users to be able to have multiple images, and images to be used by more than one user (a true many:many relationship), drop both the user_id and the image_id UNIQUE constraints.

Note: I would only do things this way if you genuinely see a possibility that the relationship could change. Someone looking at the basic table layout might assume that the relationship is many-to-many (since you have a bridging table), and miss the UNIQUE constraints, leading them to write code that won't work. It's even possible that someone will assume the UNIQUE constraints are a mistake created by someone in the past, and remove them without understanding the full implications.

Also, you do add somewhat to the overhead required to store your DB on disk, having additional indexes that you wouldn't need if you used the "normal" construct for a 1:1 or 1:many relationship. Also, queries will be a little bit more expensive. In most cases, this wouldn't be a big deal; but, if you're dealing with billions of data rows, it may be worth consideration.

I wouldn't say it's actively wrong to do things this way, but it can be over-engineering your solution, planning for eventualities that never appear. Like I said above - unless you can see a real likelihood that you will need to "upgrade" a 1:1 or 1:many relationship in the future, I'd stick with the simple solutions.

  • Thanks for the in-depth answer. In regards to your last point, I have to admit I have a bad habit of trying to over-engineer things to a point of perfection for future use cases that will most likely never appear. This helped me realize I should probably just keep the base cases, because the majority of them will never need to change. – Lansana Mar 30 '18 at 0:44
  • This answer would be better if it used the term foreign key. – Walter Mitty Mar 30 '18 at 7:35
  • @WalterMitty - ??? Right above the code block: "if our two foreign keys...." (and the code block itself makes it clear that user_id and image_id are foreign keys). I didn't think it necessary to call them foreign keys in every reference. Could you clarify your suggestion? – RDFozz Mar 30 '18 at 14:31
  • Fair enough. I noted that in the first place where you describe 1:many and many:many, you call them primary keys, where they are really copies of the primary key. By calling them primary keys, you could confuse a beginner into believing they must be unique. I know, and you know, that they don't have to be unique in the context of linking. But the begminner maybe doesn't know that. – Walter Mitty Mar 30 '18 at 14:55
  • @WalterMitty - I get your point, I've modified the section above the original code fragment, so I show the table without and with the UNIQUE constraints, and emphasize that normally those foreign keys would not be unique. Thanks for the critique! – RDFozz Mar 30 '18 at 15:25

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.