Database design - looking for your opinion 3

[virtualjm]

Hello. I am creating a little application with Access, the task is basically one to fill out forms. Information like customer details, for example, will occur on more than one form so they will be stored in separate tables, in typical relational structure. But one caveat is that when any form is printed, even two years from now, it must print EXACTLY as it was originally created. That means if the customer changes their telephone number, the form from two years ago must reflect the telephone number that was current at that time. As I see it I have two options, and I am just looking for some experienced opinions.

Firstly, I could use the "customer table" to simply fill in information to a flatfile, thus retaining that original customer information. But here I am talking about a flatfile, which seems cheesy. Secondly, I could "version" the client files - so that if customer information changes the program would force the creation of a new customer record rather than editing of the original customer. So now there would be two customer records for that customer, each with a unique primary key, but differing in at least one specific detail (such as telphone number). Now the old form can link to the "old customer", while a new form can link to the "new customer".

Approach number 2 is a more relational approach, but is also more difficult to implement. This is complicated more since there are actually about 20 "supporting files" like my customer example that would need to be "versioned". On the other hand, using approach 1 would result in a pretty massive flatfile since I would be filling out lots of forms. Am I just being stupid for considering approach 2, when the first is so easy (ie not challenging)? Looking for your opinions on my mental health. Thanks.

 

[sudhirkr]

Hi virtualjm,

I can give you two of the possible options which quite resembles your approach 2.

Option 1:
--------
Have a running Serial Number(AutoNumber datatype) as the primary key for your "customer table

Let your CustomerID field be different from this running serial number Primary key

have a "status" field which will indicate whether the record is active or not.

Option 2:
--------
if you have already identified the composite key combination, retain your Primary Key combination

your "CustomerID" field can be part of the primary key combination which you have retained

As in option 2, have a "status" field which will indicate whether the record is active or not.

This way, you can start building your system for archiving your data.
Hope with this info, you can come out of the dilemma you are facing.

Sudhir.

 

[virtualjm]

Thanks for taking the time to read this long post and provide me with some good ideas. Since I am just in the planning stage I can use your first suggestion, which helps tremendously. Thanks very much.

 

[demoman]

Howdy!

Thanks, I do not get to see many new, and unique, database design puzzlers anymore. I cannot remember having worked something exactly like this before.

A question that comes to mind is, what are your plans for historical reporting?

I have been in situations when we were planning how we would deal with customer/supplier data changes. You could hear the ringing of metal, as swords were drawn. Some wanted new customer/supplier records(IDs) and others insisted on just modifying the current ones. Historical reporting was a primary issue.

Anyway, I have a couple ideas for you based on how you answer.

 

[virtualjm]

Yes, that is the key - historical reporting. Because these are public records, it is absolutely essential that when a form is printed, even six years down the road, that it be an exact replica of the original. This won't be a common occurence, but I suppose that is really irrelevent. The forms are for a building permit, so once a permit is issued and paid for (for which I will just use a STATUS field), there should be no way of making a change (other than a correction, in which case I will carry an audit trail). It is a bit of a challenge but, like you, I find that the interesting part. Thanks for taking some time on this with me - it is nice, and helpful, to have another opinion. Regards.

 

[demoman]

I worked for 15 years in aerospace manufacturing. The parts we made had to have certifiable tracking to the point where the raw material came out of the ground. We had suppliers, and/or processors, who often changed name, address, etc. In our MRP database, QC wanted to be able to re-create a certification, exactly the way it was at the time of issue. Purchasing wanted to be able to make sure they could always have long-term purchasing history for every vendor. So, I had to maintain the same vendorID (supplier, processor, subcontractor), but still be able to change their name, address, etc.

What I did was to add 2 columns to the VendorMasterFile, EffectiveStartDate and EffectiveEndDate. Then I made an exact copy of the VendorMasterFile, and called it VendorMasterFileHistory. Anytime the Vendor MasterFile needed to be updated, the current record was date stamped and inserted into the History table. The current record was then updated with the new EffectiveStartDate. When historical reports were ran, the report logic looked first at the History file to see if there was an historical row for the vendor in the reporting time-frame.

Hopefully, this makes sense?

 

[BBaty]

Vote for approach #1. Sometimes when trying for nice "normalized" files one can get in too deep and forget the real world. I work in government were "reproducing" documents is part of the norm (for legal reasons) but this answer can also apply to the private sector.

I use the following order entry example: For simplicity consider a Customer Master File, Order Header File (date, billing & ship to data) and order detail file (one record per line item). The "bill to & ship to" information, in the Order Header File, defaults from the Customer Master File. The billing/shipping information is copied from the Customer Master to the Order Header. It can be changed and is stored in the Order Header File. This makes for more storage (i.e. in the Order Header) but insures that the order, whenever reprinted, will have the "exact" bill to/shipping information on it for that order. It is a nice clean simple approach. In times past when storage space was more of a premium I would have sought other alternatives, but compared to 20 years ago storage is dirt-cheap. Based on the fact you are going to use Access I would suspect that record volume will be low.

In your case I would have a "Customer/Constituent Master" [Constituent is the government word for ‘Customer’ ] with current update to-date information. The "forms" tables would always have a copy of the information, from the "Customer/Constituent Master", embedded in them, current as of the time when the form was filled out.

Another thing you may want to consider is how many times will a "customer/constituent" fill out a form? Depending on this you may not need a complete "master customer file" but only a partial one for “repeat” form fillers. Also how will you uniquely identify a "customer/constituent"? Phone numbers change too often and individuals will not give up their Social Security Number readily. If the customer base is mostly corporate I would use a unique alpha/numeric identifier but this approach for individuals becomes more problematic. If your application involves something like "Building Permits" perhaps the property address is the correct choice for main "master file".

In any case the ultimate design will always be dictated by function. Make a "paper" design of the table structure and it's relationships before you begin. It is easier to change the design on paper than it is the application.

Hope this helps.

 

[virtualjm]

Thanks everyone for your posts! As always, there is never a single answer. Obviously there are a lot of details that I did not include to keep my post concise. I will be sitting down tonight to run through a couple different scenarios and your insights will be most useful. I will post a follow up when I get through this process just in case you are interested in the result. Thanks again for sharing your experiences. Regards, Jay

 

[MichaelRed]

Another consideration to fling into the inferno will be the actual Forms. Ther will also change over hte life cycle of any real db, so you will need to do the versioning/tracking of these -and for each record, include the version of the form/report used for the record generation/publication. NOw, IF I have gotten your attention, you need to be able to access the records through some mechanisim which is NOT the original form so you can instantiate the original form / report with the record.

Now, since the atual application design (i.e. MS Access itself) will be undergoing some evoloution (with some "features" becoming obsolete, while the functionallity of others may change), it woullld appear to be necessary to step back at least ONE more time, and store the db engine and programming languages versions with each record, so as to be able to instantiate the correct version of the program. Next, consider the printer. Now the operating system, the CPU ...

So, at some point all present will consider be just a nut case and ignore the post. On the otherhand, who anongst you still has MS Access 1.0? Or that Lj500 printer? Wwho claims to be able to re-pro the output of a dbII (DOS) app today?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over