Assume we are getting some statistics from a customer which we analyse and we send our comments to the customer. Now, the customer tells us that the statistic they computed between January and March are based on a wrong methodology and sends us corrected series. We want perform analysis with the wrong and with the correct set of data, which are huge and only differ from January to March, so that we want to avoid data duplication.

Therefore, we need something like synthetic database records implementing the following logic:

synthetic[1] = wrong_data
synthetic[2] = correct_data between Januar and March, wrong_data otherwise

With this, we can easily perform our analyses on synthetic records.

Should such synthetic records be implemented in the application logic or on the side of the database? What are common pitfalls of such an implementation?


Since synthetic records are actually data, I would ... um, put them in the database.

The logic for retrieving them is relatively straightforward. You do an outer join on the synthetic table, and substitute the synthetic value if the outer join returns something other than a null. The simplest way is just to replace the entire record.

If you want to "version" the data, you can provide a sequence number field, at the cost of a little more complexity in the SQL to retrieve the most current version of the synthetic data. Since you probably only want the most recent revision in most cases, the most sensible approach is to put the current record data in a "history" table, and modify the original record in place.

Further Reading
How to version control a record in a database
SQL Strategies for Versioned Data

| improve this answer | |
  • I do not understand your answer! On the database, I have wrong_data and correct_data and I want to read it as synthetic[1] and synthetic[2] above. What do you mean by “putting synthetic records in the database”? Data sets are huge and there is lot of combinations so data duplication is no option. For the second paragraph, I do not have a synthetic table (this is precisely what I want to implement in some way), so I do not understand either what you mean! – Michael Le Barbier Grünewald Jun 3 '14 at 8:27
  • Then maybe you need to be a little clearer about what you want. If you don't need a history of previous revisions, then you don't need any of this; just edit the record in place. If you do need an edit history, then there's no way around this. – Robert Harvey Jun 3 '14 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.