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 |     | 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.
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               ║
╚══════════╩════════════════════╩════════════════════╝