Database Design – How to Model Relationship with Different User Roles

database-designschema

I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another.

Consider the following case.

There is an order, the order has a customer who made it, an order will have an assigned manager after some state changes and next order will be assigned to a delivery guy.

I don't know how to model this relationship, not strictly in SQL, but in general from the business domain perspective.

I have tried to trace these changes, however figured out that any redux dispatch action causes this issue.

We can have "fields" customer_id, manager_id, courier_id in the order "model" (table).
Use pivot tables with additional information of relationship type
```
order_id = 31
user_id = 22
rel_type  = "customer"
```

This will be quite tricky to query such a relationship.

Have multiple "pivot" tables like customer_order, manager_order, courier_order.

Please suggest the best option to choose for such a business domain.

Thanks.

Best Answer

Option 3 is considered the best practice in this case because it lets the RDBMS do what it is designed to do. You have the best options for controlling relationships with declarative referential integrity and your queries will be simple and direct. If you specifically want to make this option work like Option 1, insofar as the relationships are constrained to many to one (many orders to one customer each for example) then add a unique constraint on the order_id foreign key column in each intersection table. However, in practical terms, you might actually want many-to-many because you might assign different managers or couriers over time and that history may be important. It all depends on your business rules.

Option 2 is considered a "SQL Code Smell" (polymorphic association)

Option 1 might be reasonable as well, depending on your business rules, but some people don't like it because it might result in "sparsely populated columns" (again, depending on your business rules).

Related Solutions

Database Design – 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 – How to model this relationship

If an invitations is just an invitation, then it seems simplest to store all of the users for an invite in their own row in a separate table.

Users
 - UserID, PK
 - other details about the users

Invitations
 - InvitationID, PK
 - OwnerID or OwningUserID or just UserID, FK to Users
 - other details about the invitations

InvitationUsers
 - InvitationID, FK to Invitations
 - UserID FK to Users
   [PK on (InvitationID, UserID)]

A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.

If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:

Users
 - UserID, PK
 - other details about the users

Invites
 - SenderID, FK to Users(UserID)
 - RecipientID, FK to Users(UserID)
   [PK on (SenderID, RecipientID)]

If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.

Best Answer

Related Solutions

Database Design – Model with Users, Roles, and Rights

Mysql – How to model this relationship

Related Question