Design suggestions

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
--

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.

--
Dave Hargis, Microsoft Access MVP


"NNlogistics" wrote:

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
--

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

"NNlogistics" wrote in message
...
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
--

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.

--
Dave Hargis, Microsoft Access MVP


"Jeff Boyce" wrote:

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

"NNlogistics" wrote in message
...
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
--

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

"Klatuu" wrote in message
...
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.

--
Dave Hargis, Microsoft Access MVP


"Jeff Boyce" wrote:

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

"NNlogistics" wrote in message
...
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
--

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

Jeff

"Jeff Boyce" wrote in message
...
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

"Klatuu" wrote in message
...
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.

--
Dave Hargis, Microsoft Access MVP


"Jeff Boyce" wrote:

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

"NNlogistics" wrote in message
...
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
--

The two models are not really alternatives of choice; each is governed by the
real world business model and only one is right for the business model which
applies.

1. Including the unit price in the OrderDetails table (which is how Northwind
does it BTW) is correct if the price per order can be varied ad hoc at any
one point in time, e.g. by preferential pricing for favoured customers
negotiated per order. In this case the unit price is functionally dependent
solely on the whole of the key of the OrderDetails table, so it is correctly
normalized. If, however, the unit price per product is fixed at any one
point in time regardless of the customer or any other factor, then it is not
functionally dependent on the key of OrderDetails, so the table is not
correctly normalized and there would then be nothing to prevent different
unit prices for the same product/date invalidly being entered in different
rows in the table.

2. Where the unit price per product is fixed at any one point in time
regardless of the customer or any other factor then it is functionally
dependent on the key of your Pricings table, i.e. the Product/DateFrom/DateTo
columns. So in this scenario your 'Plan B' is the correct one, joining the
pricings table to the Orders and OrderDetails tables on the date columns to
return the unit price which applies at any order date.

3. There is a third common scenario, which in effect combines the above two.
In this the unit price per product is fixed at any one point in time, so is
functionally dependent on the key of Pricings. Prices may be variably
discounted per customer, however, to produce the net unit price applied. If
the discount per customer is a fixed time independent ratio which applies to
all products then it is functionally dependent on the key of Customers, so is
correctly a column in that table (this is an unlikely scenario); if it varies
per product per customer but is time independent then it is an attribute of
CustomerDiscounts, so is modelled by a table with CustomerID, ProductID and
DiscountRatio columns (this is also unlikely); if it is a fixed ratio per
Customer which applies to all products but varies over time, then the key of
CustomerDiscounts is CustomerID, DateFrom and DateTo; if it varies per
product per customer and can change over time, then the key of
CustomerDiscounts is CustomerID, ProductID, DateFrom and DateTo (strictly
speaking only DateFrom is necessary in the last two situations, but it makes
things simpler to include both columns as the join is then on intersecting
ranges).

4. And there is also the question of discounting per quantity of course,
which may need to built into the model.

Ken Sheridan
Stafford, England

Jeff Boyce wrote:
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

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
[quoted text clipped - 18 lines]

Any advise would be appreciated

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

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.


--
Thanks for any assistance


"NNlogistics" wrote:

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
--

The topic is far from mundane; it raises issues which are fundamental to good
database design and the more discussion and debate on such issues the better.


The same principles apply to any values which will change over time, such as
shipping charges, tax rates etc. These should be stored in columns (fields)
either in the Orders table or in the OrderDetails table depending on which of
these two entity types they are attributes of, which is the simplest scenario
and probably suitable in most cases for the reasons Dave has described; or in
separate Pricings, ShippingCosts, TaxRates tables etc which include date
columns, which is more complex to implement, but might in some cases be the
more correct solution as discussed in my first response.

If you do decide to go for the simpler approach, the current unit price per
product etc would still be stored in the Products table, and used to get the
default price etc when raising an invoice or order. The sample Northwind
database does this with the unit price in the AfterUpdate event procedure of
the Product control on the Orders Subform.

As regards changes in the Customers table then it really depends on what the
changes are and how you want to handle them. If a customer address changes
for instance do you want all previous invoices/orders for the customer to
reflect the changed address and no record to be maintained of the old address,
or do you want the invoices to retain the address which was current at the
time of the order/invoice? This is for you to judge, though in some contexts
auditing rules might have a bearing on how its done. If the former you would
store the address solely in the Customers table, if the latter in both
Customers and Orders (not OrderDetails in this case as the address is the
same for each order line of course, i.e. its 'functionally dependent' on the
key of Orders not of OrderDetails).

I'd advise against storing the 'historic' invoices externally; if you want to
maintain the history then design the database so it does that. For one thing
they are then data in the database and can be interrogated at any time if
necessary.

Ken Sheridan
Stafford, England

NNlogistics wrote:
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.

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
[quoted text clipped - 12 lines]

Any advise would be appreciated

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

On Fri, 9 Oct 2009 04:37:01 -0700, NNlogistics
wrote:

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.
--

John W. Vinson [MVP]