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

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

Answer 2

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.