0

So, I have a scenario of a movie theatre and in this scenario employee can be a customer too... if I create two entities, customer and employee ... and make emp_id of employee a foreign key in customer then it makes employee a customer too.

Customer: cust_id, Name, age, emp_id
Employee: emp_id, Name, age

But when I do this, data of employee gets repeated in customer. What should I do?

  • Are Customer and Employee different entities with different attributes? Or are they all People? – AMtwo Dec 27 '18 at 0:55
  • Yes ... but some of their attribute matches like name, age etc – Grishma Dahal Dec 27 '18 at 0:57
  • Person is a superclass of subclasses employee and customer. Your question may have an aswer here. – Walter Mitty Jan 1 at 1:10
2

I would suggest creating the ERD a bit differently, like this:

  • Users (contains data for both customers and employees, and their shared attributes such as name and age)
  • Roles (this would contain a list of roles that define a person's set of permissions. "Customer" and "Employee" could be two such roles)
  • UserRoles (this would be a many-to-many relationship table connecting Users to Roles. In other words: A User can have one or more Roles)

If there's no need for additional roles other than Customer and Employee, and also if the permissions set of a Customer are always contained within that of an Employee, and also if an Employee doesn't have attributes that a Customer doesn't, then I would even rather add a column "is_employee" to the Users table and this would make both Roles and UserRoles unnecessary.

If, on the other hand, Customers and Employees are generally very different entities with different attributes (other than, let's say, name and age), then I'd suggest that you'd create 3 tables:

  • Users
  • Customers
  • Employees

The Users table would contain the shared attributes only (i.e. name and age). The Customers and Employees tables would have their own unique attributes that are not shared, and both would have a foreign key "user_id" linking to Users.

If we're talking conceptually, what you'd want to do, generally, is create some sort of entity that would be used by both Customers and Employees (Users in my example), and thus avoid duplicating the same attributes in more than one table.

This is rather basic normalization methodology.

If you're unfamiliar with it, I'd strongly recommend you would study it a bit:

https://www.google.com/search?q=database+normalization+tutorial

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.