Design suggestions

[NNlogistics]

I have a retail related DB and I realize that may not have thought through
some design elements. It involves orders. I have an Order Table, A Order
Detail Table, Customer Table and a Products Table. Current orders are not a
problem, however past orders are in this repect - pricing-. If a price
change has occurred since the order was entered and a customer calls and ask
for a copy of the invoice, it will show the current price. Come to think of
it, if any info in the Customer Table has changed , it will also show the
current info.

The problem, I believe, is in the initial design, I didnt think it through.
How does one in general keep a history without vilating every "Normalization"
rule? My temporary fix is to save an .snp of the invoice on the same sever
on the back end, giving it a name that has the Order #, first few Customer
characters and the date.

Any advise would be appreciated
--

 

[Klatuu]

What you are describing is normal and the remedy is not violating
normalization rules. Because prices and other information in you data
change, it is usual to store the data in the orders tables.

Store the order information as it is at the time of the transaction rather
than using a query to link to it later.

 

[Jeff Boyce]

Two approaches to consider, depending on your long-term information needs
and your experience (or comfort with learning new things).

Dave's approach is a common exception to the general rule that says not to
store that kind of data redundantly. But because the amount is dependent on
'point-in-time', it could be argued that you aren't storing data
redundantly.

The other common approach comes into play if you might have need now or
later to review price history. If so, and if you're feeling lucky (*or
brave*), your pricing table can include From and To date/time fields. Each
item gets stored in a separate "ITEM" table, then the pricing history in
your pricing table. You can see the same item stored many times in that
table, once for each change in price.

You can determine the unit price and 'extended price' of a quantity of the
item by knowing which item, and what order date (and looking up the
price-as-of-that-date in your price history table.

More complex, certainly. What is your current (or potential) business need?

Regards

Jeff Boyce
Microsoft Access MVP

 

[Klatuu]

Hey Jeff,

Well there is a reason for the way I do it. The point in time approach
works well if every customer will always pay the same amount for the same
item; however, I have been in BtoB environments where different customers
paid different prices based on a slew of business rules. (negotiated rates,
quantity breaks, etc), so I found it much more manageable to plug the
calculated item price in the order detail. Now, I would argue it is neither
redundant nor violates the rule against calculated values becuase the price
calculated today may not be the price I calculate next month and I may or may
not have all the data necessary to accurately calculate the price.

I do use the point in time approach for sales tax because contrary to
popular belief, taxes do go up from time to time And, in Texas, we have a
Sales Tax Holiday weekend that is just before the school year that applies to
any item that could be needed for school.

 

[Jeff Boyce]

Dave

Yeah, I agree, this situation represents one of the valid exceptions to the
general 'rule'. And it is certainly a lot easier than incorporating all
those different price conditions.

Jeff

 

[Jeff Boyce]

<G!> ... but how hard could it be ...? <G!>

Jeff

 

[NNlogistics]

Thank you all for your thoughtful response's. Its nice to see that even a
topic that some might conclude as mundane, garnishes such a varied opinion.

I just dont know where to draw the line. Storing the Unit cost sounds
resonable but what if there are changes in the customer table or the shipping
charges for instances. The Order detail becomes quite the snapshot. Does the
idea of storing invocie history in snp files as I have suggested and using
the DB to store and retreave have any merit or is it heracy?

Thanks again, please continue, this is the best part of design.

 

[John W. Vinson]

Does the
idea of storing invocie history in snp files as I have suggested and using
the DB to store and retreave have any merit or is it heracy?

Unless the *visual image* is the critical thing to be stored, I'd argue
against it. .snp files are not data and are not searchable; they're just a
different variation on a .bmp or .jpg graphical image of a report. My guess is
that you are more interested in the data (prices say) underlying the image,
not in the image itself; and a price can be stored in 8 bytes, versus many
kilobytes for a .snp image.