Is it bad to have M:M relationships defined where 1:M is being used?

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.

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

StackExchange Database Administrators Q#202654, answer score: 4