数据建模

数据建模 - 如何处理两个依赖的“状态”列？(Data Modeling - how to handle two, dependent “status” columns?)

我遇到了一些让我烦恼的事情，我想来这里寻找一种“最佳实践”类型的建议来自你们（et gals）

我的模型中有一张桌子，我们称之为prospect 。 两个独立的外部系统可以为此表中的行提供更新，但仅作为相应系统中该记录的“状态”。

我需要在本地存储这些状态。 最初的想法，当然，它只是制作两个可以为空的外键。 像这样的东西。

+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | prospect_id | int(11) | NO | PRI | NULL | auto_increment | | ext_status_1_id | int(11) | YES | | NULL | | | ext_status_2_id | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+

在这个例子中，当然会有两个表保存状态的id / value对。

这是catch - ext_status_2_id将始终为NULL，除非ext_status_1_id为1 （这正是业务规则的工作方式）。

我是否正确建模了？ 我只是在我脑后的这个唠叨的声音告诉我“并非前景中的每一行都需要一个ext_status_2_id所以这可能不对”。

如果重要，这是MySQL 5.0.45，我正在使用InnoDB

I've run across something that's bugging me just enough that I wanted to come here and seek out a sort of "best practice" type of advice from you guys (et gals)

I have a table in my model, let's call it prospect. Two separate external systems can provide an update for rows in this table, but only as a "status" of that record in those respective systems.

I need to store those statuses locally. Initial idea, of course, it just to make two nullable foreign keys. Something like this.

+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | prospect_id | int(11) | NO | PRI | NULL | auto_increment | | ext_status_1_id | int(11) | YES | | NULL | | | ext_status_2_id | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+

In this example there would be, of course, two tables that hold id/value pairs for statuses.

Here's the catch - ext_status_2_id will always be NULL unless ext_status_1_id is 1 (this is just how the business rules work).

Have I modeled this correctly? I just have this nagging voice in the back of my brain telling me that "not every row in prospect will need an ext_status_2_id so this might not be right".

If it matters, this is MySQL 5.0.45 and I'm using InnoDB

最满意答案

由于Status1上的Status2存在内置依赖关系，为什么不在Pros表上只有一个状态字段，并在Status1表上创建Status2作为属性？ 它肯定以这种方式大量归一化，但以这种方式拥有数据结构说明了Status2对Status1的依赖性。

Since there is an in-built dependency for Status2 on Status1, why not just have a single status field on the prospect table, and create Status2 as a property on the Status1 table? It is certainly normalized heavily in this fashion but having the data structure this way speaks about the dependency of Status2 on Status1.