Data Modeling - how to handle two, dependent "status" columns?

Question

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

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

Answer 2

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

ext_status_type (either 1 or 2) and ext_status for the actual 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.

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