有两个具有双列行对应关系的SQL表是一种好习惯吗？ [英] Is it good practice to have two SQL tables with bijective row correspondence?

问题描述

我有一张任务表，

  id |名称
 ---- + ------------- 
 1 |刷牙
 2 |洗衣服
  

和一张状态表。

  taskid |状态
 -------- + ------------- 
 1 |已完成
 2 |未完成的
  

表之间存在双射对应关系，即 task 表恰好与 state 表中的一行相对应。

另一种实现方法是将 state 行放在 task 表中。

  id |名称|状态
 ---- + ------------- + ------------- 
 1 |刷牙|已完成
 2 |洗衣服|未完成的
  

我选择使用两个表而不是一个表的主要原因是因为更新了然后，状态将导致任务 id 的更改。
我还有其他表引用 task（id）列，并且不想在更改任务状态时也必须更新所有其他表。

对此我有两个问题。

2. 是否有两种方法可以保证约束条件：状态表与任务表中的每一行相对应？

我正在使用的系统是 postgresql 。

解决方案

您可以通过将每个表中的id用作主键和引用另一个表中的id的外键来确保1-1对应。这是允许的，它可以保证1-1的身高。

有时候，您想要这样的表，但是一个表的行比另一个表少。当存在子集关系并且您不希望所有行上都有其他列时，就会发生这种情况。

另一个目的是将单独的列存储在不同的位置。当我了解数据库时，这种方法称为垂直分区。如今，柱状数据库相对普遍。这些将概念带到了极致–每列都有一个单独的存储（尽管存储并不完全是表）。

为什么做这个？以下是一些原因：

• 您不经常使用的列是您不想为更频繁的列上的每个查询加载的。 li>
  
• 您经常更新列，并且不想锁定其余的列。


• 列太多，无法存储在一行中。 / li>
  
• 您在不同的列上有不同的安全要求。

Postgres确实提供了其他可能的机制找到相关的。特别地，表继承在您的情况下可能很有用。

总而言之，您通常不会设计这样的数据库。这样做有充分的理由，但是更常见的是将与实体相关的所有列都放在同一张表中。

I have a table of tasks,

 id | name
----+-------------
 1  | brush teeth
 2  | do laundry

and a table of states.

 taskid | state
--------+-------------
 1      | completed
 2      | uncompleted

There is a bijective correspondence between the tables, i.e. each row in the task table corresponds to exactly one row in the state table.

Another way of implementing this would be to place a state row in the task table.

 id | name        | state
----+-------------+-------------
 1  | brush teeth | completed
 2  | do laundry  | uncompleted

The main reason why I have selected to use two tables instead of this one, is because updating the state will then cause a change in the task id. I have other tables referencing the task(id) column, and do not want to have to update all those other tables too when altering a task's state.

I have two questions about this.

1. Is it good practice to have two tables in bijective row-row correspondence?
2. Is there a way I can ensure a constraint that there is exactly one row in the state table corresponding to each row in the task table?

The system I am using is postgresql.

解决方案

You can ensure the 1-1 correspondence by making the id in each table a primary key and a foreign key that references the id in the other table. This is allowed and it guarantees 1-1'ness.

Sometimes, you want such tables, but one table has fewer rows than the other. This occurs when there is a subsetting relationship, and you don't want the additional columns on all rows.

Another purpose is to store separate columns in different places. When I learned about databases, this approach was called vertical partitioning. Nowadays, columnar databases are relatively common; these take the notion to the extreme -- a separate "store" for each column (although the "store" is not exactly a "table").

Why would you do this? Here are some reasons:

• You have infrequently used columns that you do not want to load for every query on the more frequent columns.
• You have frequently updated columns and you do not want to lock the rest of the columns.
• You have too many columns to store in one row.
• You have different security requirements on different columns.

Postgres does offer other mechanisms that you might find relevant. In particular, table inheritance might be useful in your situation.

All that said, you would not normally design a database like this. There are good reasons for doing so, but it is more typical to put all columns related to an entity in the same table.

这篇关于有两个具有双列行对应关系的SQL表是一种好习惯吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！