Designing a database for a scenario with multiple user types

database-design

I'm working on a project with multiple user types such as Contributor, Student, Sales, CS, Operator, etc. I usually separate them into different tables, and for each entity which is Authenticable I create a table called Credentials with a one-to-one relationship.

My questions are:

  • What is exactly the best practice when dealing with multiple user that are "authenticable" like that?

  • What are the deficiencies of my design?

  • Is it necessary to "move" Username and Password into each type ofUser entity?

Thank you for your help. Please give me a suggestion if my question is hard to understand because I'm not an English expert.

Best Answer

I don't believe there is one best design that fits all scenarios. It all comes down to the business rules that you are trying to enforce. If all types share the same attributes, it's probably best to use a single table with a classifier attribute. At the other end of the spectra there is the situation where the types share no attributes (unlikely in your case).

Typically though there are a set of common attributes that can be represented in a common table, say:

CREATE TABLE ORGANISATIONS -- I just invented a name here
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... CHECK ORGANISATION_TYPE IN (...)
);

Then a set of tables that represent attributes that are not common to all types:

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID)
);

SQL99 (might even be SQL92, not sure) allows selects in CHECK constraints so we can add a CHECK constraint with a select that guarantees that the information corresponds with the organisation_type in organisations. However, not many vendors implements this so a common approach is to "inherit" the type attribute. First add a super key in ORGANISATIONS:

ALTER TABLE ORGANISATIONS ADD CONSTRAINT AK1_ORGANISATIONS
    UNIQUE (ORGANISATION_ID, ORGANISATION_TYPE);

Then we add the type attribute to the "sub-tables":

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID,ORGANISATION_TYPE)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID,ORGANISATION_TYPE)
, CONSTRAINT ... CHECK ORGANISATION_UNIT_TYPE IN (...) -- subset of types in ORGANISATIONS
)

The check constraint and the foreign key guarantees that the correct type is used.

Note that a table such as CONTRIBUTORS may be used for two or more types if they have a set of attributes in common. For adding additional attributes to such types the same technique can be used.

Yet another approach is to use nullable attributes for not applicable. In my experience it soon gets messy, and I would only use it if there are few such attributes.

Related Question