Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis.

The idea behind all that is:

  • all the rows must have a history
  • all links must stay coherent
  • it should be easy to make requests to get "current" columns
  • clients who have bought obsolete things should still see what they've bought even though this product is not part of the catalogue anymore

and so on.

Here's what I want to do, and I'll explain the problems I'm facing.

All my tables will have those columns:

  • id
  • id_origin
  • date of creation
  • start date of validity
  • start end of validity

And here are the ideas for CRUD operations:

  • create = insert new row with id_origin = id, date of creation=now, start date of validity=now, end date of validity=null (= means it's the current active record)
  • update =
    • read = read all the records with end date of validity==null
    • update the "current" record end date of validity=null with end date of validity=now
    • create a new one with the new values, and end date of validity=null (= means it's the current active record)
  • delete = update the "current" record end date of validity=null with end date of validity=now

So here's my problem: with many-to-many associations. Let's take an example with values:

  • Table A (id = 1, id_origin = 1, start=now, end=null)
  • Table A_B (start=now, end=null, id_A = 1, id_B = 48)
  • Table B (id = 48, id_origin = 48, start=now, end=null)

Now I want to update table A, record id=1

  • I mark record id=1 with end=now
  • I insert a new value into table A and... damn I've lost my relation A_B unless I duplicate the relation, too... this would end to a table:

  • Table A (id = 1, id_origin = 1, start=now, end=now+8mn)

  • Table A (id = 2, id_origin = 1, start=now+8mn, end=null)
  • Table A_B (start=now, end=null, id_A = 1, id_B = 48)
  • Table A_B (start=now, end=null, id_A = 2, id_B = 48)
  • Table B (id = 48, id_origin = 48, start=now, end=null)

And... well I have another problem: the relation A_B: shall I mark (id_A = 1, id_B = 48) as obsolete or not (A - id=1 is obsolete, but not B - 48)?

How to deal with this?

I have to design this on a big scale: products, partners, and so on.

What is your experience on this? How would you do (how have you done)?

-- Edit

I've found this very interesting article, but it doesn't deal properly with "cascasding obsolescence" (= what I'm asking actually)

share|improve this question
    
How about copying the data of the updating record before it's updated to a new one with a new id keeping the linked list of the history with id_hist_prev field. So the id of current record never gets changed –  Maksee Apr 30 '12 at 19:36
    
Rather that re-inventing the wheel, have you considered using, for example, Flashback Data Archive on Oracle? –  Jack Douglas May 2 '12 at 21:41
add comment

migrated from programmers.stackexchange.com May 1 '12 at 16:37

This question came from our site for professional programmers interested in conceptual questions about software development.

4 Answers

This has some overlap with functional programming; specifically the concept of immutability.

You have one table called PRODUCT and another called PRODUCTVERSION or similar. When you change a product you don't do an update, you just insert a new PRODUCTVERSION row. To get the latest, you can index the table by version number (desc), timestamp (desc), or you can have a flag (LatestVersion).

Now if you have something that references a product, you can decide which table it points to. Does it point to the PRODUCT entity (always refers to this product) or to the PRODUCTVERSION entity (only refers to this version of the product)?

It gets complicated. What if you have pictures of the product? They have to point to the version table, because they could be changed, but in many cases, they won't and you don't want to duplicate data unnecessarily. That means you need a PICTURE table and a PRODUCTVERSIONPICTURE many-to-many relationship.

share|improve this answer
add comment

It's not clear to me if these requirements are for auditing purposes or just simple historical reference such as with CRM and shopping carts.

Either way, consider have an main and main_archive table for each major area where this is required. "Main" will only have current / active entries whereas "main_archive" will have a copy of everything that ever goes into main. Insert / update into main_archive can be a trigger from insert / update into main. Deletes against main_archive can then run across a longer period of time, if ever.

For the referential issues such as Cust X bought Product Y, the easiest way to solve your referential concern of cust_archive -> product_archive is to never delete entries from product_archive. Generally, churn should be much lower in that table so size shouldn't be too bad of a concern.

HTH.

share|improve this answer
2  
Great answer, but I would like to add that another benefit of having an archive table is that they tend to be denormalized, making reporting on such data much more efficient. Consider the reporting needs of your application with this approach as well. –  maple_shaft Apr 30 '12 at 15:22
1  
In most of the databases I design all the 'main' tables have a prefix of the product name like LP_, and every single important table has an equivalent LH_, with triggers inserting historical rows on insert, update, delete. It doesn't work for all cases but it has been a solid model for the things I do. –  Paolo Bergantino Apr 30 '12 at 17:18
    
I agree - if the majority of queries are for the "current" rows, you probably will get a perf advantage by partitioning current from history in two tables. A view could union them back together, as a convenience. This way the data pages with current rows are all together and probably stay in cache better, and you don't have to constantly qualify queries for current data with date logic. –  onupdatecascade May 6 '12 at 0:06
1  
@onupdatecascade: Note that (at least in some RDBMSs) you can put indices on that UNION view, which lets you do cool things like enforce a unique constraint across both current and historical records. –  Jon of All Trades Jun 22 '12 at 15:02
add comment

How about this? It seems simple and pretty effective for what I have done in the past. In your "history" table, use a different PK. So, your "CustomerID" field is the PK in your Customer table, but in the "history" table, your PK is "NewCustomerID". "CustomerID" becomes just another read-only field. That leaves "CustomerID" unchanged in History and all your relationships stay intact.

share|improve this answer
    
Very nice idea. What I've done is very similar: I duplicate the record, and mark the new one as "obsolete" so that the current record is still the same. Note I wanted to create a trigger on each table but mysql forbids modifications of a table when you're into a trigger of this table. PostGRESQL do this. SQL server do this. Oracle do this. Well in short MySQL still has a very long way to go, and next time I'll think twice when choosing my database server. –  Olivier Pons May 3 '12 at 7:17
add comment
up vote 0 down vote accepted

I've implemented all the stuff from here with 4 fields that are on all my tables:

  • id
  • date_creation
  • date_validity_start
  • date_validity_end

Each time a record has to be modified, I duplicate it, mark the duplicated record as "old" = date_validity_end=NOW() and the current one as the good one date_validity_start=NOW() and date_validity_end=NULL.

The trick is about the many to many and one to many relations: it works without touching them! It's all about the queries which are more complex: to query a record in a precise date (= not now), I have for each join, and for the main table, to add those constraints:

WHERE (
  (date_validity_start<=:dateparam AND date_validity_end IS NULL)
  OR
  (date_validity_start<=:dateparam AND date_validity_start>=:dateparam)
)

So with products and attributes (many to many relation):

SELECT p.*,a.*

FROM products p

JOIN products_attributes pa
ON pa.id_product = p.id
AND (
  (pa.date_validity_start<=:dateparam AND pa.date_validity_end IS NULL)
  OR
  (pa.date_validity_start<=:dateparam AND pa.date_validity_start>=:dateparam)
)

JOIN attributes a
ON a.id = pa.id_attribute
AND (
  (a.date_validity_start<=:dateparam AND a.date_validity_end IS NULL)
  OR
  (a.date_validity_start<=:dateparam AND a.date_validity_start>=:dateparam)
)

WHERE (
  (p.date_validity_start<=:dateparam AND p.date_validity_end IS NULL)
  OR
  (p.date_validity_start<=:dateparam AND p.date_validity_start>=:dateparam)
)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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