主页 / dba / 问题 / 202528
Accepted
Ophidian
Asked: 2018-03-29 05:05:14 +0800 CST

可空的依赖属性

  • 772

我正在为学生设计一个数据库。瑞士的计算机科学专业的学生可以在不同的专业之间做出选择，例如应用程序开发、系统工程等。

本申请中也有来自其他专业的学生，​​他们没有这个专业。几个例子：

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

我希望你明白了。我现在的问题是，我该如何设计具有这些属性的数据库表，因为它们相互依赖？每个用户都有自己的职业，有些用户根据自己的职业没有专业化。所以电工不应该是应用程序开发人员，也不应该是架构师。

到目前为止我的想法 1

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

通过约束检查执行逻辑

方法二

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

自己管理逻辑并确保每个没有专业化的职业都有一个专业化条目。

方法三

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

不知何故，所有不同的方法都让人觉得笨拙、肮脏。支持和反对不同方法的论据是什么？有没有更好的办法？

我什至如何搜索这个问题？dependent命名正确吗？

任何帮助是极大的赞赏。

database-design hierarchy

2 个回答

  1. Best Answer

    我将使用一个表来表示专业，并使用一个相关表来表示专业化。

    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
);

    一些示例数据：

    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);

    专业及其相关专业：

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

    结果：

    ╔════════════════════╦════════════════════════════════════════
║ 专业名称 ║ 专业名称 ║
╠════════════════════╬═════════════════════════════════════
║ 计算机科学家 ║ 应用程序开发 ║
║ 计算机科学家 ║ 系统工程 ║
║ 计算机科学家 ║ 支持 ║
║ 电工 ║ NULL ║
║ 看门人 ║ NULL ║
║ 建筑师 ║ 小型建筑 ║
║ 建筑师 ║ 高层建筑 ║
╚════════════════════╩════════════════════════════════

    如果您的用户只能有一个职业，我会创建一个这样的用户表：

    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))
            ) 
);

    检查约束确保只能添加​​有效的职业和专业化，但代价是需要“重复”ProfessionID的专业化列。

    然后插入用户如下所示：

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

    查看具有职业和专长的用户如下所示：

    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;

    ╔═════════════════════╦════════════════════╦══════ ══════════════╗
║ 用户名 ║ 专业名称 ║ 专业名称 ║
╠═════════════════════╬════════════════════╬══════ ══════════════╣
║ 小约翰尼 ║ 计算机科学家 ║ 系统工程 ║
║ 玛丽完全相反 ║ 电工 ║ NULL ║
╚═════════════════════╩════════════════════╩══════ ══════════════╝

    尝试插入无效数据如下所示：

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

    消息 547，级别 16，状态 0，第 83 行
    INSERT 语句与 CHECK 约束“CK_Users_ProfSpec”冲突。冲突发生在数据库“tempdb”、表“dbo.Users”中。

    如果我需要支持多个职业的用户，我会使用交叉引用表，如下所示：

    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 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);

    查询数据：

    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

    结果：

    ╔═════════════════════╦════════════════════╦══════ ═══════════════════╗
║ 用户名 ║ 专业名称 ║ 专业名称 ║
╠═════════════════════╬════════════════════╬══════ ═══════════════════╣
║ 小强尼 ║ 计算机科学家 ║ 应用程序开发 ║
║ 小强尼 ║ 建筑师 ║ 高层建筑 ║
║ 玛丽完全相反 ║ 电工 ║ NULL ║
║ 玛丽完全相反 ║ 看门人 ║ NULL ║
║ 吃南瓜的彼得 ║ NULL ║ NULL ║
╚═════════════════════╩════════════════════╩══════ ═══════════════════╝

    再一次，适当的约束可以防止无效的职业/专业化组合：

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

    消息 547，级别 16，状态 0，第 149 行
    INSERT 语句与 FOREIGN KEY 约束“FK_UsersProfessions_Specialization”冲突。冲突发生在数据库“tempdb”、表“dbo.Specializations”中。

    这也将支持具有专业但没有专业化的用户：

    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

    ╔══════════╦════════════════════╦═════════════════ ═══╗
║ 用户名 ║ 专业名称 ║ 专业名称 ║
╠══════════╬════════════════════╬═════════════════ ═══╣
║ Max ║ 计算机科学家 ║ NULL ║
╚══════════╩════════════════════╩═════════════════ ═══╝
    • 3

  2. 您可以使用单个表层次结构保持简单，它也允许多个“专业化”。

    • 专业化本质上只是该专业的一个子集或子集。
    • 该专业只是一个没有其他父母的专业。

    我认为这就是你的方法 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);

    现在您可以使用递归 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"}

    这种形式的优点是你可以添加一个新的进一步的“专业化”，它就会起作用......例如，

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

    它会起作用..

     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"}

    现在您可以看到The Gods是 的进一步特化Computer scientists，并且PostgreSQL DBAs是它们的子集。

    • 1

相关问题