Search code examples
databaseentity-relationship

Choosing entities for a database

There are three kinds of people in a database, member, volunteer and requester.

Most of the volunteers are members and half of the requesters are members.

volunteer has some attributes that member doesn't have.

If a requester is not a member, only basic information can be put in the database. And they may become member later.

Anyone can be requester and volunteer, so, yes, a user can be both a requester, volunteer and member at same time. A user payed membership fee can be a member, once he made a request, he is a requester. And he can choose to be a volunteer. If a member did nothing, he is just member.

How should I choose entities?

Should I make them three entities or put them in one entity, and set volunteer and requester as two attributes?

Thanks

Solution

  • What about having a users table that contains all generic data, and then tables for the "roles", that contain role-specific data and that can be linked to the users:

    user:
    - id
    - name
    - email
    - member_id
    - volunteer_id
    - requester_id

client
    - id
    - data

volunteer
    - id
    - data

requester
    - id
    - data

    Then, if you are representing the rows with an object-oriented abstraction, your User objects can have this method:

    // C-style pseudocode

boolean isVolunteer() {
    !!self.volunteer_id;
}