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?
Best Answer
I would suggest creating the ERD a bit differently, like this:
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:
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