主页 / dba / 问题 / 234061
Accepted
Zachary Weber
Asked: 2019-04-06 11:19:49 +0800 CST

当一张表有多个关系但只应使用一个时，保持数据完整性。（这是糟糕的设计吗？）

  • 772

我正在研究一个保存实验室医学检测订单的数据库。我遇到的问题（也许）是我有一个包含订单号和测试代码的表格。还有一个TestNumber PK。还有另外两个表与这个表有关系。其中一个拥有测试尿液样本的信息，另一个拥有测试血液样本的信息。根据第一个表中的测试代码，应将附加记录插入其中一个相关表中。我遇到的问题是没有什么可以阻止将记录插入到测试代码的错误表中。

这是表格图的简化图像，显示了我在说什么：

表格图的简化图像

因此，如果将测试代码插入 LabOrders 表并且其样本类型实际上是“Blood”，那么还应将一条记录插入到表 BloodTestDetails 中。但是没有什么可以阻止记录也被插入（或者相反，被插入）到 UrineTestDetails 表中......有没有办法可以防止这种情况并保持数据完整性，或者这是一个糟糕的设计？

如果这是一个糟糕的设计，有人会提出什么样的建议来保存这种类型的数据？

sql-server database-design

3 个回答

  1. Best Answer

    在我从事 IT 工作的这些年里，我使用过六七个不同的实验室信息管理系统 (LIMS)。

    这些系统使用的一般结构往往是这样的：

    加入 （这是案例开始的地方。）
    AccessionKey PK
    AccessionDate
    CreatedBy
    {other columns as needed}

    患者 （这包含有关病例患者的信息）
    PatientName
    PatientBirthDate
    {其他列，包括地址条目的外键等}

    AccessionPatients （将现有患者链接到新病例）
    AccessionPatientKey PK
    AccessionKey FK
    PatientKey FK
    {etc.}

    标本 （这包含有关从患者身上捕获的任何标本的信息）
    SpecimenKey PK
    AccessPatientKey FK
    SpecimenTypeKey FK CollectionDate
    （等）

    SpecimenTypes （保存不同种类标本的信息）
    SpecimenTypeKey PK
    SpecimenTypeName
    SpecimenContainerTypeKey FK （链接到容器类型表或只是 EDTA 容器、标本杯、血管或...的文本字段）
    {等}

    SpecimenTestRequests （保存针对哪些样本进行了哪些测试）
    SpecimenTestRequestKey PK
    SpecimenKey FK （请求此测试的样本。）
    TestKey FK （请求的测试。） RequestDate
    {等}

    测试 （存储有关实验室可以执行的测试的信息）
    TestKey PK
    TestName
    CreatedDate
    EndDate
    TestFee
    {etc}

    TestResultTypes （跟踪测试可以有的结果类型） TestResultTypesKey PK
    TestKey FK
    TestResultName （此测试中此答案的显示名称） UnitsKey FK （链接到度量单位表） TestResultDataType （自由文本、下拉列表名称、数字、等）
    TestResultListName （FK 到列表或 NULL）
    DisplayOrder （数字，设置结果中出现的位置）
    {等}

    TestResultLists （这包含下拉结果的有效列表条目）
    TestResultListKey PK
    TestResultListName （在 TestResultTypes 中使用）
    TestResultListValue （下拉列表中显示的内容）
    （等等）

    TestResults （这是存储实际结果的地方）
    TestResultKey PK
    SpecimenTestRequestKey FK （针对什么情况要求进行什么测试？）
    TestResultTypeKey FK （结果是什么答案？）
    TestResultAnswer （保存实际结果的长文本）
    EnteredDate
    EnteredBy FK (输入结果的用户）
    ReleasedBy FK （发布结果的用户）
    ReleasedDate
    （等）

    单位 （mg 或 mg/dL 等值的测量单位表）
    UnitsKey PK
    UnitsText
    （等）

    LIMS 中通常包含几个其他表——用于跟踪随时间变化的历史表（审计跟踪）、用户表、权限表、地址表，有时还有费用表、诊断表等。我正在查看我支持的其中一个 LIMS 的架构，它有 457 个表。另一个有 465 张桌子。

    无论如何，这会创建一个案例或加入，将患者连接到该案例（如果您的系统在一个案例中有多个患者，那么您将需要一个链接表）。然后将一个或多个样本分配给具有样本类型的案例。接下来，要求对每个样本进行测试。每个测试可以有 1 个或多个结果值。然后有一个结果表来提供每个测试的结果。

    完整的 LIMS 也有参考范围（您的血糖为 100。它应该在 80 到 120 之间，因此您在正常范围内）。它也可能有面板的想法（一组一起执行的测试，如 CBC 面板），可以作为一个组而不是单独的测试来定义和排序......）

    但希望这会有所帮助。

    • 2

  2. 使用当前数据模型执行此操作的最简单方法是创建一个函数来验证提供的 TestNumber 的 TestCode 并在 BloodTestDetails 和 UrineTestDetails 表的 CHECK CONSTRAINT 中使用它。下面的例子。

    设置：

    CREATE TABLE TestCodes (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    TestType NVARCHAR(255) NOT NULL
)
GO
INSERT INTO TestCodes (TestType)
VALUES ('Urine'), ('Blood')
GO

CREATE TABLE LabOrders (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    TestCode INT NULL,
    CONSTRAINT FK_LabOrder_TestCode FOREIGN KEY (TestCode)
    REFERENCES TestCodes (ID)
    ON UPDATE SET NULL
    ON DELETE SET NULL
)
GO

CREATE FUNCTION LabOrderTestCode (@LabOrderID INT)
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @RetCode NVARCHAR(255)

    SELECT @RetCode = tc.TestType 
    FROM LabOrders lo
    INNER JOIN TestCodes tc ON tc.ID = lo.TestCode
    WHERE lo.ID = @LabOrderID

    RETURN @RetCode
END
GO

CREATE TABLE BloodSample (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    LabOrder INT NOT NULL,
    CONSTRAINT FK_BloodSample_LabOrder FOREIGN KEY (LabOrder)
    REFERENCES LabOrders (ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    CONSTRAINT CK_BloodSample_TestCode CHECK (dbo.LabOrderTestCode(LabOrder) = 'Blood')
)
GO

CREATE TABLE UrineSample (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    LabOrder INT NOT NULL,
    CONSTRAINT FK_UrineSample_LabOrder FOREIGN KEY (LabOrder)
    REFERENCES LabOrders (ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    CONSTRAINT CK_UrineSample_TestCode CHECK (dbo.LabOrderTestCode(LabOrder) = 'Urine')
)
GO

    测试：

    -- Insert a Urine Sample Lab Order -- Should Succeed
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (1)

    INSERT INTO UrineSample (LabOrder)
    SELECT ID FROM #Inserted

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH

-- Insert a Blood Sample Lab Order -- Should Succeed
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (2)

    INSERT INTO BloodSample (LabOrder)
    SELECT ID FROM #Inserted

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH

-- Insert a Blood Sample Lab Order -- Should Fail due to TestCode violation
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (1) -- This is the TestCode for Urine Samples not Blood Samples

    INSERT INTO BloodSample (LabOrder)
    SELECT ID FROM #Inserted -- Will fail due to TestCode = 1. Check Constraint will block.

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH
    • 1

  3. 我使用存储实验室结果（非医疗）的系统。我相信你的设计会受到限制。

    IMO 你应该有一个包含样本的通用表。这意味着将来您可以添加其他样本类型而无需添加更多表格。

    我认为医生会下令进行测试。采集 1 个或多个样本。每个样本可以有 1 个或多个测试。测试订单和测试结果应分开存放。

    在我所在的领域，有时可以重新测试样品。尽管这可能是一种非常罕见的情况，但仍然值得考虑。

    • 0

相关问题