Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: Keeping Version History of Rows

    Hello,
    I have a table with this structure:
    Code:
    CREATE TABLE `tp_orders` (
    		`orderId` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT
    	,	`oderNumber` VARCHAR(20) NOT NULL
    	,	`creationDate` DATE NOT NULL
    	,	`custId` TINYINT(3) UNSIGNED NOT NULL
    	,	`createdById` SMALLINT(5) UNSIGNED NOT NULL
    	,	`orderStatusId` TINYINT(3) UNSIGNED NOT NULL
    	,	`noBoxes` SMALLINT(5) UNSIGNED NOT NULL
    	,	`weightKg` SMALLINT(5) UNSIGNED NOT NULL
    	,	`volumeDm3` SMALLINT(5) UNSIGNED NOT NULL
    	,	`valueCent` MEDIUMINT(8) UNSIGNED NOT NULL
    	,	`payerId` TINYINT(3) UNSIGNED NOT NULL
    	,	`costCent` MEDIUMINT(8) UNSIGNED NOT NULL
    	,	`costCentInclVAT` MEDIUMINT(8) UNSIGNED NOT NULL
    	,	`estimatedWorkingMinutes` SMALLINT(5) UNSIGNED NOT NULL
    	,	`comments` VARCHAR(500) NOT NULL
    	,	`blockedForEditByUserId` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0'
    	,	PRIMARY KEY (`orderId`)
    	)
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;
    and I need to manage and keep all versions of the rows.
    Any ideas that do not involve copying the whole row for each version?
    The versions need to be searchable with good performance.

    Thank you,
    Udo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    Any ideas that do not involve copying the whole row for each version?
    i don't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    Thanks Rudy,
    long time . . . no see . . . :-)

    First I thought about storing each version in one column as gzcompressed csv.
    Without making the effort to test, I imagine the search performance to be unacceptable.
    Probably better to take the extra storage.

    Udo

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If storage is a problem you could always create a set of triggers:

    UPDATE triggers will identify only the old column values that have changed
    DELETE triggers will have to identify the entire entry that is being deleted.

    The savings though are minimal because you will still need to identify the primary key (orderid), the column name (or a numeric representation to reduce storage) and the old value. You will need to create fields for INT, DATE and VARCHAR field types for the values. Finally you will also need a transaction number for that record. The transaction number is used to determine similar operations, for example, lets imagine columns noBoxes and weightKg are updated at the same time then we need to create a transaction number to identify to link these two fields with the same update.

    If there are many deletes then every field is effectively being updated so you will end up with more information in your audit table than in the original.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    Thanks for your reply Ronan.

    I presented a simplified version as example here.
    The biggest concern is performance while trying to keep storage minimized.

    A row once create on tp_orders and other tables will never be deleted, only flagged as canceled in another table.

    A history table including a foreign key restraint to orderId and one column for each datatype in the main table [TINYINT(1), TINYINT(3), SMALLINT(5), MEDIUMINT(8), VARCHAR, DATE, DATETIME] might be a good performance / storage compromise.
    The enumeration of the main table's column names will make changes to the table-structure during development more difficult.

    Creating and inserting optimized history records should probably be handled by php and not triggers because the logic needed might be a little too much for SQL.

    Also performance wise I will have to compare "JOIN" syntax to separate searches on every table via php.

    Udo

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Actually performing the work on the database server is more efficient if the work is simple. Handling this in PHP will require the PHP engine to start up, requests will need to be sent using the client, these will need to be parsed and finally executed on the server. Using triggers performs all the work in one location without starting any new processes.

    As for the queries, if you create VIEWS once you know the SELECT syntax that you would need then this would further simplify the development by calling the VIEW to get the historical data.

    Just my 2 cents worth.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    Your 2 cents are very welcome, thanks.

    This will be a dedicated web application running on HTML5/CSS3, Javascript / jQuery, php and MySQL optimized for Google Chrome only, since it is not developed for the general public.

    So the majority of DB interactions go through AJAX / php activating the php anyway during the queries.
    I will have to make test once the final data-structure is locked down.

    VIEWS will definitively come in handy.

    thanks again,
    Udo

  8. #8
    Join Date
    Jul 2009
    Posts
    56
    My main concern regarding the use of VIEWs as opposed to combining separate queries in php is: limited RAM on the server.

    I do not know how MySQL behaves when rendering a VIEW of e. g. 50 resulting rows out of 4 tables holding far beyond one million rows each.
    If the VIEW combines the 4 tables (in RAM) and then extracts the resulting 50 rows, I might hit a RAM bottleneck.

    Anyone knows for sure how MySQL behaves while rendering small VIEWs from huge data-sets?


    Thanks,
    Udo

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •