DB design: Historical Data

[dazzerd]

While I'm on the roll and after posting my first thread just a while ago, I'd thought I'd post another about table structure and holding historical data.

As a rough example of one of the projects I am working on.

The main table will consist of a log of all raised system issues. Say:

issueID (PK)
originallyRaisedBy
dateRaised
timeRaised

Due to the nature of the database users will be able to virtually change everything about the issueID. For instance every issue will have an owner but this owner will change over time, but I need to know what the history of changes will be. Hence I will have another table like this:

dbID (PK)
issueID (FK linked to main Table)
ownerChangeDate
ownerChangeTime
issueOwner
isCurrent (bool)

The history of the issue will probably held in a listbox of sorts, however the main form of the database only needs to know the current issue owner. My thoughts are that when this changes I simply change the isCurrent flag to false and when inserting the new owner make isCurrent true.

I have reservations about this though as setting a flag such a way does not really preserve referential integrity, although it is quite clean. I could of course not use a flag at all and just get the latest issue owner for each issueID from either the data or in fact the dbID, as the highest one for each issueID will be definition be the latest. This should perform better too as I won't need to update any prior records to false and can just do one insert for each change.

I'm open to other ideas though so am keen to hear how other people have dealt with these issues.

 

[Thales750]

In the past I have put an owner field in the main table which would just be a number referring to an employee in the Employee Table. The user would select the owner from a drop down list.

Then as users would select another owner, it would create a new record on the User History Table. You can use the "After Update" event to trigger the Owner History Append Query, to add the new record to the History Table.

So the last Owner selected would be the one in the main table.

 

[dazzerd]

Ok thanks for that, so you are almost saying use the main table as the store for all current information about the issue and then move historical information to the history table.

Certainly makes sense but it again still requires two calls to the backend, one to update the current issue and one to insert a new row into the history table.

For reference I rarely use bound objects so I would need to handle the update and insert in code, not a problem I know, but still more work.

 

[CJ_London]

If you are only ever looking at the current status, having a separate history table is the way to go, the overhead of two inserts is small.

If you want a single table then typically you need three fields

EffectiveFromDate(Time) - so you know which its the latest record - you can't rely on the autonumberID as showing the latest record. Compacting for example will reset them all so the count will start from new position.

EffectiveToDate(Time) - this is left blank until you want the record to cease being available from a particular date - note this usually only applies to the last record

TimeStamp - only required to a) differentiate between two records with the same effectivefromdate(time) - the later timestamp is the latest record for that effectivefromdate(time) and b) to maintain a record of the order of events.

 

[dazzerd]

EffectiveFromDate(Time) - so you know which its the latest record - you can't rely on the autonumberID as showing the latest record. Compacting for example will reset them all so the count will start from new position.

Thanks that is a different way to do it for sure. Though I am a bit confused by the above statement. I didn't know that a compact would reset the autonumber? It seems crazy that that would happen.

Edit: In fact this seems to only happen on older versions of access:

There is a page on TechNet which answers the specific question, but I cannot link to it.

For reference I am using a SQL server backend, and simply setting the isidentifier flag to true and the increment to 1. Do you know if this happens on SQL server too?

 

[jdraw]

Normally when you are dealing with Projects and Issues, you include things like IssueResolution, Impact, AssignedTo... But readers don't know your real requirement.
Often Issues can be related to other Issues.

Just some thoughts for consideration.
Good luck with your project.

 

[CJ_London]

I didn't know that a compact would reset the autonumber?

I'm not saying it will reset to zero, just to the lowest number it can find. If you had a table with 10 records and delete the contents for a table, and then when you insert a new record, it will be 11. However compact it and it will be 1

If you read the documentation on autonumbers you will find it does not guarantee the order of numbers, just that they will be unique.

Re sql server, I would imagine so, but I only use autonumbers to identify a specific record, and don't use them in any way as a control on the system. Don't forget that auto number can also be set to random and replicationID which is a different sort of number altogether.

 

[jdraw]

Further to CJ's post
Autonumbers

 

[dazzerd]

Normally when you are dealing with Projects and Issues, you include things like IssueResolution, Impact, AssignedTo... But readers don't know your real requirement.
Often Issues can be related to other Issues.

Just some thoughts for consideration.
Good luck with your project.

Thanks for this, yes issues will contain lots of different properties including some of the ones you mention above. My plan was to have basically just the issueID number in the main table, and some static data like who actually raised it in the first place and original target date for resolution. Everything else would be stored in distinct history tables.

 

[dazzerd]

Further to CJ's post
Autonumbers

I'm getting confused now. Foot note 4 of the linked article seems to suggest it will never change unless under special certain circumstances.

I cannot link the TechNet post due to not having enough posts here and everything I try to put just the link in text fails but if you search for "access autonumber compress" its one of the first results.

 

[CJ_London]

try this

http://www.utteraccess.com/wiki/index.php/Autonumbers

 

