Normalization...I have to break the rules! 2

[gusbrunston]

Hi.

I am a law-abiding citizen, and don't like to break the rules. But...

The client prepares a check to a vendor on October 31, 2001. On November 30, 2001, A new address for that vendor replaces the previous address in the Vendor table. On December 30, 2001, the Vendor says he never received the check. When looking at the check, the old address on the check written in October has now been replaced on the check by the new address. To find out where the check was mailed, the user has to dig out the paper stub. That's like using carbon paper on a typewriter instead of printing multiple copies from a word processor...

Instead of looking up information about the vendor when the check is entered and saved, information on the check that is vulnerable to future changes, like the address of the vendor, must be stored in the table of checks. Or, where did I go wrong?

Thanks for your comments. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[HoleInTheFoot]

Gus,

I'm assuming you have the folloing tables in your database :

1) Main Vendor table
2) Vendor Address table
3) The relationship between 1) and 2) is one to many.

Now in the Vendor Address table add a start date and end date fields this should over come the problem your are having by not allowing users to delete data from the Vendor Address table.

Thus checks can be associated to the correct Vendor Address record. Using the a query which validates if the check date is in the correct date period for the related vendor address record.

I think the above make sense.

ANy queries give me a shout

HoleInthefoot

 

[AlexMidd]

Excellent post, HoleInTheFoot. This has stumped me for a while, this type of problem. The answer is so logical as well, why didn't I think of it? Have fun! )

Alex Middleton

 

[gusbrunston]

Hi,

Thanks for your response.

Yes, your suggestion makes sense, and I could divide my one Vendor table into two, and add the start and end date fields that would accomplish what I need to do.

There are some things that influence my thinking...

I guess the principal one is that the client is subject to periodic audits by a state regulatory agency, and needs to be able to capture "point-in-time" data in many different tables.

For the time being, my thinking is that I will go ahead and store the redundant data. Then during my next vacation, I can go back and revise the tables, the queries (oh, the queries!), etc. Best thing I can think of at 4:59 am.

Thanks again.
Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[JimHorton]

Gus,
The multiple addresses thing is a good start, but an enhanced way to accomplish this is to have, as well as the Addresses table, a transaction table. Coming from nearly 3 years developing a banking system, it's my opinion that this offers further advantages.

First, a vendor may (and should be able to) have many Addresses active at one point in time, so the date range has limitations. The transaction table stores the ID of the address that was used for *that* transaction. The address can't be delted or changed--any changes to any addresses are regarded as a New Address, and a new ID.

Second, there are other items of data that may need to be frozen in the transaction table record, such as the contact ID at the time of the transaction, the P.O #, pricing data (which obviously changes constantly), department ID, etc.

It depends on how involved you want to get, this is just another opinion...
--Jim

 

[gusbrunston]

Thanks Jim:

Transaction table! Yup, I've got one, and all checks, deposits, extra-register transactions (transfers between related client accounts, etc.) go in there. That's where I store the stuff that needs to be retrieved. Then I "auto lookup" names, addresses, chart of account names, etc.

I'm faced now with the decision of what is essential in that table and what can be "looked up". For the time being, it may contain some redundant stuff, but I promise the gods of normalization (and there are quite a few), that I will come back and address this design sooner or later.

Thanks for your help in this present time and in all ages past. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[mac318]

With today's high speed technology, I believe it is normalization rules which should be revisited. I have several point-of-sale applications which store both the purchase and sale data in the same transaction record; i.e., each record stands alone once it is placed in the transaction table. My clients can retrieve archived data many years after its entry without concern for missing related tables. This especially true for intranets. Why use e-commerce rules to shackle an intranet application?

mac

 

[JimHorton]

Well, it's not so much the Normalization rules themselves that need to be revisited, they serve a purpose, rather it's the choice of whether to normalize or not, and how, when you put on your Analyst hat, you design the system.

In a Data-Warehouse, the designer may (and often does) choose to forego the normalization rules, and typically a 'Transaction' type table in any database is going to fit a data-warehouse strategy. But it still can be 'Normalized', if the architecture and program logic is set up correctly. In my example, the Transaction table would store the AddressID--not the Street, City, etc. So in that sense it's Normalized, but the logic says that in ID can never be changed or deleted. Here you gain both advantages--the storage efficiency of normalization and the data snapshot utility of the DW.
--Jim

 

[MichaelRed]

Gus.

Bddn there as well. IMHO, you need to go back to the concept of the db being a model of the "real world". In that rather messy and disorganized world, changing stuff like addresses, costs, prices ... all relate to current and future transactions. An actual event such as a sale, or payment (e.g. CHECK) need to be recordes AS IT OCCURED. You may clollect the information necseeary to generate the check from the nice dynamic tables - but you SHOULD capture the actual check info as it occurs in a seperate table. These 'transactions' may usually be moved to an archive after some period but they should record the physical activity as it actually transpires, not just 'reference' information via keys / indicies.

MichaelRed
m.red@att.net

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

 

[johncb]

Normalization requires the reduction of redundant data.

We try to place non-volatile data into a single place where we can assume consistency and simplicity of maintenance.

What is being spoken about here may well be considered volatile data. The address a cheque (check for US people) is sent to could be viewed as variable in the same way that the amount of the cheque is variable.

For example, a company may have a single Head Office address but hundreds of branch addresses, even transient ones.

There is nothing wrong with normalization, you just need to apply it appropriately.

John