Início / dba / Perguntas / 202528
Accepted
Ophidian
Asked: 2018-03-29 05:05:14 +0800 CST

Atributos anuláveis ​​e dependentes

  • 772

Estou projetando um banco de dados para estudantes. Estudantes de ciência da computação aqui na Suíça podem decidir entre diferentes especializações, por exemplo, desenvolvimento de aplicativos, engenharia de sistemas, etc.

Também há alunos de outras profissões neste aplicativo, que não possuem essa especialização. Alguns exemplos:

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

Espero que você tenha a idéia. Minha pergunta agora é, como eu desenho as tabelas do banco de dados com esses atributos, já que eles são dependentes uns dos outros? Cada usuário tem uma profissão, alguns não possuem uma especialização dependendo de sua profissão. Portanto, um eletricista não deve ser um desenvolvedor de aplicativos, nem um arquiteto.

Meus pensamentos até agora 1

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

Imponha a lógica por meio de verificações de restrição

Abordagem 2

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

Gerencie eu mesmo a lógica e garanta que toda profissão sem especialização tenha uma entrada de especialização.

Abordagem 3

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

De alguma forma, todas as abordagens diferentes parecem desajeitadas, sujas. Quais são os argumentos a favor e contra as diferentes abordagens? Existe uma maneira melhor?

E como faço para procurar esse problema? Dependente é a nomenclatura correta?

Qualquer ajuda é muito apreciada.

database-design hierarchy

2 respostas

  1. Best Answer

    Eu modelaria isso usando uma única tabela para as Profissões e uma tabela relacionada para as Especializações.

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

    Alguns dados de exemplo:

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

    As profissões, com suas especializações associadas:

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

    Resultados:

    ╔════════════════════╦═══════════════════╗═════════╗═════════╗═════════╗
║ ProfessionName ║ SpecializationName ║
╠════════════════════╬═══════════════════╣════════╣════════╣
║ Cientista da Computação ║ Desenvolvimento de Aplicativos ║
║ Cientista da Computação ║ Engenharia de Sistemas ║
║ Cientista da Computação ║ Suporte ║
║ Eletricista ║ NULL ║
║ Zelador ║ NULL ║
║ Arquiteto ║ Prédios Pequenos ║
║ Arquiteto ║ Prédios Altos ║
╚════════════════════╩════════════════════════════╝════════╝

    Se seus usuários puderem ter apenas uma única profissão, eu criaria uma tabela Users como esta:

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

    ProfessionIDAs restrições de verificação garantem que apenas profissões e especializações válidas possam ser adicionadas às custas de exigir uma coluna "duplicada" para especializações.

    A inserção de usuários fica assim:

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

    Ver os usuários com suas profissões e especializações se parece com:

    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 Johnny ║ Cientista da Computação ║ Engenharia de Sistemas ║
║ Maria bem ao contrário ║ Eletricista ║ NULL ║
╚═════════════════════╩════════════════════╩══════ ══════════════╝

    A tentativa de inserir dados inválidos se parece com:

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

    Msg 547, Level 16, State 0, Line 83
    A instrução INSERT entrou em conflito com a restrição CHECK "CK_Users_ProfSpec". O conflito ocorreu no banco de dados "tempdb", tabela "dbo.Users".

    Se eu precisasse dar suporte a usuários com várias profissões, eu usaria uma tabela de referência cruzada, assim:

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

    Inserir dados:

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

    Consulte os dados:

    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

    Resultados:

    ╔═════════════════════╦════════════════════╦══════ ═══════════════════╗
║ UserName ║ ProfessionName ║ SpecializationName ║
╠═════════════════════╬════════════════════╬══════ ═══════════════════╣
║ Little Johnny ║ Cientista da Computação ║ Desenvolvimento de Aplicativos ║
║ Joãozinho ║ Arquiteto ║ Prédios altos ║
║ Maria bem ao contrário ║ Eletricista ║ NULL ║
║ Maria bem ao contrário ║ Zelador ║ NULL ║
║ Peter Pumpkin Eater ║ NULL ║ NULL ║
╚═════════════════════╩════════════════════╩══════ ═══════════════════╝

    Mais uma vez, as restrições em vigor impedem combinações inválidas de profissão/especialização:

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

    Msg 547, Level 16, State 0, Line 149
    A instrução INSERT entrou em conflito com a restrição FOREIGN KEY "FK_UsersProfessions_Specialization". O conflito ocorreu no banco de dados "tempdb", tabela "dbo.Specializations".

    Isso também apoiará usuários com uma profissão, que não se especializou:

    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 ║ Cientista da Computação ║ NULL ║
╚══════════╩════════════════════╩═════════════════ ═══╝
    • 3

  2. Você pode manter isso simples com uma única hierarquia de tabela, que permite mais de uma "especialização" também.

    • A especialização é essencialmente apenas um subconjunto ou filho da profissão.
    • A profissão é apenas uma especialização sem mais pais.

    Acho que você é a Abordagem 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);

    Agora você pode consultá-lo com um CTE recursivo.

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

    A vantagem deste formulário é que você pode adicionar uma nova "especialização" e ela funcionará... Por exemplo,

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

    E só vai funcionar..

     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.

    • 1

relate perguntas