Nullable, dependent attributes

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 | | Pr

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 (Profess

StackExchange Database Administrators Q#202528, answer score: 3