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.
