Database design and concept

[ddskorupski]

Hello again. This site has been so instrumental in my last project that I am been asked to do another. And once again, I need your help. Here's the gist:

I need to build a database that tracks changes to a table. Basically the steps would look something like this:

1. import spreadsheet
2. create a backup version of the existing table and record the version # and date
3. create a table that llists all of the version changes
4. inputs new data into current table

What we are trying to achieve is to build a database that tracks changes to milestone data over time. We want to track every change and assign it a version and version date.

Does that make sense? Can you help me with the design?

 

[The_Doc_Man]

OK, here are some randomized thoughts coming from a really randomized computer - my brain BC (before coffee)

When doing before/after comparisons, you need to think about lots of possible types of transactions in this DB. Like: Added a milestone. Dropped a milestone. Changed start date, responsible party, estimated duration, dependency, etc. etc.

When you can add/drop milestones, you run into a numbering problem that makes it difficult to keep the comparison at apples to apples because you lose track of the keys that make items A and B comparable. So to me, the very first part of this design is to decide a time-invariant method that allows you to recognize an event throughout the life of the project. Otherwise you are going to do nothing useful.

 

[ddskorupski]

Thanks docman. I agree about the changes which is why I want to create a version copy of the records before I upload or query the changes. Any idea how the table structures should look and how the relationships should be?

 

[The_Doc_Man]

I'd put one master table that holds everything you currently know and a set of transaction tables that give you something like, say, the field name and date of a change and the value that was in the table before the update.

Then I would import the spreadsheet to a temporary table and write some VBA code to compare fields between the matching records in time-sequence order. (Earliest first.) Now when you reach a comparison with your VBA, you compare field A in the master record to field A in the update record. If you have a change, you make an append of the current value in the master table plus field name, date, time, anything else you need to record - AND THEN you update the master table to its new value. Always leave the master table as current. Always transact to remember the previous value.

 

[ddskorupski]

I'd put one master table that holds everything you currently know and a set of transaction tables that give you something like, say, the field name and date of a change and the value that was in the table before the update.

Then I would import the spreadsheet to a temporary table and write some VBA code to compare fields between the matching records in time-sequence order. (Earliest first.) Now when you reach a comparison with your VBA, you compare field A in the master record to field A in the update record. If you have a change, you make an append of the current value in the master table plus field name, date, time, anything else you need to record - AND THEN you update the master table to its new value. Always leave the master table as current. Always transact to remember the previous value.

Oh, is that all? you make it sound so easy. I actually came across a history db I found on this site under database samples. I am trying to use it as a basis. I'm not having much luck though.

 

[The_Doc_Man]

Here is how you approach this more or less reasonably.

BEFORE YOU START:

Look up a few things in the Access Help files and do some object browsing among the DAO and ADO libraries to look at data types. There is a set of constants you can use that describe every access data type.

Look up "Collections" as a thing to be "enumerated" or "iterated." There are some examples in the help files.

Look up VBA SELECT/CASE syntax.

Look up and review recordset operations including opening, closing, editing, and updating. Also navigation including ways to find a specific record in a recordset based on a search or maybe a key.

DESIGN SOMETHING LIKE THIS (in a VBA module that you can trigger some way):

You've got three recordsets. One is the change log. One is the master file. One is the incremental file. Get these set up before the loop I describe below.

It is imperative that either by a unique index or compound key, you have a way that you can match up the incremental record to one and only one master record. It is less important for the incremental records to be unique.

Write a loop that enumerates the records in a recordset. For the purposes of your comparison, drive the process from the INCREMENTAL update file. (It is smaller than the master file, probably, so takes less time to iterate.)

For each incremental record, search the second recordset to find the matching record in your master table. (If there is no match, you have just found a new record to be added. How you want to log that is your call.)

OK, now iterate over the collection of fields in the recordsets. You don't need a special trap for the fields driving the matchup because in order to match the records, those fields had to be equal, so... no change will be detected.

For each field, you can just compare the value in the incremental table to the value in the master table, assuming you imported the incremental table to a structure that matches the master table. You just do something like

they_match = ( recsetA.fields = recsetB.fields )

If the fields match, that's nice. No work to be done.

For differences, you need to see the field.type code and do a select on that code to see how to log things. You already know the field name (as recsetA.Fields.name, perhaps), the old value (in the master table's field), and the new value (in the incremental table's field). So all you have to do is log the date/time, field name, and the old value in the master table. A wrinkle here is that you have to do a CStr([field]) in order to log all types, and might wish to include the field type code as part of the log so that someone reading it later can know that the string they see came from a short integer or a DOUBLE or whatever. Then just update the master record from the incremental record. They are, after all, the same data type if you did it right.

To make it easier, just remember that there was at least one change to the record and wait to update it until the field collection enumeration is complete for the current incremental record.

Outside of the field loop, you step through the incremental recordset until you reach .EOF condition and then stop that loop.

Don't forget the parity rule: Close what you open. Close the recordsets, close the database variables, close anything that you opened.