Saving State in the Database

Saving state to a database is a common solution, but how do we do this effectively? I'm going to give you three solutions to this problem and then let you make your own mind up.

Database Design One: Single Table and Many Bit Fields

Pro's

All data is stored in the one database table so could be an effective solution if the state model is kept simple. In turn, the SQL to return the current state would be simple; no table joins required.

Con's

This is not an extensible solution and would become harder to maintain as business process and state evolves, we wouldn't be able to track state-change history and keeping state fields mutually exclusive could be error-prone.

Database Design Two: Using a Foreign Key State Field and a Lookup Table

Pro's

This is a clean solution for maintaining state and requires only one Sql join to get a description for the current state.

Con's

We still cannot maintain a history of state changes with this solution.

Database Design Three: A State Table and a Lookup Table

Pro's

An effective database design that allows us to easily determine the current state and maintain complete state-change history, which is good for auditing and undoing mistakes.

Con's

The SQL required to insert and select is a little more complex than the other two solutions.