Easy dB question for anyone familiar with the eCommerce tutorial

[logical_c]

In the eCommerce tutorial's data design, there's a Products table (for all the products for sale) and an Ordered_Products table (to track orders).

My really basic data model question has to do with the actual price that a product is sold for. It doesn't appear to be written to the database anywhere.

As I'm going through the tutorial, I'm near the end and looking at the confirmation JSP that shows when an order is complete. To show the summary of what has been ordered, the product unit price is pulled from the Products table and multiplied by the quantity from the Ordered_Products table.

That's all fine for showing a customer at the time of order. But if the store owner ever changes the price of a product (i.e. demand for cheese goes up so the shop owner increases the price price of cheese in the Products table), she has forever lost the price that she sold any of that product for in the past, hasn't she? She's got the # of each item ordered and the total order price, but no line item prices.

I realize this is just a tutorial and it's an outstanding one and surely isn't intended to be a world class shopping cart to compete with all the commercial ones out there. I just want to make sure I'm not missing something obvious in the data model. Seems to me one would want to write the actual price paid for a given product to the Ordered_Products table, no?

[TD]

In a word, yes.

You'd save the pricing details for the order into the ordered_product table,
which, since it is linked by an FK relationship to the customer order, would
enable you to retrieve historical order details, together with item pricing
for that order, from the database.

The other option is to create a new product record every time you change the
price of an item, which means you end up with loads of 'stale' product items
in your table, which isn't very sensible, but has been done in the past.
That way 'old' customer order information always points to 'old' product
data, and price changes aren't relevant, you can still reproduce the correct
pricing information for the order. As I said though, better to add one row
to a table than to retain stale data.

However, if you were to fully normalise the data stored in the database,
theory says you should create a new table containing the price data for the
sale, linked to the ordered_product table, since this data, in some people's
view, isn't atomically related to the order, but is a 'snapshot' of the item
price data at a specific point in time i.e. the separate table would reflect
only the price of an item at a specific time point, defined by the moment
the order was placed. However, all database design is a compromise and most
people would just add it to the ordered product table, since this enables
easier transaction handling.

Since the tutorial doesn't give you a way of changing the price of an item,
I guess the complication wasn't considered important .. or maybe it was a
challenge to see if anyone spotted the problem, as you have!

Further questions for you to ponder:
1. How do you record partial discounts or post-sale adjustments to partial
orders? Such as 'Buy three, pay for Two' when the customer orders 7 items of
a particular line, or discounts offered to a customer subsequent to the
placing of the order for accepting delivery containing some out-of-date
items, even if the majority of that product line is in-date? Treat it as a
separate transaction and record it as a refund, which means you have to
create an artificial link to the original order? Or split the original order
record into two records and amend the price details for one of those
records, which means that you have to find some way of recording WHY the
split occurred?
Hint: Many companies treat these sort of transactions as a separate product
i.e. they record the discount as a product with a negative cost, but with
the same order ID to keep the relationship with the original customer order.

2. How do you deal with/record an order cancellation, at any stage of the
picking/delivery process? After all, the end product (the actual price
charged) of any sales system needs to be fed thru to the company's financial
processing system while retaining the link to the actual order so that the
bean counters can mark the order as 'paid' when the money arrives. A
cancellation of an order needs to trigger a reversal of the sale entry into
the financial system AND potentially cope with any costs related to raising
the order.



Tony


-----Original Message-----
From: logical_c [mailto:address-removed]
Sent: 27 September 2010 22:42
To: address-removed
Subject: [nbj2ee] Easy dB question for anyone familiar with the eCommerce
tutorial

In the eCommerce tutorial's data design, there's a Products table (for all
the products for sale) and an Ordered_Products table (to track orders).

My really basic data model question has to do with the actual price that a
product is sold for. It doesn't appear to be written to the database
anywhere.

As I'm going through the tutorial, I'm near the end and looking at the
confirmation JSP that shows when an order is complete. To show the summary
of what has been ordered, the product unit price is pulled from the Products
table and multiplied by the quantity from the Ordered_Products table.

That's all fine for showing a customer at the time of order. But if the
store owner ever changes the price of a product (i.e. demand for cheese goes
up so the shop owner increases the price price of cheese in the Products
table), she has forever lost the price that she sold any of that product for
in the past, hasn't she? She's got the # of each item ordered and the total
order price, but no line item prices.

I realize this is just a tutorial and it's an outstanding one and surely
isn't intended to be a world class shopping cart to compete with all the
commercial ones out there. I just want to make sure I'm not missing
something obvious in the data model. Seems to me one would want to write the
actual price paid for a given product to the Ordered_Products table, no?

