亲子设计可轻松识别孩子的类型 [英] Parent child design to easily identify child type

问题描述

在我们的数据库设计中，我们有几个表描述了不同的对象，但它们具有相同的基本类型.由于描述实际的表以及每一列的工作将花费很长时间，因此我将尝试通过使用基于作业数据库的类似结构化示例来简化它.

In our database design we have a couple of tables that describe different objects but which are of the same basic type. As describing the actual tables and what each column is doing would take a long time I'm going to try to simplify it by using a similar structured example based on a job database.

假设我们有以下表格:

这些表之间没有连接，但共享相同的列.因此，第一步是统一相同的列并引入一个唯一的personId:

These tables have no connections between each other but share identical columns. So the first step was to unify the identical columns and introduce a unique personId:

现在我们面对面的拥有"header"列，然后使用personId PK作为FK，使用一对一关系将其链接到更具体的作业表.在我们的用例中，一个人只能做一份工作，因此personId在出租车司机，程序员和建筑工人表中也是唯一的.

Now we have the "header" columns in person that are then linked to the more specific job tables using a 1 to 1 relation using the personId PK as the FK. In our use case a person can only ever have one job so the personId is also unique across the Taxi driver, Programmer and Construction worker tables.

虽然这种结构有效，但我们现在有一个用例，其中在我们的应用程序中，我们获得personId并希望获得各个作业表的数据.这使我们面临一个问题，即我们无法立即知道具有这个personId的人正在从事什么样的工作.

While this structure works we now have the use case where in our application we get the personId and want to get the data of the respective job table. This gets us to the problem that we can't immediately know what kind of job the person with this personId is doing.

我们提出了一些解决此问题的方法:

A few options we came up with to solve this issue:

这意味着仅保留体系结构，然后在后端代码中查找正确的表.这可能意味着查看每个存在的表和/或构造一个半复杂的联接选择，在其中我们必须筛选所有列以找到要填充的列.

This means just leaving the architecture as it is and look for the right table in the backend code. This could mean looking through every table present and/or construct a semi-complicated join select in which we have to sift through all columns to find the ones which are filled.

总而言之:可能，但是意味着很多不必要的选择.我们还希望在实际数据库中保留这种面向数据库的逻辑.

All in all: Possible but means a lot of unecessary selects. We also would like to keep such database oriented logic in the actual database.

这意味着在人员表中添加一个字段列，例如用数字确定正确的子表，例如:

This means adding a field column in the Person table filled for example with numbers to determine the correct child table like:

因此，如果是出租车司机，则可以在Type中添加0；如果是程序员，则可以在Type中添加1，依此类推...

So you could add a 0 in Type if it's a taxi driver, a 1 if it's a programmer and so on...

虽然这大大减少了后端逻辑的数量，但是我们必须确保我们在类型"字段中使用的数字在后端是已知的并且永远不会改变.

While this greatly reduced the amount of backend logic we then have to make sure that the numbers we use in the Type field are known in the backend and don't ever change.

这意味着每个工作都会在Person中获得自己的ID(必须为可空)，例如:

That means every job gets its own ID (has to be nullable) in Person like:

现在，很容易找出每个人都有哪些工作，因为其他人的ID为空.

Now it's easy to find out which job each person has due to the others having an empty ID.

所以我的问题是:最佳设计是哪一种?我在这里错过了一个明显的解决方案吗?

So my question is: Which one of these designs is the best practice? Am i missing an obvious solution here?

推荐答案

我们现在决定采用 second选项，因为它似乎具有其他评论者所描述的最少的缺点.和海报.由于没有实际答案将第二种方法描述为解决方案，因此我将尝试总结一下我们的理由:

We've now decided to go with the second option because it seem to come with the least drawbacks as described by the other commenters and posters. As there was no actual answer portraying the second option as a solution i will try to summarize our reasoning:

针对选项1:

无法通过查看父表来区分类型.结果，后端将必须包含所有逻辑，其中包括扫描所有表以查找包含id的.尽管您可以将大多数逻辑压缩到一个大的Join选择中，但与其他选项相比，它仍然是更多的逻辑.

There is no way to distinguish the type from looking at the parent table. As a result the backend would have to include all logic which includes scanning all tables for the that contains the id. While you can compress most of the logic into a single big Join select it would still be a lot more logic as opposed to the other options.

针对选项3:

正如@ yuri-g所说，由于单独的ID不能设置为主键，因此这在技术上是不可能的.它们必须是可为空的，并且因此不能被索引，这实际上使父表无用，因为其原因是在整个表中拥有唯一的personID.

As @yuri-g said this one is technically not possible as the separate IDs could not setup as primary keys. They would have to be nullable and as a result can't be indexed, essentially rendering the parent table useless as one of the reasons for it was to have a unique personID across the tables.

针对包含所有列的单个表:

对于较小的用例(如我在问题中描述的用例)，这可能是可行的，但是我们正在谈论一堆表，每个表大约有2-6列.这样一来，该选项就会很快变成专栏文章.

For smaller use cases as the one i described in the question this might me viable but we are talking about a bunch of tables with each having roughly 2-6 columns. This would make this option turn into a column-mess really quickly.

针对带有键值表的平面设计:

我们的属性具有完全不同的数据类型，不同的约束和外键关系.在设计中，所有这些都是不可能的/困难的.

Our properties have completly different data types, different constraints and foreign key relations. All of this would not be possible/difficult in this design.

针对包含子项特定属性的自定义数据库对象:

虽然@Matthew McPeak建议的此选项对于许多人来说可能是一个可行的选择，但我们的数据库设计从未真正使用过对象，因此将它们引入混合可能会引起混乱，而不是帮助我们.

While this option that @Matthew McPeak suggested might be a viable option for a lot of people our database design never really used objects so introducing them to the mix would likely cause confusion more than it would help us.

此选项易于在我们的面向表的数据库结构中使用，可以轻松区分适当的子表，并且无需进行大量修改.尤其是因为我们已经拥有类似于Type表的内容，因此我们可以轻松地将其用于此目的.

This option is easy to use in our table oriented database structure, makes it easy to distinguish the proper child table and does not need a lot of reworking to introduce. Especially since we already have something similar to a Type table that we can easily use for this purpose.

这篇关于亲子设计可轻松识别孩子的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！