Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    DB design dilemma

    Hi all!

    Currently I am designing a new application that will use a relational database.
    One of these tables, table X, has 2 FK's to PK's of 2 different tables (TABLE_A and TABLE_B), and a field VALUE.

    Now the user needs to be able to the value of a variable that will be stored in table X.
    Besides, he should be able to see the previous 500 changes in a pop-up window.

    There are now 2 options to realize this:

    1.Add a separate table 'Changes', with fields TIMESTAMP, OLD_VALUE, NEW_VALUE, VALUE_TABLE_A (or FK_1), VALUE_TABLE_B (or FK_2)In case of an update of field VALUE in table X, the field VALUE will be overwritten, and a new record with the old and new values will be added to table 'Changes'.

    2.Add field DATE_BEGIN, DATE_END, AND PREVIOUS_ID to table X.
    In case of an update of field VALUE in table X, the field 'DATE_END' will get the current date (i.e. record is set inactive), and a new record will be added to the table with the two same FK's as the changed record and DATE_BEGIN set to the current date.
    When retrieving the current values, which will be done much more often than viewing the changes, one has to retrieve the records in which DATE_END has a null value. When viewing the changes, one has to get all active records (will be about 8, since the two refered tables include 4 and 2 records respectively). Next for these about 8 records, a backtracking SQL expression has to be written to query recursively on PREVOUS_ID. Then the 500 most recent changes are determined.

    The advantage of the second method is that there is no unrelated table in the database, so the design is cleaner.
    Moreover it should be possible to retrieve the state at some specific date in the past.

    However, in this DB it is highly unprobable that it would ever be demanded to automatically retrieve a past state (instead of watching the pop-up window 'Changes'). Besides, searching for the 500 most recent changes seems to be complex.
    Finally, it is very hard to implement the same functionality for new records in TABLE_A and TABLE_B, since possibly all references to these tables may have to be updated in case of small changes in TABLE_A and TABLE_B. So this method is inconsistent in dealing with changes in tables that do have fields that are refered to by other tables (TABLE_A, TABLE_B), and tables that don't (table X). If you update the records in TABLE_A or TABLE_B, the changes pop-up window is not able to show the correct data at the time of a specific change.

    Which option would you prefer?
    I would be very grateful if you could provide me with advise, since I am fairly unexperienced in DB design.

  2. #2
    Join Date
    Oct 2008
    Posts
    8
    I'm still a bit confused on the set-up. Can you give us a couple of sample rows from Tables A,B, and X, as well as a couple sample rows from your two options?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2008
    Posts
    2
    Sorry for the late response, but thanks for the help.
    We have already decided to do it the easy way, i.e. writing to a separate table the changes in the database.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by DutchProgrammer
    We have already decided to do it the easy way
    Where the hell is the job security in that?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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