Database Design – Handling Draft Versions of Data

database-design

The database I'm currently building is pretty traditional relational database stuff, but there is one requirement that is not clear to me how model appropriately.

There are many business rules which are translated into database consistency checks (i.e. date A must be between date B and C or this field must be not null when another field is a specific value). My problem arises because a user must be able to work on a "draft" version on the data, being allowed to save their changes even if the consistency checks fail.

Eventually, a user can commit their draft, but only once all the consistency checks pass.

The main reason the client wants this functionality is so that they can query the database directly for reports, and be sure that they are using the most recent version of the data that is "consistent", but not restrict the user from entering partial data.

I've played around with storing the changeset to the data in JSON in a document store, and then applying the changeset in code when dealing with the user is dealing with draft data, but this makes it hard to manage things like relationships and the creation/deletion of records.

Is there a common way to accomplish this goal? Or does anyone have any suggestions about how I may approach this problem differently?

Best Answer

This is going to sound crazy but I'd create a separate service that stored the data in another set of tables, but this other set of tables had no integrity checking. The user would do all the partial work there, and when they were ready you could move their data into the checked schema in a single transaction.

Related Solutions

Database Design – Should Date and Time Fields Be Merged

One minor point: WHEN you merge the two columns, you might want to do the merge into a new "MONKEY_DATE_2" column instead of overwriting the existing one. That leaves your current columns unchanged, and you can find all the code that hasn't been updated to work with the new structure with grep.

Database Design – Designing for Polymorphic Data

Follow composition over inheritance as composition lends itself well to relational databases.

Say you want to get all short messages:

SELECT * FROM Message INNER JOIN ShortMessage ON ShortMessage.message = Message.id

Say you want to get all short messages and emails:

SELECT * FROM Message 
   LEFT OUTER JOIN ShortMessage ON ShortMessage.message = Message.id 
   LEFT OUTER JOIN Email ON Email.message = Message.id

This will effectively create a result set very much like what your first option is, with a lot of null fields.

So the basic idea here is that a message potentially has a email. Depending on whether or not you define Email.message to be UNIQUE, you can make sure there's at most one email corresponding to a message. This setup (like all alternatives you proposed) allows a single message to have multiple different transports, which is actually conceivable in the real world.

Advantages of this approach:

(over 1 & 2) Your database is normalized, which usually gives you stuff for free
- if you want to add new transport types without altering any existing tables (not something you want to do in a huge database)
- You don't have to store loads of nulls

(over 3) You can query all messages in one query, so you can get all messages to a specific recipient like so:

SELECT IFNULL(Email.body, ShortMessage.body) as text FROM Message 
  LEFT OUTER JOIN ShortMessage ON ShortMessage.message = Message.id 
  LEFT OUTER JOIN Email ON Email.message = Message.id
  WHERE 
    Email.recepient = "john.doe@example.com" 
    OR ShortMessage.recipient = "01189998819991197253"

And voilà, you've got yourself a list of all text ever sent to john doe.

However, if you're using an ORM, you might actually use the power to perform queries such as that. I am have no practical experience with C#, but from my understanding as an outsider, Linq lends itself well to accessing relational databases for what they are, instead of trying to shoehorn objects semantics onto records (which always makes you hit a very thick wall at some point down the road).

Related Topic