The correct way to model User and Roles in SQL

user_5886295 0
Viewed 868

I'm designing a Java application and the model data is stored in Oracle SQL Server. I'm trying to design the best user/role model according to what is necessary.

Because of business rules all users have basic common information:

  • Identification ID
  • Name
  • Surname
  • Email
  • IsActiveUser

But then depending on the role, the user will have extra fields like:

Client Role:

  • Birth Date
  • Address

Lawyer Role:

  • Specialty
  • Professional Registration ID

Expert Role:

  • Occupation

Manager Role:

  • Region

I think in two possible solutions:

  1. User table will have all the common fields and the optional fields that will be filled depending on the role.
  2. User table will only have the common fields, and then I create a Detail_User table to save the optional fields that vary with the role.

Do you think this possible solutions are good? Is there an alternative better solution?

user-roles roles relational-database model sql
2 Answers

Answering in the Relational database paradigm, as you have tagged it.

Do you think this possible solutions are good? Is there an alternative better solution?

No. This is a classic case for Subtypes.

I have a Role table and the User table have a FK to this, because every user will have only one role.

That won't solve your problem. You need to store the values for each instance of a Role, each instance of an User.

Further, you will appreciate the correct solution only when you wish to constrain some child table (eg. Portfolio.LawyerId) to Lawyer, not User.

Data Model

The Data Model in IDEF1X/ER Level (not ERD) is:

User Role Data Model

Note

  • The Standard for Relational Data Modelling since 1983 is IDEF1X. For those unfamiliar with the Standard, refer to the short IDEF1X Introduction.
  • For full definition and usage considerations re Subtypes, refer to Subtype Definition.
user_4848140

Unless you are going to have zillions of rows, there is no need to split the tables in the two that you specify.

You might consider a separate table for each role -- or more specifically for each role that has bespoke columns. This would give you a little efficiency in storage space in many cases (versus NULL) in a wider record, although that depends on the database and data types being used.

A more important reason to split them is for foreign key references. If you have other tables where "lawyer" would be a foreign key, then you need a lawyers table for that. Voila! Having a separate table for different roles allows such specialized relationships, as well as general purpose relationships for all users.

user_11440350
Related