数据库设计 - 两个项目应该共享同一个表吗？ [英] Database design - should two projects share the same table?

问题描述

背景：

同时设计的两个项目（A& B）都需要一个新表（称为 DocumentStore ）来存储

但是文档存储的业务逻辑在项目A和B之间是不同的。 B，这意味着 DocumentStore 之间的关系在A& B。

让我们更具体一点，见下面的例子：

文档存储表结构没有约束/外键的相同：

表 DocumentStore b
$ b

  DocUUID //此文档的唯一ID，PK，FK到其他表取决于项目
 fileName //文件名
 fileType //文件类型
 FileContent //将文件存储为博客
  

< A ， DocumentStore.DocUUID 参考 Email.EmailUUID ：

请注意，通过FK，电子邮件 - > DocumentStore之间有一对多的关系。

表 code>

  EmailUUID // PK 
 subject 
 title 
 ... 
  

在项目 B ， DocumentStore.DocUUID 参考 Letter.LetterUUID ：

请注意，

表

  LetterUUID // PK 
 UserId 
 rightId 
 ... 
  

电子邮件和 Letter 因为不同的业务逻辑而完全不同。

---

我的问题是：

1. 我应该在项目A和B之间分享这个 DocumentStore B？




2. 如果1的答案是肯定的，那么如何？




3. 如果对1.的答案是否定的，我应该创建两个具有相同结构，不同表名和不同外键的表吗？一个用于项目A和项目B？

解决方案

约束对于表的同一实例的每列执行。您必须为每个fk添加一列。或者有两个 documentstore 表。

当你澄清时， documentstore 属于字母或电子邮件，但只属于其中一个，而每个字母/电子邮件可以有多个文档。

因此，我的新建议：坚持使用您现在的表格设计，但创建两个单独的表。将它们放在同一个表中没有什么好处。

您可以 有两个共享相同结构的事实没有很好的理由 schema_a.documentstore 和 schema_b.documentstore 继承自 master.documentstore 。这将主要是有用的，如果你有一次处理两个表中的所有行的用例。请务必阅读有关继承限制的章节Postgres 。特别是，它不允许你定义一个fk约束：

继承特性的一个严重限制是索引
（包括唯一约束）和外键约束只应用
到单个表，而不是它们的继承孩子。

有关代码示例的相关答案： br>
了解基于表值

在PostgreSQL中创建两种类型的表

Background:
Two projects (A & B) under design at the same time both needs a new table(called DocumentStore) to store document/file under postgres.

But business logic around the document storage are different between project A & B, this means relationship around DocumentStore are different between A & B.

Let's make this a bit more concrete, see example below:

The Document storage table structure looks the same without constraints/ foreign Keys:

Table DocumentStore

DocUUID //unique Id for this document, PK, FK to other table depends on project 
fileName //file name
fileType //file type
FileContent //store file as blog

In project A, DocumentStore.DocUUID references Email.EmailUUID:

Note there is a one to many relationship between Email -> DocumentStore via the FK.

Table Email

EmailUUID //PK
subject
title
...

In project B, DocumentStore.DocUUID references Letter.LetterUUID:

Note there is a one to many relationship between Letter -> DocumentStore via the FK.

Table Letter

LetterUUID //PK
UserId
rightId
...

Email and Letter are completely different because of different of business logic.

---

My questions are:

1. Should I share this DocumentStore table between project A & B ?

2. If the answer to 1. is yes, then how? Through inheritance under postgres?

3. If the answer to 1. is no, should I create two table with the same structure but different table name and different foreign key ? One for project A and project B?

解决方案

Only one of those fk constraints works per column of the same instance of the table. You would have to add one column for each fk. Or have two documentstore tables.

As you clarified, the same row in documentstore belongs to either a letter or an email, but only to a single one of those, while each letter / email can have multiple documents.
Hence my new advice: stick with table design you have now, but create two separate tables. There is no gain in having them in the same table. The fact that both tables share the same structure is no good reason to share the data.

You can have schema_a.documentstore and schema_b.documentstore inheriting from master.documentstore. That would mainly be useful if you have use cases dealing with all rows in both tables at once. Be sure to read the chapter about limitations of inheritance in Postgres. In particular, it won't allow you to define a single fk constraint:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

Related answers with code examples:
Find out which schema based on table values
Create a table of two types in PostgreSQL

这篇关于数据库设计 - 两个项目应该共享同一个表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！