Basic ERD question: how many entities to use

Question

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?

Answer 1

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.