设计“一排”关系的正确方法是什么？ [英] what is the correct way to design a 'table to row' relationship?

问题描述

我试图在postgres数据库中对以下内容进行建模。

我有N个数据集。这些数据集是调查结果，国家统计数据，汇总数据等等。它们每个都有一个名字来源，一个方法等等。这是一个数据集的元数据，我为此创建了表格，用于编纂研究方法等'根'元数据表称为数据集。每行代表一个数据集。

然后，我需要存储和访问与此数据集关联的实际数据。所以我需要创建一个包含该数据的表。如何在数据集表格中表示此表与其对应行之间的关系？

一个示例

< hea是一组调查回应。它是分开的，所以每一行都是一个调查回应。我创建一个名为HeaData的表，其中包含这些数据。

'cso'是一组聚合的就业数据。每一行都是经济部门。我创建一个名为'CsoData'的表，其中包含这个数据

我在数据集表中为每个表创建了一行，每个表的相关元数据有1& 2分别为

将1与HeaData表和2连接到CsoData表的最佳方式是什么？

我最终将以scala光滑的方式访问这些数据，所以如果数据库设计只能通过光滑的即插即用，这将是理想的。

解决方案

将一列添加到Datasets表中，该表指定它所代表的数据集类型。那么1可能意味着HEA，2可能意味着CSO。检查约束将该字段限制为两个值之一。如果稍后添加新类型的数据集，则唯一需要改变的是更改约束。如果将其定义为数据集类型表的外键，则只需要在其中添加新的数据集类型。

在PK和新字段。

将相同的字段添加到每个子表。但是，检查约束将HEA表中的值限制为仅该值，并将CSO表仅限制该值。然后形成Datasets表的ID字段，将新字段形成为FK to Datasets表。

这将ID值限制为只有一个子表，它必须是一个在Datasets表中定义。也就是说，如果您定义了ID值为1000的HEA数据集条目和HEA类型值，则唯一可以包含ID值为1000的子表是HEA表。

创建表数据集（
ID int identity / auto_generate，
DSType char（3）检查（DSType in（'HEA'，'CSO'），
[一切]，
约束PK_Datasets主键（ID），
约束UQ_Dateset_Type唯一（ID，DSType） - 需要参考
）;

创建表HEA（
ID int not null，
DSType char（3）检查（DSType ='HEA'） - 使其为常量值
[其他HEA数据]，
约束PK_HEA主键（ID），
约束FK_HEA_Dataset_PK外键（ID）
引用数据集（ID），
约束FK_HEA_Dataset_Type外键（ID，DSType）
引用数据集（ID，DSType）
）;

与CSO子表相同的想法。 p>

我会推荐一个HEA和CSO视图，显示完整的数据集行，元数据和类型特定的数据，连接在一起。使用这些视图的触发器，它们可以是应用程序代码的DML点。然后，应用程序不必跟踪数据库中的数据是如何布局的，如果机会出现本身，则可以轻松进行改进。

I am trying to model the following in a postgres db.

I have N number of 'datasets'. These datasets are things like survey results, national statistics, aggregated data etc. They each have a name a source insitution a method etc. This is the meta data of a dataset and I have tables created for this and tables for codifying the research methods etc. The 'root' meta-data table is called 'Datasets'. Each row represents one dataset.

I then need to store and access the actual data associated with this dataset. So I need to create a table that contains that data. How do I represent the relationship between this table and its corresponding row in the 'Datasets' table?

an example

'hea' is a set of survey responses. it is unaggregated so each row is one survey response. I create a table called 'HeaData' that contains this data.

'cso' is a set of aggregated employment data. each row is a economic sector. I create a table called 'CsoData' that contains this data

I create a row for each of these in the 'datasets' table with the relevant meta data for each and they have ids of 1 & 2 respectively.

what is the best way to relate 1 to the HeaData table and 2 to the CsoData table?

I will eventually be accessing this data with scala slick so if the database design could just 'plug and play' with slick that would be ideal

解决方案

Add a column to the Datasets table which designates which type of dataset it represents. Then a 1 may mean HEA and 2 may mean CSO. A check constraint would limit the field to one of the two values. If new types of datasets are added later, the only change needed is to change the constraint. If it is defined as a foreign key to a "type of dataset" table, you just need to add the new type of dataset there.

Form a unique index on the PK and the new field.

Add the same field to each of the subtables. But the check constraint limits the value in the HEA table to only that value and the CSO table to only that value. Then form the ID field of Datasets table and the new field as the FK to Datasets table.

This limits the ID value to only one of the subtables and it must be the one defined in the Datasets table. That is, if you define a HEA dataset entry with an ID value of 1000 and the HEA type value, the only subtable that can contain an ID value of 1000 is the HEA table.

create table Datasets(
    ID     int identity/auto_generate,
    DSType char( 3 ) check( DSType in( 'HEA', 'CSO' ),
    [everything else],
    constraint PK_Datasets primary key( ID ),
    constraint UQ_Dateset_Type unique( ID, DSType ) -- needed for references
);

create table HEA(
    ID     int not null,
    DSType char( 3 ) check( DSType = 'HEA' ) -- making this a constant value
    [other HEA data],
    constraint PK_HEA primary key( ID ),
    constraint FK_HEA_Dataset_PK foreign key( ID )
        references Dataset( ID ),
    constraint FK_HEA_Dataset_Type foreign key( ID, DSType )
        references Dataset( ID, DSType )
);

The same idea with the CSO subtable.

I would recommend an HEA and CSO view that would show the complete dataset rows, metadata and type-specific data, joined together. With triggers on those views, they can be the DML points for the application code. Then the apps don't have to keep track of how that data is laid out in the database, making it a lot easier to make improvements should the opportunity present itself.

这篇关于设计“一排”关系的正确方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！