Billy Chrisnawan Adhyaksa Billy Chrisnawan Adhyaksa - 2 months ago 9
SQL Question

ER Diagram Design doubts

I have this case where Designers and Managers works on a project with a certain Role.

Is this ER Diagram design correct?

I have doubt in Manager, Designer, Role, and Project relation.

ER Diagram:

ER Diagram


Or do I just make it as attribute?

enter image description here

Here is the Database table Representation
Here is the Database table Representation

The case also states to show that Administrator manages almost everything.
Do I have to make a "Manages" relations?



Here is the complete case:

• Administrators can login to manage everything

• Managers can login and can work on Projects

• Designers can also login and can work on Projects

• Customers own Projects

• Managers, Designers and Customers have profile pages listing the projects they are related to. Administrator don't have.

• Managers can have Designers working for them

• Designers only have one Manager at a time and Administrator can change the Manager of the Designer at any time

• Managers and Designers who working on a Project is assigned a Role that the Administrator can manage (e.g. Project Champion, Lead Architect, Safety Engineer, etc)

• Project needs to have rich text as the description

• Project needs to be able to have many photos and PDF files

• Only Managers and Designers who is working on the Project can edit the project (Administrator also can)

Answer

Making Role an attribute means a Designer/Manager can only work on a Project in a single Role. Making it an entity with a cardinality of N in the relationship means a Designer/Manager can work on a Project in multiple Roles.

Between those options is the possibility of making it an entity with a cardinality of 1 in the relationship (meaning the PK consists of Manager/Designer and Project). Whether to make it an entity or an attribute depends on whether you have information about Roles or just want to record a value.

Your question about whether to make a Manages relation is unclear - I can't decide for you what "Almost everything" means.