StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POData Modeling - how to handle two, dependent "status" columns?
text
Body
copied!<p>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)</p> <p>I have a table in my model, let's call it <code>prospect</code>. 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.</p> <p>I need to store those statuses locally. Initial idea, of course, it just to make two nullable foreign keys. Something like this.</p> <pre><code>+-----------------+--------------+------+-----+---------+----------------+ | 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 | | +-----------------+--------------+------+-----+---------+----------------+ </code></pre> <p>In this example there would be, of course, two tables that hold id/value pairs for statuses.</p> <p>Here's the catch - <code>ext_status_2_id</code> will <em>always</em> be NULL unless <code>ext_status_1_id</code> is <code>1</code> (this is just how the business rules work).</p> <p>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 <code>ext_status_2_id</code> so this might not be right".</p> <p>If it matters, this is MySQL 5.0.45 and I'm using InnoDB</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload