如何使用父子表建立连接表？ [英] How do I model a join table with parent-child tables?

问题描述

我有一张表， VisitTest ，将患者访问（访问表）加入诊断测试（测试表）。 Test 表是包含实际测试的表的超类（父表）。每个子表具有测试参数和结果（1）。

测试表包含元数据。像 id ，名称，描述， status 等存储在这里。子表（以OOP术语继承，从 Test 表），并具有自己的测试特定字段。每个测试参数都是它自己的列。子表具有表示测试实例的id列。例如，我们可能有10个测试，但是每个测试被给予100次，所以我们有1000个测试实例。

由于某些原因，我无法弄清楚如何建模关系。这是我需要帮助的地方。

我是否缺少另一个连接表来使其工作？如何加入访问号码到测试的实例？给定一个访问ID，如何找到访问期间给出的具体测试？如何建立父表和子表之间的关系？

这里有三个表。 TestABC表是一个特定的测试，并且将有许多其他测试。访问表和与之相关的一切，减去与测试的链接，已经完成。

  CREATE TABLE [dbo]。[访问]（
 [Id] [int] IDENTITY（1,1）NOT NULL，
 [ColumnV1] [nchar]（10）NULL，
 CONSTRAINT [PK_Visit] 
 
 CREATE TABLE [dbo]。[Test]（
 [Id] [int] IDENTITY（1,1）NOT NULL，
 [Name] [nvarchar]（50）NULL，
 [描述] [nvarchar]（50）NULL，
 CONSTRAINT [PK_Test] 
 
 CREATE TABLE [dbo]。[TestABC]（
 [Id] [int] NOT NULL， 
 [Parameter1] [nvarchar]（50）NULL，
 [Parameter2] [nvarchar]（50）NULL，
 CONSTRAINT [PK_TestABC] 
  / pre> 
 
 
（1）我删除了我早期关于在键值对和实体类型表之间进行选择的问题，我选择避免因为较少的KVP路而冒险 -  KVP的幸福报告建筑经验和我对强类型值的偏好。
解决方案
如果给定的测试完成/执行/创建只是因为访问，并且只是因为 访问，然后将VisitId作为外键添加到测试表中，以指示父/拥有/定义Viist。
 
 
 
类型/子类型更为棘手。
 
 
 
如果TestABC是Test的子类型，那么在TestABC中，主键应该是TestId ，而且它也应该是Table Test Test的外键。 [em]我从来没有使用ID代替主键列 - 在编写复杂的连接时，它太快了。
 
 
 
听起来像有很多测试子类型。听起来也像是排他性的，例如表中TestABC中的数据的测试不能在表TestXYZ或任何其他子类型表中具有数据。要确定哪种类型的测试，您必须查询每个子表以查找它是什么样的测试。然而，这只是可怕的，所以反而添加一个TestType列到测试表。也许最好有一个查找表（TestType），使用外键，以确保没有任何方式的测试类型潜入系统。
 
 
 
一个微妙的问题是如何防止数据从给定的测试中输入到多个子类型表。为了保证准确性可能是过于夸张，但如果您将安心和关系诚信视为价值，请执行以下操作：
 
 
 
 
 
添加每个子类型表的TestType列
 
 
向每个子类型表添加一个检查约束，使TestType列只能 设置为该子类型的相应代码
 
 
在测试表中，在{TestId，TestType} 
 
 
上构建主键在每个子类型表中，在{TestId，TestType返回表格测试（在两列中）
 
 
如果拥有测试条目是使用该表的TestType设置的，则只能向子类型表添加一行。 li> 
 
 
I have a table, VisitTest, that joins patient visits (Visit table) to diagnostic tests (Test table) given during that appointment. The Test table is a superclass (parent table) of tables containing actual tests. Each child table has the test parameters and results (1).

The Test table contains metadata. Columns like id, name, description, status, etc are stored here. The child tables (inherit, in OOP terms, from the Test table) and have their own test-specific fields. Each test parameter is its own column. The child tables have an id column that represents the test instance. For example we may have 10 tests but each test was given 100 times so we have 1000 test instances.

For some reason, I can't figure out how to model the relationships. That's where I need help.

Am I missing another join table to make this work? How to I join the visit number to the instance of the test? Given a visit id, how do I find the specific tests given during that visit? How do I model the relationship between the parent and child tables?

Here are three tables I have. The TestABC table is a specific test and there will be many other tests like it. The visit table and everything related to it, minus the linkage to tests, is complete.
CREATE TABLE [dbo].[Visit](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ColumnV1] [nchar](10) NULL,
 CONSTRAINT [PK_Visit]

    CREATE TABLE [dbo].[Test](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NULL,
        [Description] [nvarchar](50) NULL,
     CONSTRAINT [PK_Test] 

    CREATE TABLE [dbo].[TestABC](
        [Id] [int] NOT NULL,
        [Parameter1] [nvarchar](50) NULL,
        [Parameter2] [nvarchar](50) NULL,
     CONSTRAINT [PK_TestABC]
(1) I deleted my earlier question about choosing between key-value pair and entity-type tables. I opted to avoid venturing down the KVP road due to less-than-blissful report building experience with KVP and my preference for strongly-typed values.
解决方案
If a given Test is done/performed/created only because of a Visit, and only because of that  visit, then add VisitId as a foreign key to the Test table, to indicate the parent/owning/defining Viist.

Types/Subtypes are much trickier.

If  TestABC is a subtype of Test, then in TestABC, the primary key should be TestId, and it should also be a foreign key to table Test column TestId. [I never use just "ID" for surrogate primary key columns – it gets too confusing too fast when writing complex joins.]

Sounds like there are lots of Test subtypes. Sounds also like they’re exclusive, e.g. a Test with data in table TestABC cannot also have data in table TestXYZ, or any other subtype table. To determine which type of Test it is, you’d have to query each subtable to find what kind of test it is. That, however, is just awful, so instead add a TestType column to the Test table. Probably best to have a lookup table as well (TestType), with foreign keys to ensure no wayward test types sneak into the system.

A subtle issue is how to prevent data from being entered into multiple subtypes tables for  a given Test. To guarantee accuracy is perhaps overly-fussy, but if you consider peace of mind and relational integrity as worth the price, do this:


Add a TestType column to each subtype table
Add  a check constraint to each subtype table such that the TestType column can only be set to the appropriate code for that subtype
In the Test table, build the primary key on {TestId, TestType}
In each subtype table, build a foreign key on {TestId, TestType} back to table Test (on the two columns) 
Voila, you can only add a row to a subtype table if the "owning" Test entry is set with that table's TestType.


                        
这篇关于如何使用父子表建立连接表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！