What to name column in database table that holds versioning number

Question

I'm trying to figure out what to call the column in my database table that holds an INT to specific "record version". I'm currently using "RecordOrder", but I don't like that, because people think higher=newer, but the way I'm using it, lower=newer (with "1" being the current record, "2" being the second most current, "3" older still, and so on). I've considered "RecordVersion", but I'm afraid that would have the same problem. Any other suggestions? "RecordAge"?

I'm doing this because when I insert into the table, instead of having to find out what version is next, then run the risk of having that number stolen from me before I write, I just insert insert with a "RecordOrder" of 0. There's a trigger on the table AFTER INSERT that increments all the "RecordOrder" numbers for that key by 1, so the record I just inserted becomes "1", and all others are increased by 1. That way, you can get a person's current record by selection RecordOrder=1, instead of getting the MAX(RecordOrder) and then selecting that.

PS - I'm also open to criticism about why this is a terrible idea and I should be incrementing this index instead. This just seemed to make lookups much easier, but if it's a bad idea, please enlighten me!

Some details about the data, as an example:

I have the following database table:

CREATE TABLE AmountDue (
    CustomerNumber INT,
    AmountDue      DECIMAL(14,2),
    RecordOrder    SMALLINT,
    RecordCreated  DATETIME
)

A subset of my data looks like this:

CustomerNumber    Amountdue      RecordOrder                 RecordCreated
           100            0                1       2009-12-19 05:10:10.123
           100        10.05                2       2009-12-15 06:12:10.123
           100       100.00                3       2009-12-14 14:19:10.123
           101         5.00                1       2009-11-14 05:16:10.123

In this example, there are three rows for customer 100 - they owed $100, then $10.05, and now they owe nothing. Let me know if I need to clarify it some more.

UPDATE:

The "RecordOrder" and "RecordCreated" columns are not available to the user - they're only there for internal use, and to help figure out which is the current customer record. Also, I could use it to return an appropriately-ordered customer history, though I could just as easily do that with the date. I can accomplish the same thing as an incrementing "Record Version" with just the RecordCreated date, I suppose, but that removes the convenience of knowing that RecordOrder=1 is the current record, and I'm back to doing a sub-query with MAX or MIN on the DateTime to determine the most recent record.

Answer 1

I think that "current version = 1" is a bad idea because when you add a new current record, you'll have to update ALL the previous versions. And any other tables or applications that refer to the old version numbers will now be wrong. I got to write a service that interfaces with a mainframe program that works like that, and it was a huge headache that wasted dozens of developer hours.

I usually do versioning with a version_id field, that just gets incremented every time. Then when I want to find the newest record, I order by version_id desc in the query and select the first row only.

Edit: I didn't see the datawarehousing tag that iandisme just pointed out. If selecting all the versions wouldn't work, I've seen some systems that keep a separate table that just stores the most up-to-date version for each record in another table. So when a new version is added to the Record table, the appropriate RecordVersion record gets updated to store that new version. This has always been overkill for the stuff I work on, but I don't work on whole warehouses of data, so I don't know if that would be any better or worse.

Answer 2

Instead of using Integers to state which version of a row is the latest, I think you should use a TimeStamp field with a default value of the current time.

This way, no matter who adds a row at what time, there will be no ambiguity as to the latest version of the row.

I think renumbering all associated rows to id+1 is not a good idea for multiple reasons:

1. You are changing something that there is no need to change
   • Locking and Blocking will increase
   • Using more processing power and memory than required