Correct data model for users with clients

Question

I have an application where users are granted access. Part of the system allows users to have their own 'clients'.

I have a users table

********************************
 users
********************************
 PK user_id    int
    username   string
    password   string(salted)
********************************

and clients table

********************************
 clients
********************************
 PK client_id  int
 FK user_id    int
    username   string
    password   string(salted)
********************************

What i want is that each user should be able to have a client with the same name. The case could be that two individuals choose to go with a different user, but by chance choose the same username. Therefore, there can be multiple entries for a username in the clients table, but against different user_ids.

The problem I have is that although the username field is not unique in the clients table, it should be unique for each user_id i.e. a user cannot have two or more clients with the same username.

How would I model this?

Answer 1

You can add a unique index across the user_id and username in the Clients table this will ensure that the username can only occur once per user_id. Though you should be aware that if the user_id column is nullable it will also apply to clients that do not have a user_id assigned to them.

EDIT

Using a composite primary key that consists of the client_id, user_id and the username will not meet your requirements. This is because the addition of the client_id makes the grouping unique. Take the following rows as an example:

client_id user_id username
1         1       John Doe
2         1       John Doe

If you had a composite primary key of all 3 columns the above would be allowed - however, if you just added a unique index across the last 2 columns the above would be invalid - which is your intended result.

You could add a composite primary key across the last two columns but this is not a good design as it adds additional complexity when attempting to reference the client. A better design would be to keep the client_id as the primary and add a unique index across the last 2 columns.