数据建模 - 如何处理两个依赖的“状态”列？

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)

我遇到了一些让我烦恼的事情,我想来这里寻找一些“最佳实践”类型的建议来自你们(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.

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

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).

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

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".

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

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

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

3 个解决方案

#1

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.

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

#2

This is probably fine. But since you'll always only use 1 of the 2, you could model it as :

这可能很好。但由于你总是只使用2中的1个,你可以将它建模为:

ext_status_type (either 1 or 2) and ext_status for the actual id.

ext_status_type(1或2)和ext_status表示实际的id。

I would probably do the same as you did, because it might be easier to build indexes around this and both numbers appear to have a true different meaning.

我可能会像你一样做,因为围绕这个建立索引可能更容易,两个数字似乎都有真正不同的含义。

If there will be more statuses (3,4,5,6) I would consider the first approach in my answer.

如果有更多的状态(3,4,5,6),我会在我的答案中考虑第一种方法。

#3

What are the possible ext__status__1? Will ext__status__2 have a value only if status__1=1? What is status__1=2? I agree partially with Nissan Fan. Is there, however a direct dependency between status__1 and Status__2? Is there a Functional dependency of the form status__1 -> Status__2?
If there is no such dependence then keeping status__1 and Status__2 in a separate table does not solve your problem.

有什么可能的ext__status__1?只有当status__1 = 1时,ext__status__2才会有值吗?什么是status__1 = 2?我同意日产范。是否存在status__1和Status__2之间的直接依赖关系?表单status__1 - > Status__2是否存在功能依赖?如果没有这种依赖性,那么将status__1和Status__2保存在一个单独的表中并不能解决您的问题。

#1

#2