Many-to-Many with "Primary"

Question

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

1) As I'm currently doing: linking table with a boolean IsPrimary column. Joining requires something like ON (c.computer_id = l.computer_id AND l.is_primary = 1). It works, but it feels wrong because it's not easy to constrain the data to only have one primary user per computer.

2) A field on the computer table that points directly at a user row, all rows in the user table represent non-primary users. This represents the one-primary-per-computer constraint better, but makes getting a list of computer-users harder.

3) A field on the computer table linking to a row in the linking table. Feels strange...

4) Something else?

What is the 'relational' way to describe this relationship?

EDIT: @Mark Brackett: The third option seems a lot less strange to me now that you've shown how nice it can look. For some reason I didn't even think of using a compound foreign key, so I was thinking I'd have to add an identity column on the linking table to make it work. Looks great, thanks!

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

Answer 1

Since the primary user is a function of the computer and the user I would tend to go with your approach of having the primaryUser being a column on the linking table.

The other alternative that I can think of is to have a primaryUser column directly on the computer table itself.

Answer 2

I would have made another table PRIMARY_USERS with unique on computer_id and making both computer_id and user_id foreign keys of USERS.

Answer 3

Either solution 1 or 2 will work. At this point I would ask myself which one will be easier to work with. I've used both methods in different situations though I would generally go with a flag on the linking table and then force a unique constraint on computer_id and isPrimaryUser, that way you ensure that each computer will only have one primary user.

Answer 4

2 feels right to me, but I would test out 1, 2 and 3 for performance on the sorts of queries you normally perform and the sorts of data volumes you have.

As a general rule of thumb I tend to believe that where there is a choice of implementations you should look to your query requirements and design your schema so you get the best performance and resource utilisation in the most common case.

In the rare situation where you have equally common cases which suggest opposite implementations, then use Occam's razor.

Answer 5

Edit -- I didn't think properly about it the first 3 times through... I vote for -- (Number 3 solution)

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 involving computers and users I like the idea of being able to associate a user to multiple computers as the primary user. This may not ever occur where this database is being used though.

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

 (computer_users.primary_user_id fk-> users.user_id)

is to prevent a computer from ever having multiple primary users.

Given those reasons Number 3 solution looks better since you will never run into some possible problems I see with the other approaches.

Solution 1 problem - Possible to have multiple primary users per computer.

Solution 2 problem - Computer links to a primary user when the computer and user aren't link to each other.

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

Solution 3 problem - It does seem kind of odd doesn't it? Other than that I can't think of anything.

Solution 4 problem - I can't think of any other way of doing it.

---

This is the 4th edit so I hope it makes sense still.

Answer 6

We have a similar situation in the application I work on where we have Accounts that can have many Customers attached but only one should be the Primary customer.

We use a link table (as you have) but have a Sequence value on the link table. The Primary user is the one with Sequence = 1. Then, we have an Index on that Link table for AccountID and Sequence to ensure that the combination of AccountID and Sequence is unique (thereby ensuring that no two Customers can be the Primary one on an Account). So you would have:

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

Answer 7

Option 3, though it may feel strange, is the closest to what you want to model. You'd 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 (the PrimaryUserId can be nullable if you want), that must be in Computer_User. Edit: If a user can only be primary for 1 computer, then a UNIQUE CONSTRAINT on Computer.PrimaryUserId will enforce that. Note that there is no requirement that all users be a primary on some computer (that would be a 1:1 relationship, and would call for them to 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