Nullable, dependent attributes

Question

I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc.

There are students from other professions in this application as well, which have no such specialization. A few examples:

+--------------------+-------------------------+
| Profession         | Specialization          |
+--------------------+-------------------------+
| Computer scientist |                         |
|                    | Application development |
|                    | System engineering      |
|                    | Support                 |
|                    |                         |
| Electrician        |                         |
|                    | none                    |
|                    |                         |
| Janitor            |                         |
|                    | none                    |
|                    |                         |
| Architect          |                         |
|                    | Small buildings         |
|                    | High buildings          |
|                    |                         |
+--------------------+-------------------------+

I hope you get the idea. My question now is, how do I design the database tables with these attributes, since they're dependent of each other? Every user has a profession, some do not have a specialization depending on their profession. So an electrician should not be an application developer, nor should an architect.

My thoughts so far 1

+-----------------------------+
| User                        |
+-----------------------------+
| #id                         |
| profession_id               |
| specialisazion_id, nullable |
+-----------------------------+

Enforce logic through constraint checks

Approach 2

+-------------------+     +----------------+     +------------+
| User              |     | Specialization |     | Profession |
+-------------------+     +----------------+     +------------+
| #id               |  +--| #id            |  +--| #id        |
| username          |  |  | name           |  |  | name       |
| specialization_id |--+  | profession_id  |--+  +------------+
+-------------------+     +----------------+

Manage the logic myself and ensure, that every profession with no specialization has a specialization entry.

Approach 3

+----------------+     +-----------------------------+     +------------+
| Specialization |     | spec_prof                   |     | Profession |
+----------------+     +-----------------------------+     +------------+
| #id            |--+  | #id                         |  +--| #id        |
| name           |  +--| specialization_id, nullable |  |  | name       |
+----------------+     | profession_id               |--+  +------------+
                       +-----------------------------+
                                                |
                                                |                                         
                              +--------------+  |
                              | User         |  |
                              +--------------+  |
                              | #id          |  |
                              | username     |  |
                              | spec_prof_id |--+
                              +--------------+

Somehow all different approaches feel clumsy, dirty. What are the arguments for and against the different approaches? Is there a better way?

And how do I even search for this problem? Is dependent the correct naming?

Any help is greatly appreciated.

Answer 1

I would model this using a single table for the Professions, and a related table for Specializations.

USE tempdb;

DROP TABLE IF EXISTS dbo.Users;
DROP TABLE IF EXISTS dbo.Specializations;
DROP TABLE IF EXISTS dbo.Professions;
GO

CREATE TABLE dbo.Professions
(
    ProfessionID int NOT NULL
        CONSTRAINT PK_Professions
        PRIMARY KEY CLUSTERED
    , ProfessionName  varchar(30) NOT NULL
);

CREATE TABLE dbo.Specializations
(
    ProfessionID int NOT NULL
        CONSTRAINT FK_Specializations_ProfessionID
            FOREIGN KEY 
            REFERENCES dbo.Professions(ProfessionID)
    , SpecializationID int NOT NULL
    ,   CONSTRAINT PK_Specializations
        PRIMARY KEY CLUSTERED
        (ProfessionID, SpecializationID)
    , SpecializationName varchar(30) NOT NULL
);

Some sample data:

INSERT INTO dbo.Professions (ProfessionID, ProfessionName)
VALUES (1, 'Computer Scientist')
    , (2, 'Electrician')
    , (3, 'Janitor')
    , (4, 'Architect');

INSERT INTO dbo.Specializations (SpecializationID, SpecializationName, ProfessionID)
VALUES (1, 'Application Development', 1)
    , (2, 'System Engineering', 1)
    , (3, 'Support', 1)
    , (4, 'Small Buildings', 4)
    , (5, 'Tall Buildings', 4);

The professions, with their associated specializations:

SELECT p.ProfessionName
    , sp.SpecializationName
FROM dbo.Professions p
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID

Results:

╔════════════════════╦═════════════════════════╗
║   ProfessionName   ║   SpecializationName    ║
╠════════════════════╬═════════════════════════╣
║ Computer Scientist ║ Application Development ║
║ Computer Scientist ║ System Engineering      ║
║ Computer Scientist ║ Support                 ║
║ Electrician        ║ NULL                    ║
║ Janitor            ║ NULL                    ║
║ Architect          ║ Small Buildings         ║
║ Architect          ║ Tall Buildings          ║
╚════════════════════╩═════════════════════════╝

If your users can only have a single profession, I would create a Users table like this:

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
    , ProfessionID int NOT NULL
        CONSTRAINT FK_Users_Profession
        FOREIGN KEY 
        REFERENCES dbo.Professions(ProfessionID)
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT FK_Users_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations(ProfessionID, SpecializationID)
    , CONSTRAINT CK_Users_ProfSpec
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL) 
            OR (COALESCE(SpecProfID, 0) = COALESCE(ProfessionID, 0))
            ) 
);

The check constraints ensure only valid professions and specializations can be added at the expense of requiring a "duplicate" ProfessionID column for specializations.

Inserting users then looks like this:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 'Little Johhny', 1, 1, 2)
    , (2, 'Mary Quite Contrary', 2, NULL, NULL);

Seeing the users with their professions and specializations looks like:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    INNER JOIN dbo.Professions p ON u.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID
                   AND u.SpecializationID = sp.SpecializationID;

╔═════════════════════╦════════════════════╦════════════════════╗
║      UserName       ║   ProfessionName   ║ SpecializationName ║
╠═════════════════════╬════════════════════╬════════════════════╣
║ Little Johhny       ║ Computer Scientist ║ System Engineering ║
║ Mary Quite Contrary ║ Electrician        ║ NULL               ║
╚═════════════════════╩════════════════════╩════════════════════╝

