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

Question

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?

Answer 1

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.

Answer 2

How to model it in the ERD ?

The created_by attribute in Project defines indeed a many-to-one relationship relationship, that you would denote N 1 in your diagram (because a user can create several projects, but a project only have 1 creator):

If we want to be more precise, we could indicate total participation of project in the relationship (i.e. every project must have a creator, i.e. created_by NOT NULL) and partial participation of user since a user does not necessarily have created a project.

In UML, the fact that the user identified by create_by has to be an admin would be simply noted with a constraint on the association, as simple as {created_by.user_type = 'admin"}. Unfortunately, there is no such notation in classical ERD. You could go either for an informal comment, on the diagram, or for one of the extended ERD notations (EERD) allow to model subcategories as if they were entities but without repeating the attributes.

How to implement it?

At the database level you would have to enforce the constraint in the behavior, e.g. check programatically when a user creates a project that the right permission is there. Typically, you would do this with a trigger on the Project table. You would then also have to decide what to do if a user who was admin and has created project later gets its type changed.

Caution: The idea of duplicating the user_type into a created_by_type is not a good idea at all: this is a denormalisation of the database schema that introduces a redundancy: the redundant information would then to have to be maintained in sync programatically. Moreover, when a new project is created, the information would also have to be programatically fed, in synch with the relevant user row. This would also require some extra step i the creation behavior, but with much more risks of inconsistencies.