0

Say there are Buyers, Sellers, and Reviewers, all have common attributes to them such as:

  • name
  • address
  • login-info (username, password, email)

and all three have abilities unique to their roles.

e.g. buyer can buy, seller can sell, reviewer can review

Assuming a person can be only one of the three roles, what is the best way to design an ERD for this scenario?

Would having 3 separate entities for each role + an entity for LoginInfo having a relationship with all 3 of role entities be good?

If so, would it be redundant to have name, address in all 3 role entities?

0

Generally in these cases its best to have a single entity with flags for which type the person is. In many cases as your system is used, you will likely find someone can be any combination of these. And if they update their name/address/username/password, you want it updated for ALL of their roles simultaneously. This lends to using a single entity with flags for their types:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password
...
Seller Boolean
Buyer Boolean
Reviewer Boolean

EDIT:

Alternatively, as suggested by John M and depending on if you are going to create more Person Roles, you could have 2 entities with a lookup between them identifying the type of roles a person is able to perform:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password

A Role table

Role
------------
RoleID
RoleDescription

(this table would contain Seller/Reviewer/Buyer to start with)

Then this link table:

PersonRole
------------
PersonID
RoleID

This would enable you to add Roles easily in the future without modifying your Person table. So if you created a Role such as "Editor", you would only need to create the row in the Role table and Inserts for the people you want to add as Editors into the PersonRole table.

|improve this answer|||||
  • 1
    I would favor using a separate table with FK to track the person "type" and an intermediate table to create a many-to-many relationship - the idea of having a ton of boolean flags in a table is usually not very appealing or ideal. Even when things are fixed (possible person types) they tend to have way of changing :) – LowlyDBA Nov 30 '16 at 22:54
  • True, just depends on your domain situation. I will update my answer... – blobbles Nov 30 '16 at 23:03

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.