Better way to define role restrictions in the database tables

Question

Role table

RoleID Desc
1      primary
2      secondary
3      alternate

Users

UserID  Name
1       ann
2       saylor
3       jim
4       ken
5       kathy

Route table

RouteID   Name
1          x
2          y

RouteRoleUser table

RouteID   RoleID      UserID
1         primary      ann
1         secondary    saylor
1         alternate    jim
1         alternate    ken
1         alternate    kathy

I have a grid which shows the following:

Route | Primary Pumper | Secondary Pumper | Alternate Pumpers (comma separated)
x        ann                saylor           jim, ken, kathy

My requirements are:

• Any route can have only one primary user
• Any route can have 0 or one secondary user
• Any route can have 0 or more alternate users
• All users of a route are unique

How can I have the requirements restriction from a db design perspective in the RouteRoleUser table? Currently if I make Route, Role and User as candidate key, it does not stop anyone to add two primary users for a route.

Is there a better way?

Answer 1

For the "Any route can have N number of (type) user(s)" rule, you could validate this by using an INSTEAD OF INSERT trigger and preventing the insert. I personally handle this type of logic at the application or stored procedure level.

For the "All users of a route are unique" you can enforce this with a UNIQUE constraint on RouteID, UserID.