Half a croissant, on a plate, with a sign in front of it saying '50c'
h a l f b a k e r y
You think: Aha! We go: ha, ha.

idea: add, search, annotate, link, view, overview, recent, by name, random

meta: news, help, about, links, report a problem

account: browse anonymously, or get an account and write.




Date Trailed Database Records
(+1, -1)
  [vote for,

For some categories of information held within a relational database, it's very useful to be able to know not just what the current information is, but a history of what the information used to be.

We can accomplish this by holding multiple copies of the data with date markers and simply checking which record to retrieve at any given time, but as this information becomes more widely required the design quickly becomes unwieldy as extra objects grow all over the database and the public interface changes significantly. In essence, the developer ends up building a separate database on top of the existing database platform, with its own commands and API. This is clearly inefficient.

Instead, the database platform should support this as an option on all tables at creation. When this option is selected, rather than a traditional file the database shall store a full change history with multiple versions of each record. Records won't be deleted but expired.

For reading, instead of running SELECT * FROM Users

The programmer could then run SELECT * FROM Users AT '2010-01-19 23:00' and receive a full copy of the requested table as it was at that point rather than at the present moment, or SELECT * FROM Users BETWEEN '2010-01-01 23:00' AND '2010-01-19 23:00' to see the changes within the time period.

eftpotrm, Jan 19 2010


       A really excellent idea. I'm not sure one could discover the prior art if he/she didn't happen to know the term "version control."
mouseposture, Jan 19 2010

       When I read the title I thought this was going to be a database of reviews on all the people you've dated.   

       What you are talking about is something like saving the transaction log from SQL. The downside is that this can get BIG quickly.
MisterQED, Jan 20 2010

       a) This _ISN'T_ database version control. That's about taking a copy of your database's structure and saving that into a version control system so the database structure can be tracked and compares with your VC tools. The purpose of this would be to allow you to build a table whose data could be read at different points in time through SQL syntax. Very useful for some applications but a major pain to implement by other means.   

       b) Yes, it could need a lot of storage. That can be mitigated by storing deltas rather than full records and providing a purge function, but the chief point is to make this an option. Most tables don't need this and those that do already have the storage issue, plus lots of extra code to write which this would make unnecessary.
eftpotrm, Jan 26 2010

       something keeping you from putting a date field in when you write the schema ?
FlyingToaster, Jan 26 2010

       [eftpotrm] Oops, sorry.   

       Perhaps more usefully: A lot of companies are now marketing database software designed to comply with "CFR 21 part 11" (rules from the USA's Federal Drug Administration regulating clinical trials databases). Compliant software must be capable of retrieving dated earlier versions of altered records. But probably not with the ease & convenience of this idea, since they have an audit in mind, not the sort of query you describe.
mouseposture, Jan 27 2010

       FlyingToaster - no, but that presumes you only want to know when an individual record was created / last altered / whatever. This would allow you to see what it used to look like and when, which for some applications is _very_ useful and can't just be done with a simple date field on an existing table.
eftpotrm, Mar 27 2010

       what? no.
(okay I read what I wrote and that's what it says, but it's not what I meant)

       Include "date" as part of the unique key, so with not too much flimflammery you'd normally just pick up the latest record unless you specifically asked for another date/range.... umm, requires a small amount of programming.   

       But incremental's better when you don't mind having to pick up field-change records from all over the file.
FlyingToaster, Mar 28 2010

       I've seen this where people save an id, a version number,a timestamp telling whenever something changes, plus a status field stating whether something is Active or Historic. If you want to see the live situation, and see it quickly, simply recall everything that's Active (bonus, you can make this an indexed field too if you want) If you want to see/rewind the state of play as at X, select everything with a date less than X, grouping on the id and showing only those records who's X is equal to a Max(x) within id numbers - but that is pretty expensive performance wise.
zen_tom, Mar 29 2010


back: main index

business  computer  culture  fashion  food  halfbakery  home  other  product  public  science  sport  vehicle