Attempting to insert invalid data looks like:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecializationID)
VALUES (3, 'Peter Pumpkin Eater', 3, 2);

Msg 547, Level 16, State 0, Line 83
The INSERT statement conflicted with the CHECK constraint "CK_Users_ProfSpec". The conflict occurred in database "tempdb", table "dbo.Users".

If I needed to support users with multiple professions, I'd use a cross-reference table, like this:

DROP TABLE IF EXISTS dbo.UsersProfessions;
DROP TABLE IF EXISTS dbo.Users;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
);

CREATE TABLE dbo.UsersProfessions
(
    UserID int NOT NULL
    , ProfessionID int NOT NULL
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT PK_UsersProfessions
        PRIMARY KEY CLUSTERED 
        (UserID, ProfessionID)
    , CONSTRAINT FK_UsersProfessions_ProfessionID
        FOREIGN KEY (ProfessionID)
        REFERENCES dbo.Professions(ProfessionID)
    , CONSTRAINT FK_UsersProfessions_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations (ProfessionID, SpecializationID)
    , CONSTRAINT CK_UsersProfessions_Specialization
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL)
            OR (COALESCE(ProfessionID, 0) = COALESCE(SpecProfID, 0))
            )
);

Insert data:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (1, 'Little Johnny')
    , (2, 'Mary Quite Contrary')
    , (3, 'Peter Pumpkin Eater');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 1, 1, 1)
    , (1, 4, 4, 5)
    , (2, 2, NULL, NULL)
    , (2, 3, NULL, NULL);

Query the data:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

Results:

╔═════════════════════╦════════════════════╦═════════════════════════╗
║      UserName       ║   ProfessionName   ║   SpecializationName    ║
╠═════════════════════╬════════════════════╬═════════════════════════╣
║ Little Johnny       ║ Computer Scientist ║ Application Development ║
║ Little Johnny       ║ Architect          ║ Tall Buildings          ║
║ Mary Quite Contrary ║ Electrician        ║ NULL                    ║
║ Mary Quite Contrary ║ Janitor            ║ NULL                    ║
║ Peter Pumpkin Eater ║ NULL               ║ NULL                    ║
╚═════════════════════╩════════════════════╩═════════════════════════╝

Once again, the constraints in place prevent invalid profession/specialization combinations:

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (3, 3, 3, 2);

Msg 547, Level 16, State 0, Line 149
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UsersProfessions_Specialization". The conflict occurred in database "tempdb", table "dbo.Specializations".

This will also support users with a profession, that have not specialized:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (4, 'Max');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID)
VALUES (4, 1);

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

╔══════════╦════════════════════╦════════════════════╗
║ UserName ║   ProfessionName   ║ SpecializationName ║
╠══════════╬════════════════════╬════════════════════╣
║ Max      ║ Computer Scientist ║ NULL               ║
╚══════════╩════════════════════╩════════════════════╝

Answer 2

You can keep this simple with a single table hierarchy, which permits more than one "specialization" too.

• The specialization is essentially just a subset or child of the profession.
• The profession is just a specialization with no further parents.

I think that's you're Approach 1.

CREATE TABLE professions(
    id        int PRIMARY KEY,
    parent_id int REFERENCES professions,
    name      text
);

INSERT INTO professions(name,id,parent_id)
  VALUES
    ('Computer scientist',      1, null),
    ('Electrician',             2, null),
    ('Janitor',                 3, null),
    ('Architect',               4, null),
    ('Application development', 5, 1),
    ('System engineering',      6, 1),
    ('Support',                 7, 1),
    ('Small buildings',         8, 4),
    ('High buildings',          9, 4);

Now you can query it with a recursive CTE.

WITH RECURSIVE t(id, profession, specialization, specializations) AS (
SELECT id, name, null, ARRAY[name]
FROM professions
WHERE parent_id IS NULL
  UNION ALL
  SELECT p.id, profession, name, t.specializations || ARRAY[name]
  FROM t
  INNER JOIN professions AS p
    ON t.id = p.parent_id
)
SELECT * FROM t;

 id |     profession     |     specialization      |                   specializations                   
----+--------------------+-------------------------+-----------------------------------------------------
  1 | Computer scientist |                         | {"Computer scientist"}
  2 | Electrician        |                         | {Electrician}
  3 | Janitor            |                         | {Janitor}
  4 | Architect          |                         | {Architect}
  5 | Computer scientist | Application development | {"Computer scientist","Application development"}
  6 | Computer scientist | System engineering      | {"Computer scientist","System engineering"}
  7 | Computer scientist | Support                 | {"Computer scientist",Support}
  8 | Architect          | Small buildings         | {Architect,"Small buildings"}
  9 | Architect          | High buildings          | {Architect,"High buildings"}

The advantage to this form is that you can add a new further "specialization" and it'll just work... For instance,

INSERT INTO professions(name, id, parent_id)
VALUES
  ('SQL Server DBAs', 10, 3),
  ('The Gods',        11, 1),
  ('PostgreSQL DBAs', 12, 11);

And it will just work..

 10 | Janitor            | SQL Server DBAs         | {Janitor,"SQL Server DBAs"}
 11 | Computer scientist | The Gods                | {"Computer scientist","The Gods"}
 12 | Computer scientist | PostgreSQL DBAs         | {"Computer scientist","The Gods","PostgreSQL DBAs"}

Now you can see The Gods are a-further specialization of Computer scientists, and PostgreSQL DBAs are a subset of them.