databasedatabase-designerd

How to specify relation based on a condition in Entity Relationship Diagram (ERD)?

Suppose I have a table named User (Entity), whose attributes are _id, name, email, user_type [enum: "normal user", "admin", "volunteer"], address, and age. also, I have another entity called Project (Entity), whose attributes are _id, title, start date, end date, and created_by. The admin-type user can only able to create a project so another user_type user can only view it. so how can I denote the relationship between the User and the Project entity based on this condition? I don't want to create 3 separate entities NormalUser, Admin, and Volunteer or should I separate them? All attributes are the same but the permission is different. What can I do?

enter image description here

Solution

  • The admin-type user can only able to create a project so another user_type user can only view it. so how can I denote the relationship between the User and the Project entity based on this condition?

    By using a composite FK plus a constraint on the side of the child table:

    Users {
  _id PK    UK1
  user_type UK1
  ...
}

Projects {
  _id PK
  created_by      FK1 -> Users._id
  created_by_type FK1 -> Users.user_type
  ...
  CONTRAINT created_by_type = 'admin'
}

    (I have specified a composite UK1, meaning a uniqueness constraint, since most DBMS's only allow an FK to a unique key, whether simple or composite.)

    Notice that that guarantees integrity of the data, namely only admins will ever appear in the created_by field of Projects: a quite different issue is app authorizations and who can do, or try to do, what and when at applicative level.

    A couple of additional notes:

    In fact, eventually along that line even authorizations can be enforced, namely via some UserActions (pre-)logging table, so that constraints can be applied across Users (their types or roles) and possible Actions, although such a level of control is an extreme and requires that all applicative logic be encoded in the relational model: which is far from impossible and provides a very significant amount of possible code generation, but requires a complete functional specification on top, not just the relational data model.

    Incidentally, as far as ERD's and relational databases are concerned, entity names should really be plural (well, so I would advice/claim: I won't belabour the point), which is why I have changed the table names above.