SQL表设计:具有多个一对一关系的表。

huangapple go评论48阅读模式
英文:

SQL table design: table with multiple one-to-one relationships

问题

在SQL中,我有一个表设置:

RegisterTable
----
DocId int
status int
docType int
CarDocument Table
----
carDocId int (PK, FK -> RegisterTable)
name string
carMake varchar
EmployeeDocument
----
emplyeeDocId int (PK, FK -> RegisterTable)
name varchar
age int

这是一个关于文档的数据库。表的设计与问题无关。
所以我有不同的文档,如汽车/员工等... - 它们都有完全不同的字段集,互不相关。

我需要为这些文档创建元数据,这在RegisterTable中表示。这些文数据在文档之间是相似的。所以它有点像继承。

对于这种情况,数据库设计是什么?目前我创建了三个单独的表,并从CarDocument/EmployeeDpcument到RegisterTable创建了一对一关系。

当我创建一个文档时,我首先在RegisterTable中创建其元数据,然后获取密钥并使用它来在相应的CarDocument或EmployeeDocument表中创建文档。

这样做可以工作,但对我来说看起来有点繁琐。

额外信息:我有10-20个不同的文档表。
我使用typeorm作为我的ORM解决方案。

研究:与https://stackoverflow.com/questions/5750456/table-has-one-to-one-relationship-with-many-tables相似

我的设计可以工作,但RegisterTable有点虚假,因为它包含了所有docId。对于这种情况,最佳的数据库设计是什么?

英文:

In SQL I have a table setup

RegisterTable
----
DocId int
status int
docType int
CarDocument Table
----
carDocId int (PK, FK -> RegisterTable)
name string
carMake varchar
EmployeeDocument
----
emplyeeDocId int (PK, FK -> RegisterTable)
name varchar
age int

This is a database about documents. Tables design have no relevance to the question.
So I have different documents Cars/Emplyees/etc... -- they all have completely different set of fields, unrelated.

I need to have metadata for these documents, which is represented in RegisterTable. This metadata is similar across documents. So it's a bit like inheritance.

Which is the DB design for this case? Currently I made three separate tables and created one-to-one relation from CarDocument/EmployeeDpcument to RegisterTable.

When I create a document, I first create it's metadata in RegisterTable, then I take the key and use it to create a document in corresponding CarDocument or EmployeeDocument table.

This works but looks cumbersome to me.

Extra info: I have 10-20 different document tables.
I use typeorm as my ORM solution.

Research:
Has similarities with https://stackoverflow.com/questions/5750456/table-has-one-to-one-relationship-with-many-tables

My design works but RegisterTable is kinda fake since it holds all the docIds.
Which is the best DB design for this case?

答案1

得分: 1

Postgres实际上支持继承 - 请参见https://www.postgresql.org/docs/current/tutorial-inheritance.html

除此之外,如果您拥有在各种文档类型中始终相同的元数据,那么您的方法,即在元数据表中建立与文档表的关系,原则上是正确的(请参见下文)。

元数据表本身不需要知道引用它的表。您的查询逻辑可以根据docType和docId来获取正确的辅助文档。

对于您的特定情况,如您上面发布的,如果一个名为"status"的字段是您在该表中唯一实际的元数据,我认为您最好是将该字段简单地添加到文档表中。只有在您有一组固定的元数据,不想在许多不同的表中复制时,将其拆分成自己的表才有意义。

英文:

Postgres actually does inheritance - see https://www.postgresql.org/docs/current/tutorial-inheritance.html

Aside from that, if you have metadata that is always the same across various types of documents, your approach to have a metadata table with a relation to the document tables is the right one, in principle (see below).

The metadata table itself does not need to know about the tables that reference it. Your query logic can derive the correct secondary document from the docType and the docId.

For your specific case, as you've posted it above, if a single "status" field is the only actual metadata you hold in that table, I think you would be better off to simply add that field to the document tables. Only if you have a fixed set of metadata that you don't want to replicate over many different tables does it make sense to split it into its own table.

答案2

得分: 0

有一种更灵活和可扩展的方法可以使用。

一个单独的表将存储所有文档的元数据,然后针对每种文档类型另建一个独立的表,用于存储该类型文档的具体细节。

RegisterTable 可以重命名为 DocumentMetadata,其中包含 DocIdstatusdocType 等信息。

CarDocumentEmployeeDocument 表包含针对每种类型的特定列,如 carMakeage

可以通过从 DocumentMetadata 表到文档特定表的外键来绑定这些表。

这不仅更加灵活,因为您可以不断添加新类型的文档,而且还避免了创建一个没有任何实际信息的无意义表(RegisterTable)。

英文:

There's a more flexible and scalable approach that can be used.

A single table would store all document metadata and then another separate table for each document type that stores specific details for that type of doc.

RegisterTable can be renamed to DocumentMetadata and contains DocId, status, docType etc.

CarDocument and EmployeeDocument tables contain columns that are specific to each type such as carMake and age.

Can bind the tables via Foreign Key from DocumentMetadata table to document-specific tables

It's not only more flexible because you can keep adding new types of docs, but also avoids creation of a meaningless table that doesn't have any real info (RegisterTable)

答案3

得分: 0

我看不出你的设计有什么问题。无论如何,一个关键点是决定是否要分享所有实体/表的ID(就像你现在在做的那样),还是使用单独的ID。第二个选择可能更加整洁和灵活。你可以有类似这样的结构:

RegisterTable
----
docId int
status int
docType int

CarDocument
----
carDocId int(主键)
docId int(外键-> RegisterTable)
name string
carMake varchar

EmployeeDocument
----
employeeDocId int(主键)
docId int(外键-> RegisterTable)
name varchar
age int

当然,你也可以只使用一个大表,具有许多字段,根据docType填充每个字段(或不填充),可能对于每个不同的docType具有不同的语义(不,我在开玩笑,不要这样做)。

英文:

I see nothing wrong with your design. One key point, anyway, is deciding if you'll share the IDs for all your entities/tables (as you're doing) or have separate IDs. The second choice may be the more tidy and flexible. You'll have something like this:

RegisterTable
----
docId int
status int
docType int

CarDocument
----
carDocId int (PK)
docId int (FK-> RegisterTable)
name string
carMake varchar

EmployeeDocument
----
emplyeeDocId int (PK)
docId int (FK-> RegisterTable)
name varchar
age int

Of course, you can also have just ONE big table with a lot of fields, filling each field (or not) depending on the docType, and maybe with different semantics for each different docType (no, I'm joking, don't do that).

huangapple
  • 本文由 发表于 2023年2月18日 00:16:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75486760.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定