Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Database with history

    Hello everyone

    I'm new user and i need some help about the best way to build a database that saves all data...

    Let me explain, my database will not have any Update, every time the user "updates" some data, a new field will be added to the table as a copy of the previous, with update values in the changed ones.
    I was building this, in a master\slave kind of way, the master has the ID and the Name that identifies the row, and then the salve row have all the values of that row (with a PK/auto-increment), every time the user changes values, i create a new row in the slave table and make the necessary changes. Everything worked fine with this approach until now...

    Now i need to associate other table to this one where the user can make changes in the same way (history), and that it's letting me stuck.

    The database data it's for a building company (constructions), that design components, send them to approval, after approval send them to production, after production send them to expedition to the construction site (table with all the info, and for each of this steps i need to register at least the when and who, every one of this steps are made inside of the company). Each component it's associated with a construction (table with the number of the construction and some relevant data), and some components need some material (this is the new table), in this material orders it saves the order num, and some other info like state of the order.

    1 Construction has X Components
    1 Component has X Material

    I hope i made myself clear...

    Thanks for looking to my problem.

  2. #2
    Join Date
    Jun 2003
    A better concept is to maintain only current data in your primary tables, but a complete history of the data in archive tables.
    This script will create the archive tables and triggers you need:
    sqlblindman private pastebin - collaborative debugging tool
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jul 2009
    Thanks for the reply, i'll give it a try...

    But at first glance, doesn't looks like that will do what i want\need.

Posting Permissions

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