2

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.

2

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.

  • 1
    in my case, each table type of user have many differences between them, some particular profile data and related information that constraint from another table, so I can't merge those table into single Users table. I just prevent field duplication regard those users have ability to login with username and password, so do I need separate the credential into another table or each table has username and password fields? – Angga Ari Wijaya Aug 10 '16 at 11:51
  • The credentials sound like they should be common for all types that can login. As mentioned in my post you can nest "types" several times in a hierarchy. – Lennart Aug 10 '16 at 12:08

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.