Many-to-Many with Primary

Question

I am working on a database that should represent computers and their users. Each computer can have multiple users, and each user can be connected to multiple computers, so this is a classic many-to-many relationship. However, there should also be a concept of a "primary" user. I should be able to join the first user to list all computers with their main users. I'm not sure the best way to structure this in a database:

1) As I am doing now: linking a table to a boolean column IsPrimary. The connection requires something like ON (c.computer_id = l.computer_id AND l.is_primary = 1). It works, but it does not feel right, because it is not easy to limit data to only one primary user on a computer.

2) The computer table field, which points directly to the user row, all the rows in the user table are not primary users. This is better than a single primary restriction on a computer, but makes it difficult to get a list of computer users.

3) A field in the computer table that refers to a row in the binding table. Feels weird ...

4) Something else?

What is the “relational” way of describing this relationship?

EDIT: @Mark Brackett: The third option seems a lot less strange to me when you showed how good it can look. For some reason, I didn’t even think about using a complex foreign key, so I thought that I would have to add an identifier column in the link table for it to work. Looks great, thanks!

@ j04t: Cool, I'm glad we agreed on # 3 now.

Answer 1

Option 3, although it may seem strange, is closest to what you want to model. You would do something like:

User { UserId PRIMARY KEY (UserId) } Computer { ComputerId, PrimaryUserId PRIMARY KEY (UserId) FOREIGN KEY (ComputerId, PrimaryUserId) REFERENCES Computer_User (ComputerId, UserId) } Computer_User { ComputerId, UserId PRIMARY KEY (ComputerId, UserId) FOREIGN KEY (ComputerId) REFERENCES Computer (ComputerId) FOREIGN KEY (UserId) REFERENCES User (UserId) } 

Which gives you 0 or 1 primary user (PrimaryUserId can be null if you want), which should be in Computer_User. Edit: if the user can only be primary for one computer, then UNIQUE CONSTRAINT on Computer.PrimaryUserId will provide this. Please note: there is no requirement that all users be primary on some computer (this would be a 1: 1 ratio and would require that they be in the same table).

Edit: some queries to show you the simplicity of this design

 --All users of a computer SELECT User.* FROM User JOIN Computer_User ON User.UserId = Computer_User.UserId WHERE Computer_User.ComputerId = @computerId --Primary user of a computer SELECT User.* FROM User JOIN Computer ON User.UserId = Computer.PrimaryUserId WHERE Computer.ComputerId = @computerId --All computers a user has access to SELECT Computer.* FROM Computer JOIN Computer_User ON Computer.ComputerId = Computer_User.ComputerId WHERE Computer_User.UserId = @userId --Primary computer for a user SELECT Computer.* FROM Computer WHERE PrimaryUserId = @userId 

Answer 2

Edit - I did not think about it at first three times ... I vote for - (Decision number 3)

Users

 user id (pk) 

Computers

 computer id (pk) primary user id (fk -> computer users id) 

Computer users

 user id (pk) (fk -> user id) computer id (pk) (fk -> user id) 

This is the best solution I can think of.

Why I like this design.

1) Since this is a relationship related to computers and users, I like the idea of ​​associating a user with multiple computers as the primary user. This may not happen when this database is used.

2) The reason I don't like having primary_user in the link table

  (computer_users.primary_user_id fk-> users.user_id) 

means that the computer does not have several primary users.

Given these reasons, solution # 3 looks better, since you will never encounter some of the possible problems that I see with other approaches.

Problem Solution 1 - It is possible to have several primary users on the computer.

Problem 2 - the computer connects to the main user when the computer and the user are not connected to each other.

 computer.primaryUser = user.user_id computer_users.user_id != user.user_id 

Problem of the 3rd solution. That seems weird, doesn't it? Other than that, I can't think of anything.

Problem Solution 4 - I can't think of another way to do this.

---

This is the fourth edit, so I hope this makes sense.

Answer 3

Since the primary user is a function of the computer and the user, I would like to go with your approach to the fact that primaryUser is a column in the link table.

Another option I can think of is having a primaryUser column directly on the computer table itself.

Answer 4

I would create another PRIMARY_USERS table with a unique computer_id function and create foreign keys computer_id and user_id USERS.

Answer 5

One solution 1 or 2 will work. At this stage, I asked myself the question of who is easier to work with. I used both methods in different situations, although usually I would use a flag in the link table, and then force a unique restriction on computer_id and isPrimaryUser, thereby you guarantee that there will be only one primary user on each computer.

Answer 6

2 feels good, but I would test 1, 2, and 3 for performance on the types of queries that you usually run and the types of data volumes that you have.

As a rule, I am inclined to believe that where there is a choice of implementations, you should look at your query requirements and design your scheme so that you can get the best performance and resource use in the most common case.

In a rare situation when you have equally common cases that involve opposite implementations, use the Occam razor.

Answer 7

We have a similar situation in the application in which I work, where we have Accounts that can have many clients, but only one must be the Primary client.

We use a link table (like yours), but we have a Sequence value in the link table. The primary user is the one who has Sequence = 1. Then we have an index in this link table for AccountID and Sequence to make sure that the combination of AccountID and Sequence is unique (thereby ensuring that no two clients can be Primary in the Account) . So you will have:

 LEFT JOIN c.computer_id = l.computer_id AND l.sequence = 1