Correct data model for users with clients

database-designrelational-theoryunique-constraint

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?

Best Answer

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.

Related Solutions

Database model with users, roles and rights

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?

RBAC in the Role-Based Access Control (RBAC) model, access to resources is based on the role assigned to a user. In this model, an administrator assigns a user to a role that has certain predetermined right and privileges. Because of the user's association with the role, the user can access certain resources and perform specific tasks. RBAC is also known as Non-Discretionary Access Control. The roles assigned to users are centrally administered.

DAC In the Discretionary Access Control (DAC) model, access to resources is based on user's identity. A user is granted permissions to a resource by being placed on an access control list (ACL) associated with resource. An entry on a resource's ACL is known as an Access Control Entry (ACE). When a user (or group) is the owner of an object in the DAC model, the user can grant permission to other users and groups. The DAC model is based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources. 2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2) DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)

Mysql – SQL Uniqueness Across Multiple Tables

You could have a third table which stores both. Something like this:

unique_ids
----------
  identifier (UNIQUE)
  used_where (name/id of table that the identifier is used in)

users
-----
  username (FK to unique_ids.identifier)

clients
-------
  clientname (FK to unique_ids.identifier)

This way, both tables share one common table that contains the values that need to be unique and uniquness contraints can be easily enforced in that shared table. You will need a simple on-insert trigger (on both tables) as well that will check the used_where field to ensure that the identifier is not already used by the other table (because if I understand your requirement, username must be unique in users but clientname does not need to be unique in clients).

If you don't want to introduce new structures and modify existing ones, you could try using an on-insert trigger on clients and users that checks to make sure a new record does not contain an existing username/clientname.

Best Answer

Related Solutions

Database model with users, roles and rights

Mysql – SQL Uniqueness Across Multiple Tables

Related Question