CODEWITHSUNDEEP
entity-relationship
Thush-Fdo
Thush-Fdo

Reputation: 514

User and Customer in ER diagram

I'm having a scenario to create an ER diagram.

Scenario

There are Several Regions. One region can have several Business Chains. Each Business chain have one region to cover. One Business chain have several outlets. Customers can use this system to connect to any Business chain. Employees of a Business chain can be assigned to any outlet by the admin of the particular Business chain................

My Question is how am I suppose to handle user details and login details in the above ER diagram (or in the application)

  1. Should I use two separate entities as "Customer" and "Employee"???
  2. Should I use one entity as "User"? If so how to handle the above case of handling emplyees' roster

Upvotes: 0

Views: 2327

Answers (1)

reaanb
reaanb

Reputation: 10065

I suggest you combine customers, employees and users into persons, and use subtyping for each of the roles in which a person may occur:

Customers, Employees and Users are Persons ERD

I left out any indication of overlapping/disjoint subsets, you can fill them in based on your requirements. Implementing disjoint subsets would require adding some additional type indicators and check constraints to the tables below.

Physically, the diagram above would translate into a set of tables like:

person (person_id PK, first_name, last_name, ...)
user (person_id PK/FK, username, password_hash, ...)
customer (person_id PK/FK, credit_limit, ...)
employee (person_id PK/FK, salary, ...)

This allows any person with a user record to log in, and you can easily find out whether they're customers, employees (or both) by joining with those tables. You can create customer or employee-specific relationships easily, e.g.

outlet_employees (outled_id PK/FK, employee_person_id PK/FK)

where employee_person_id has an FK constraint referencing person_id in the employee table. You can also make user-specific relationships, or general person relationships, as your requirements dictate

Upvotes: 2

Related Questions