Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
there will always need to be a user_id for a client. In your answer, are you talking about composite primary keys? Or something else? –  Gaz_Edge Feb 19 '13 at 12:39
    
No a composite Primary Key will not work because it will include the client_id and so will not achieve what you want. You need a unique index across just the user_id and the username in the Client table. –  Mr.Brownstone Feb 19 '13 at 12:41
1  
can i not have a composite primary key of just the user_id and username in the clients table? –  Gaz_Edge Feb 19 '13 at 12:45
    
You can if you wish but having the client_id as the primary key and having a unique index over the other two fields is a better design. –  Mr.Brownstone Feb 19 '13 at 12:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.