[dazzerd]

My confusing grows...

So the consensus is that an autonumber is just a great way for Access to organise itself internally and should therefore not be used as a unique identifier for an actual record, rather an autonumber simply uniquely identifies the arbitrary position of any row in table?

So for instance for stuff which I use to populate a combobox drop down list, the source is usually a table which holds:

ID(Autonumber)
Name(String of whatever it needs to show)
Live (Boolean whether its still in use or not in the live front end)

This populates a 2 column combobox, column 1 being ID, column 2 and the only one being visible being name.

When a user chooses a selection from the combobox what is saved back to the backend database is the ID.

What I have read apart from the TechNet article is that this is actually a bad way to go as the relationship between the table holding the ID for the combobox and that where the actual record can never be guaranteed?

i.e.

I have a row in my combobox table :

ID Name Live
5 SomeStuff True

For years everytime somebody wanted to save "SomeStuff" to the DB what was recorded was 5. However something has happened which has meant the autonumbers have been generated, so "SomeStuff" now has an ID of 6 and 5 now has a name of "OtherStuff". Hence when we look at the history in the table we get a result of "OtherStuff" rather than "SomeStuff"?

 

[Minty]

If you are using SQL backend I think your fears are unfounded.
I believe almost everyone uses the approach you have described above and I personally haven't experienced a issue with it in many years of use.

 

[CJ_London]

an autonumber is just a great way for Access

Not just Access, any db

should therefore not be used as a unique identifier for an actual record

should therefore only as a unique identifier for an actual record

 

[Thales750]

There is one more little use for an autonumber. When you're first putting in test data and debugging; it's helpful to quickly find the latest records.

 

[Thales750]

I'm not saying it will reset to zero, just to the lowest number it can find. If you had a table with 10 records and delete the contents for a table, and then when you insert a new record, it will be 11. However compact it and it will be 1

If you read the documentation on autonumbers you will find it does not guarantee the order of numbers, just that they will be unique.

Re sql server, I would imagine so, but I only use autonumbers to identify a specific record, and don't use them in any way as a control on the system. Don't forget that auto number can also be set to random and replicationID which is a different sort of number altogether.

If there's already a 2 what will the next one be. I've actually had it lock before when I forced it to create a zero as a autonumber primary key, when there was already records.

In case you're wondering why on Earth I would ever force it to 0, the answer is to have an "All" selection for combo boxes.

After update: If me.cboCurentDropdownBox = 0 then me.txtQueryFiler = "*"


And oh yeah, if you want the "All" selection to sort with "All" at the top than just name it something like <All>

 

[CJ_London]

In case you're wondering why on Earth I would ever force it to 0, the answer is to have an "All" selection for combo boxes.

I just use a union query, then I can have Add, All, Create New or whatever

SELECT -2 as ID, "All" As Description FROM myTable
UNION SELECT -1 as ID, "Create New" As Description FROM myTable
UNION SELECT ID, Description FROM myTable

And by using a really large number for ID, I can put these options at the bottom if required

 

[dazzerd]

To be fair I have never considered autonumber to be anything more than an autogenerated unique identifier and I will continue to treat it as such.

To be fair I don't think I have read anything that shows that using an autonumber to uniquely identify the most recent record from a subset in a history table will not work.

Compressing a table will not effect the autonumber the way that has been described here, certainly not in Access 2007 and certainly not in a SQL backend.

In Access 2007 + this only happens if you first delete all the content of the table and then compress. If that is the case then it's not really an issue as if I am deleting the records then I clearly have no regard for a consistent autonumber.

Though maybe I'll just use date the record was logged instead

 

[Thales750]

Compressing a table will not effect the autonumber the way that has been described here, certainly not in Access 2007 and certainly not in a SQL backend.

In Access 2007 + this only happens if you first delete all the content of the table and then compress. If that is the case then it's not really an issue as if I am deleting the records then I clearly have no regard for a consistent autonumber.

Though maybe I'll just use date the record was logged instead

That's been my experience as well. The only time I have ever seen Access change the sequence is when you force it to have a specific number by using an append query with a pre-assigned number in the Autonumber field.

And if it does go to a lower number, the table will freeze up when the next new autonumber equals an existing one.

I always date and time stamp all records except look up records.

 

[gemma-the-husky]

My confusing grows...

So the consensus is that an autonumber is just a great way for Access to organise itself internally and should therefore not be used as a unique identifier for an actual record, rather an autonumber simply uniquely identifies the arbitrary position of any row in table?

no - an autonumber is fine for all purposes EXCEPT where you are trying to maintain an intact sequence.

so if you are trying to log problems/issues then you could do any of these

a) use an autonumber PK and accept that it will most likely leave gaps as it is used.
b) use a manually determined (long number) PK that you manually ensure will maintain a sequence (not particularly hard)
c) use the autonumber BUT also retain some sort of sequential reference index.