Início / dba / Perguntas / 332108
Accepted
Santi
Asked: 2023-10-13 21:42:18 +0800 CST

Como projetar um relacionamento um-para-um que pode ser um-para-(pai) ou um-para-(filho)?

  • 772

Estou criando um site onde as pessoas podem avaliar/avaliar bebidas alcoólicas ("Destilados"). Dado que algumas bebidas destiladas podem variar muito de garrafa para garrafa, gostaria que os usuários tivessem a opção de revisar a bebida em si ou optar por revisar uma garrafa específica dessa bebida.

Considere as tabelas (SQL Server) abaixo:

Spirits
+--------+------------------+
| Column | Type             |
+--------+------------------+
| Id     | uniqueidentifier |
| Name   | nvarchar(255)    |
+--------+------------------+

Bottles
+----------+------------------+
| Column   | Type             |
+----------+------------------+
| Id       | uniqueidentifier |
| SpiritId | uniqueidentifier |
+----------+------------------+

Ratings
+----------+------------------------+
| Column   | Type                   |
+----------+------------------------+
| Id       | uniqueidentifier       |
| SpiritId | uniqueidentifier       |
| BottleId | uniqueidentifier, null |
| Score    | int                    |
+----------+------------------------+

Estou me aproximando da Ratingsmesa corretamente? Em caso afirmativo, como faço para garantir que BottleIdé filho de SpiritId?

Ou devo require SpiritId or BottleId , mas não ambos (e existe uma maneira de impor esse requisito no banco de dados?)

Tenho certeza de que uma pergunta como essa foi feita, mas estou tendo problemas suficientes para formulá-la sozinho, portanto, minhas tentativas de pesquisa não produziram nada de útil. Obrigado.

sql-server

2 respostas

  1. Best Answer

    como faço para garantir que BottleId é filho de SpiritId

    Uma maneira de fazer isso é com uma chave alternativa composta (restrição exclusiva) na Bottlestabela SpiritIde BottleIdnas colunas. A Ratingstabela pode então ter uma chave estrangeira referenciando ambas as colunas da Bottlestabela para garantir que a bebida e a garrafa correspondam. Observe que uma chave estrangeira pode fazer referência a colunas de uma restrição exclusiva ou de um índice exclusivo, não apenas à chave primária.

    As linhas com NULLvalores de chave estrangeira não são verificadas quanto à integridade referencial, portanto, uma classificação com NULLBottleId será permitida desde que o SpiritId seja válido, imposto apenas pela restrição de chave estrangeira SpiritId.

    Exemplo de DDL e dados:

    CREATE TABLE dbo.Spirits(
      Id uniqueidentifier NOT NULL
        CONSTRAINT PK_Spirits PRIMARY KEY
    , Name nvarchar(255) NOT NULL
);

CREATE TABLE dbo.Bottles(
      Id uniqueidentifier NOT NULL
        CONSTRAINT PK_Bottles PRIMARY KEY
    , SpiritId uniqueidentifier NOT NULL
        CONSTRAINT FK_Bottles_Spirits FOREIGN KEY REFERENCES dbo.Spirits(Id)
    , CONSTRAINT UQ_Bottles_Id_SpiritId UNIQUE (SpiritId, Id)
);

CREATE TABLE dbo.Ratings(
      Id uniqueidentifier NOT NULL
        CONSTRAINT PK_Ratings PRIMARY KEY
    , SpiritId uniqueidentifier NOT NULL
        FOREIGN KEY REFERENCES dbo.Spirits(Id)
    , BottleId uniqueidentifier NULL
    , Score int NOT NULL
    , CONSTRAINT FK_Ratings_Bottles FOREIGN KEY (SpiritId, BottleId) REFERENCES dbo.Bottles(SpiritId, Id)
);
GO

INSERT INTO dbo.Spirits (Id, Name)
    VALUES('8F4A2202-528B-4B7B-9B60-2EFD2F30534A', 'Gin')
INSERT INTO dbo.Spirits (Id, Name)
    VALUES('43C748B9-4876-4D2A-AAF4-30AD8D66BDE7', 'Whiskey')
GO

INSERT INTO dbo.Bottles (Id, SpiritId)
    VALUES('78D3E2A8-327C-43C6-A68F-0E74E49E8CC2', '8F4A2202-528B-4B7B-9B60-2EFD2F30534A');
INSERT INTO dbo.Bottles (Id, SpiritId)
    VALUES('826B6FA6-4BC0-4C4D-942B-724D77E26C22', '8F4A2202-528B-4B7B-9B60-2EFD2F30534A');
INSERT INTO dbo.Bottles (Id, SpiritId)
    VALUES('CC4EEC4D-7FBF-4406-BFF8-1A7C66829880', '43C748B9-4876-4D2A-AAF4-30AD8D66BDE7');
INSERT INTO dbo.Bottles (Id, SpiritId)
    VALUES('BACAE7FD-F122-4A6B-8327-5E5E59E298C2', '43C748B9-4876-4D2A-AAF4-30AD8D66BDE7');
GO

--gin rating without specific bottle - allowed
INSERT INTO dbo.Ratings (Id, SpiritId, BottleId, Score)
    VALUES(NEWID(), '8F4A2202-528B-4B7B-9B60-2EFD2F30534A', NULL, 5); 
--gin with gin bottle - allowed
INSERT INTO dbo.Ratings (Id, SpiritId, BottleId, Score)
    VALUES(NEWID(), '8F4A2202-528B-4B7B-9B60-2EFD2F30534A', '826B6FA6-4BC0-4C4D-942B-724D77E26C22', 6); 
--gin rating with whiskey bottle - not allowed
INSERT INTO dbo.Ratings (Id, SpiritId, BottleId, Score)
    VALUES(NEWID(), '8F4A2202-528B-4B7B-9B60-2EFD2F30534A', '43C748B9-4876-4D2A-AAF4-30AD8D66BDE7', 7); 
--whiskey with whiskey bottle - allowed
INSERT INTO dbo.Ratings (Id, SpiritId, BottleId, Score)
    VALUES(NEWID(), '43C748B9-4876-4D2A-AAF4-30AD8D66BDE7', 'CC4EEC4D-7FBF-4406-BFF8-1A7C66829880', 8);
--whiskey with gin bottle - not allowed
INSERT INTO dbo.Ratings (Id, SpiritId, BottleId, Score)
    VALUES(NEWID(), '43C748B9-4876-4D2A-AAF4-30AD8D66BDE7', '78D3E2A8-327C-43C6-A68F-0E74E49E8CC2', 9); 
GO
    • 1

  2. Estou abordando a tabela de classificações corretamente?

    Não vejo nada de muito errado com o seu design.

    Em caso afirmativo, como faço para garantir que o BottleId seja filho do SpiritId?

    Você já está aplicando isso em sua Bottlestabela, fazendo referência a Spiritpor meio do campo de chave estrangeira não anulável SpiritId. Você vai querer ter certeza de criar um objeto de chave estrangeira para impor esse relacionamento, na prática.

    Uma maneira alternativa de estruturá-lo também é uma tabela separada para SpiritRatingsvs BottleRatings. Você não precisa fazer isso, pois seu design atual parece bom para mim à primeira vista, mas você pode achar esse design alternativo mais benéfico no futuro, caso adicione colunas às classificações que se aplicam apenas a um, mas não ao outro.

    • 1

relate perguntas