Modeling Relational Entities Problem

Answer 1

Once you draw in all the relationships, they're the same.

I don't understand why you have state_id in the question table -- since you have the historical table, having the state in the question table is redundant and can leave you with out-of-sync data.

Seems to me that if you want the current state on a question, you do

SELECT State_ID FROM Historical WHERE Question_id = ? ORDER BY Date DESC LIMIT 1

(or whatever method your flavor of SQL uses to limit to just 1 row)

Answer 2

I think the gist of what you're asking is: Should the state of the question be based on an intermediary table between Questions and State that has a time component (A) or should the table be more static, but with a log-oriented history table on the side (B).

(Note: If you wanted to do a pure version of (A), then Boofus is right, you probably wouldn't put the state_id in the Questions table as well, as it's redundant; but that would definitely be inconvenient because it would make simple queries to get questions in a particular state much harder. So you've got a hybrid version here.)

In general, if the requirement of keeping historical information about the state is really just for audit purposes - that is, if it won't be regularly queried by the application itself - you're probably better off going with option B, because it's a little simpler (there's really just the one "Questions" table, with a reference table for the states, and a "log" table for previous states). I think that shows your intent a little better.

However, if the application semantics are more complex (e.g., if you have queries like "show all questions that have been in state X within the last 24 hours ..."), then an approach like (A) might make more sense. It's essentially making the state of a question into a time-dependent fact. If you do that, just be aware that it complicates things - either all your queries are harder and have to consider time, or you have to worry about keeping the state_id on Questions in sync with the most recent state in the Questions table. If you go that route, maybe call it "current_state" or something on Questions, so it's clear that it's sort of derivative information.

Answer 3

Assuming you have good abstraction layers between the database and your OO, you might consider taking the State table out of the database and make it an enumeration in a class. It's not necessarily something that needs to be persisted.

Then have the State column in the Questions table, and the audit table.

Answer 4

You say audited, which implies that you simply wish to retain historical information for reporting purposes. In which case I'd suggest that diagram B is the clearer, although you should probably mark the one to many relationships between Questions and Historical and State and Historical too.

As to practicalities, if the circumstances are as above, myself I would encapsulate the Historical insert functionality into an insert/update trigger on Questions, and if the volume of the Questions table and/or the number of state changes is going to be significant I would consider putting Historical table in a different database. This just eases database management later. Normally I am wary of triggers as over-zealous usage can lead to hard-to-maintain databases (as it's not immediately obvious what is going on) but this is a clear case where they are a good fit and are a superior choice to using application logic.

Incidentally both your two diagrams imply that a question can only ever enter each state once (from your PK) - you should consider if this is correct as in most real world applications mistakes will be made and states reversed.

Answer 5

You might want to scour the web on the subject of "temporal databases". Basically, storing the history of the changes of any variable raises the same problems, regardless of whether the variable captures question state or person's weight or whatever.

Second, I think your question relates to database design, and not to conceptual data modeling. If I get your drift right, you are asking which table design is better.

Third, I like option B better, but it really depends on what you are going to do with the data.

The reason I asked about database design versus conceptual modeling is that I long ago adopted the practice of using "entities and relationships" for conceptual data modeling associated with data analysis. I use the terms "tables, columns, and rows" when discussing logical database design. Keeping analysis and design separate turns out to be very valuable in large projects. And it isn't as easy to do as it sounds.

You really ought to add an arrow between the Historical table and the State table in the diagram for option B. The way the diagram is presented, it almost looks like the Historical table is a disjoint table. Not a problem in this simple example, but if you keep the same practice when you scale up to databases with dozens of tables, you'll end up confusing everybody who looks at the diagram.