Acceptable condition to break normalization rules?

C

CS

I am building a database that will have:
tblCustomers (PK CustID)
tblPets (PK PetID 2ndKey CustID -- many pets may be owned by each customer)
tblVisits (PK VisitID 2nd Key CustID -- each customer may have many visits,
and each visit may involve many pets)
tblVisitDetails (PK DetailID 2nd Key Pet ID -- each Visit may have many
treatments/medications, details will each involve one pet and the
treatment/medication administered)
tblTreatments (PK TreatCode, price of treatment, description)
tblMedications (PK MedCode, price of medication/dos, description)

Because prices of medication and treatments change over time, and I don't
want these changes to update old invoices, I have created an append query to
create and add to tblInvoiceArchive -- this has an indexed field on DetailID
from tblVisitDetails, so that only unduplicated detail records are appended
to this table.

This does result in duplicated data in my db -- I am wondering if this is
considered an occasion where breaking the usual normalization rules is
correct?

Can I reduce duplication of the data, yet still retain the important
information for the invoices, such as calculations based on current prices,
by including all the fields that I need for a report or form (CustName,
Address, Pet Name,TreatmentDescriptions, etc.) in the query, but only having
certain key fields that append to the Invoice Archive table (such as
including only CustID, PetID, VisitID, and any fields calculated based on
the current price in the tblInvoiceArchive)?

Thanks in advance for any advice,
CS
 
D

Douglas J. Steele

If I'm understanding what you're saying correctly, you're essentially
duplicating the data from tblVisitDetails in tblInvoiceArchive. If the only
reason for doing that is that you're concerned about price changes, why not
add a "CurrentPrice" field to tblVisitDetails to indicate the price that was
in effect at the time? Far less duplication.
 
J

John Spencer

This also has an advantage in that you can change current price to some other
value if you want to do a one-time special price for one customer. Say as an
apology for having an inordinate delay in shipping one item in a multi-item
shipment.

Of course, there are other ways of handling that situation.
 
C

CS

Thanks for your responses.

The reason I did not choose to do it this way is that there could
potentially be many different prices on each invoice -- including but not
limited to:

The basic fee for making the home visit (CalloutFee -- changes if an
emergency)
The basic hourly fee for the visit (HourlyFee -- changes if it is emergency)
These basic charges would be related to the Visit itself, and would be
entered or calculated on (in the case of hourly rate) in fields in
tblVisit. I have created a tblBasicCharges to draw these fees from, to
assure data-entry integrity.

The fee for each of up to 20 treatments for each visit, as many animals
could receive treatment on a specific farm, and a medication associated for
each treatment, each with their own price.
These charges would be related to the specific detail, and drawn from the
treatments and medications tables.

I am concerned that having to hand-enter a "current price" as hard data in
each detail would be cumbersome to the user.

The idea was to automate the data entry as much as possible -- in other
words, select the treatment in a drop-down and this grabs and enters the
current price listed in the treatment table, rather than having to enter the
current price by hand. I had intended to enter any discount (as in the
apology example) at the end of the total bill.

The idea for this db is to allow the vet to enter the treatment and
medication notes quickly (without necessarily fussing with each price --
just entering the treatment notes), then hit a button, generate an invoice,
and print it in the field. Problem is, the vet wants a record of the
invoice as it was when printed.

Would there be any way to "grab" the prices from the current tblTreatments,
tblMedications and enter this amount automatically as hard data in the
VisitDetail "current price" field (and the current base prices fromt the
Charges table and enter it as hard data in Visits)? Most of the schemes
that I've come up with (eg. getting this info from the tblTreatments in a
form) would update past records each time the form is refreshed.

Thanks for any help.

CS
 
D

Douglas J. Steele

I don't think John's suggesting hand-entering the prices.

Fill in the prices from your tables. However, since they're bound to a
different field, you can easily change any price just for that one visit.

Of course, this means you'll have to do some work to get the prices into the
correct boxes in the first place. Your underlying recordset could have both
the current prices from tblTreatments and tblMedications, and you'd copy
them from those fields into the price fields in your Invoice. Depending on
how your form's built, you'd probably do this in the form's Current event.
 
C

CS

Thanks Douglas -- Here's my question -- if this price is being copied to
the price fields in the Current event, wouldn't this update them every time
this form was displayed?

How can I enter the current price when the record is entered in the form,
but not update it if the form is viewed? Use only one form for new Detail
entries (a data entry only form), and a different form to display records
that are already entered that does not do the copy procedure? Is this a
solid approach?

Thanks
CS
 
D

Douglas J. Steele

In the Current event (or wherever you end up putting the logic), check
whether it's a new record, and only do the copy then.

If Me.NewRecord Then
' do the copies
Else
' don't do the copies
End If
 
J

Jay Gamel

Grover Park George solution is to set up the price table with a price
effective date. That way you can keep all the records historically intact.
That's how I handle prices and sales tax increases. No normalization
problems.

Jay Gamel
 
J

John Spencer

I am much more comfortable storing the "sold' price in the item records.
That way if someone accidentally (or deliberately) changes the "list" price
all my sales record do not change. Suppose you've invoiced your customer at
a 5% tax rate and two days later realize the tax rate should have been 5.5%.
And to complicate matters, the customer has already paid. How do you get an
accurate invoice if you haven't stored the information with the record?
Especially if you get audited?

Too each his own.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

Douglas J. Steele

It's actually still possible to use Effective and Expiry dates and handle
the situation you've described, but it's messy.

You need to include a WhenModified field in your table, and compare the date
of interest (usually Now, but in your example, you'd probably use
WhenBilled) to the WhenModified field, as well as to the Effective and
Expiry dates.

I know I implemented a fairly complex pricing system this way many years ago
(TSO-ISPF-DB2). Unfortunately, I can't remember the details of whether we
reset the Expiry date when we determined it was incorrect or left it and
relied on the presence of a Changed flag. However, I do recall that we were
able to return results for "What would the system have to me the price was
at 2007-01-10 08:30 if I asked at at 2007-01-10 08:30, if I asked at
2007-10-13 14:30 or if I asked now?"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John Spencer said:
I am much more comfortable storing the "sold' price in the item records.
That way if someone accidentally (or deliberately) changes the "list" price
all my sales record do not change. Suppose you've invoiced your customer
at a 5% tax rate and two days later realize the tax rate should have been
5.5%. And to complicate matters, the customer has already paid. How do you
get an accurate invoice if you haven't stored the information with the
record? Especially if you get audited?

Too each his own.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Click to expand...
 
Top