[logical_c]

Crikey! I feel like I should have to pay for that answer. Thanks a million TD. Very much appreciated.

And thanks for all the great food for thought too. Good thing I'm hungry.

[Troy Giunipero]

There were certain trade-offs involved when designing the application for the tutorial, and this is an example of that. One of the concerns I had was to eliminate any code or functionality that isn't directly relevant to the presented 'customer requirements', and wouldn't be explained at any point in the tutorial. In this case, because the requirements for the admin console didn't include changing the product prices, it wasn't immediately necessary to store prices in two separate tables.


But the down-side is as you point out - if the application were to be extended to enable administration to modify prices, and possibly allow customers to "log in" and view previous purchases, it would be necessary to modify the data model.


As far as I'm aware, there are two approaches you could take to extend the application in this manner. You could simply add the new property to the Ordered_Products table manually, then allow the persistence provider to update the database schema, or you could modify the data model itself, regenerate the DDL script, and rerun the IDE's Entities from Database wizard. (The JPA metamodel for the application remains virtually unchanged from the generated code.)


troy




On 27 Sep 2010, at 22:42, logical_c wrote:

[logical_c]

Thanks a ton Troy. I opted for your first suggestion of manually adding a "price_paid" field to the ordered_product table manually. But....

[TD]

All contributions welcome.

I have three greedy children, too many ex-wives and an expensive holidays
habit to support.

A decent job would do, anyone know one available? This one is boring me to
death! And doesn't pay enough for the holidays either. Somewhere hot, dry
and with a nearby beach would do nicely ....

Tony


-----Original Message-----
From: logical_c [mailto:address-removed]
Sent: 29 September 2010 16:57
To: address-removed
Subject: [nbj2ee] Easy dB question for anyone familiar with the eCommerce
tutorial

Crikey! I feel like I should have to pay for that answer. Thanks a million
TD. Very much appreciated.

And thanks for all the great food for thought too. Good thing I'm hungry.

[Troy Giunipero]

On 29 Sep 2010, at 21:36, logical_c wrote:

[TD]

Retaining the data is the same process you'd do for any table structure
manipulations. I mean you do always back-up/export your data before making
any changes, don't you?

Dump the database using any of the normal tools, but omit the code to delete
and recreate the structure. Just export the data insert commands.

Make the changes to the database structure.

Reload the data into the tables. If you have any new index fields or
relationships that you've inserted into the structure, you have to get
creative, but that's what planning is all about.

Rule 1: Always keep the data safe.
Rule 2: Never make any changes you cannot later reverse without any damage.
Rule 3: Test your plans first. Then get a clean piece of paper and work them
out all over again, because you've missed something.
Rule 4: Always put the coffee pot on before you start work, because it'll
always take more time that you thought at first.

As a matter of personal preference, I tend to get MySQL Admin to export the
data and the structure creation script, then split that file into two.

Then get MySQL Workbench to create the new DDL script for the altered
database, without any data import, and manually compare the two. If there
are no changes that will cause the data imports to fail, I merge the two
files back together again and use MySQL Admin to regenerate the structure
and import the data back in at the same time, then delete the entity classes
in Netbeans and rerun the Entity Classes from Database wizard. I ALWAYS make
sure the Drop and Create option is left switched OFF. Too dangerous, IMO.


Tony

-----Original Message-----
From: Troy Giunipero [mailto:address-removed]
Sent: 30 September 2010 12:32
To: address-removed
Subject: [nbj2ee] Re: Easy dB question for anyone familiar with the
eCommerce tutorial


On 29 Sep 2010, at 21:36, logical_c wrote:

[logical_c]

Thanks again TD and Troy. Wow, I had no idea the persistence provider could modify table structure like that. I clearly still have lots to learn. Troy, your tutorial was my first introduction to JPA and persistence in general.

TD, I hope I would have been careful enough to backup my database before even making simple changes, but since this is all in development, I guess I've been a bit to cavalier with it all.

Effective now, I'll be much more disciplined about backups prior to mods because, even though it's test data, it's still a major pain to recreate it all.

By the way, I'm doing cartwheels right now because my humble little tutorial-based shopping cart is successfully communicating with the USPS's rate calculator API and presenting my shopper with real-time shipping based on the weight of items in their cart and the total volume (cubic inches) the items total up to.

I know, probably a major yawn for experienced folk, but it's my first success talking to an API on another server. Happy day for me.

Thanks to you both.

[aanyad]

Can a user be assigned more than one role in Java EE/Java? Like a user can be administrator and reporting-user at the same time. Both user roles can access the same resources but the admin can also access admin pages.
_____________________________________
Open Cart Ecommerce Development