Linux 数据库表设计

Database table design

提问by e.b.white

I design the tables as below for the system
which looks like a package delivering system.

我为
看起来像包裹递送系统的系统设计了如下表格。

For example, after user received the package, postman should record in system,
and the state(history table) is "delivered",and operator is this postman,
the current state(state table) is of course "delivered"

例如，用户收到包裹后，邮递员要在系统中记录，
状态（历史表）是“已送达”，而操作员就是这个邮递员，
当前状态（状态表）当然是“已送达”

history table:
+---------------+--------------------------+
|   Field       |        Desc              |
+---------------+--------------------------+
|    id         |    PRIMARY KEY           |
+---------------+--------------------------+
|    package_id |    package_tacking_id    |
+---------------+--------------------------+
|    state      |     package_state        |  
+---------------+--------------------------+      
|    operators  |    operators             |
+---------------+--------------------------+
|    create_time|    create_time           |
+---------------+--------------------------+   

state table:
+---------------+--------------------------+
|   Field       |        Desc              |
+---------------+--------------------------+
|    id         |    PRIMARY KEY           |
+---------------+--------------------------+ 
|   package_id  |    package_tacking_id    |
+---------------+--------------------------+
|    state      |    latest_package_state  |
+---------------+--------------------------+  

Above is just the basic information to record, some other information(
like invoice, destination,...) should be recorded as well.
But there are different service types like s1 and s2, for s1 it is not needed
to record invoice but s1 need, and maybe s1 need some other information to record
(like the tel of end user).

以上只是需要记录的基本信息，其他一些信息（
如发票、目的地等）也需要记录。
但是有s1和s2等不同的服务类型，s1不需要
记录invoice，s1需要，可能s1还需要一些其他的信息来记录
（比如终端用户的电话）。

After all, at delivering way stations there are additional information to record,
and for different service type the information type is different.

毕竟，在中转站有额外的信息需要记录，
而且不同服务类型的信息类型是不同的。

My questions:

我的问题：

1. For different service type, shall I need to declare different tables(option A) or just
   one big table which can record all information for all types(option B)?
2. If option A, since the basic information above is MUST, how
   can prevent from declaring there duplicate fields in different tables?

1. 对于不同的服务类型，我需要声明不同的表（选项A）还是只需要
   一张可以记录所有类型的所有信息的大表（选项B）？
2. 如果选项A，由于上面的基本信息是MUST，如何
   防止在不同的表中声明重复的字段？

采纳答案by Mike Lue

I have no completed answer for your issue, but you may try inheritance patternin table design.

对于您的问题，我没有完整的答案，但您可以在表设计中尝试继承模式。

Service Table(service) : used to put common or not-null columns in all kinds of services.
sv_id - internal key(PK)
sv_data_1, sv_data_2, ...

Service 1 Table : dedicated attributes to service 1 and 'inherited from service'.
sv1_id - internal key(PK) and foreign key to service(sv_id)
sv1_data_1, sv1_data_2, ...

And so on to other dedicated service table...

依此类推到其他专用服务表...

1. For convenience(and performance), adding a 'sv_type' column to represent the exact 'sub-type table' of servcie if you need to list all kinds of services.
2. Otherwise, just using 'inner join' to get the specific type of service.

1. 为了方便（和性能），如果您需要列出所有类型的服务，请添加一个“sv_type”列来表示 servcie 的确切“子类型表”。
2. 否则，只需使用“内连接”来获取特定类型的服务。

Although we need to use 'join' to get all attributes of a specific type of service, but the schema showed would construct the 'null-free' solution for your issue.

虽然我们需要使用“join”来获取特定类型服务的所有属性，但显示的架构将为您的问题构建“null-free”解决方案。

If you care about the performance issue, there is one positive thing: joining tables for a specific type of service is accomplished with two primary keys.

如果您关心性能问题，有一件好事：连接特定类型服务的表是通过两个主键完成的。

More about performance, you can create cached tables with joined base tables when you need speedier query and don't worry about null-error and type-error issue of services.

更多关于性能，当您需要更快的查询并且不用担心服务的空错误和类型错误问题时，您可以创建带有连接基表的缓存表。