2

I have an entity called Users, with the attributes Id, Name, and Phone No. The objective is to develop an entity-relationship diagram (ERD for brevity) where Users might be Store Cashiers, and also Customers.

  • Cashiers have the attributes Position and Work Hours.
  • Customers have the attributes Credit History and Amount.
  • A Cashier can also be a Customer.
  • Only a Cashier can add a Customer to the Store's database.

The question

It is easy to develop the ERD where I can include Roles, but then how can I enforce the following condition in the ERD?

  • Only a Cashier can add a Customer to the Store's database.
0

First of all: (A Cashier can also be a Customer)
In this case, you can use a 1-to-1 relationship named "CAN ALSO BE" between Cashier Entity and Customer Entity. And also transmit Customer P.K as F.K to Cashier. This F.K (Foreign key of Customer in Cashier) can be null too and should be UNIQUE.

Edit: (based on @RDFozz notice)
We can use the similar User_ID in both Cashier and Customer to show that a Cashier can also be a Customer.

Secondly: (Only a Cashier can add a Customer to the Store's database)
Meaning that each Customer has a creator and should be a Cashier. So you have a one-to-many relationship named "CREATOR" between Cashier and Customer. So, you should transmit Cashier P.K as F.K to Customer. This F.K (Foreign key of Cashier in Customer that shows creator id) should not be null.

Thirdly: (Only a Cashier can add a Customer to the Store's database)
We can not put ALL Business Logic of our project into ERD. For this reason we creates Business Layer or Business Logic Layer (BLL) or sometimes we write some Stored Procedures or Functions with Enhanced SQLs (those have IF, Loops and etc.)

If we put P.K of Cashier as F.K into Customer and prevent it to be null, that's enough. Your Insert into Customer should has a Cashier ID to be done.

  • For a cashier can also be a customer - assuming User, Cashier, and Customer tables, and that Cashier and Customer both have User_ID as a foreign key, would you really need anything more? Also, while a Cashier_ID as a required FK in Customer may represent that only a Cashier can create a Customer, I agree with your statement that this isn't really something that can be built into the ERD - any Cashier_ID could be included in Customer, regardless of who created the record. This is a function of the application, not the ERD. – RDFozz Dec 12 '17 at 17:18
  • You're right. Thanks. For the first part of my answer, there is no need to any relationship and we can use a same User_ID to for Cashier and Customer. – Gholamali-Irani Dec 12 '17 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.