Page 8 of 27

Posted: Fri Aug 25, 2006 9:32 am
by Benjamin

Posted: Fri Aug 25, 2006 9:37 am
by Jenk
Take these scenarios.

1.) Product obselete, but orders outstanding. Product is removed from products table. Now you have a foreign key to nothingness on your order table. Which product did the customer order?

2.) Product is changed, for example price. Customer ordered at 'x' price, but the order table now says 'y' price.

3.) Reporting - everything is in one table. Easier to read even if the only storage medium you have is an SQL dump.
3.b) Your choice of storage platform dies of death. Some super huge vulnerability is discovered which renders it completely obselete and the only option to protect your data is to shutdown the software. Even non-savvy people will it easier to read n SQL dump, over having to manually map product to order across multiple tables.

4.) Backups - everyting is on one table. Things go pete tong, it's much easier to restore from a backup when all data (important data at that) is in one table. Same as 3/3b.

Remember.. it's a shop, the orders are the number 1 priority for safeguarding (alongside customer credit details etc.)

Posted: Fri Aug 25, 2006 9:45 am
by jamiel
1) Like I said before, Product's shouldn't be removed, but should definately be able to be made "Inactive".

2) The Price should be linked to the Transaction, not the Product or Order. And Prices should be in their own table linked to Product ID's.

3a) Rule one of Database Normalization.
3b) I agree that it will be complex for an admin to make head or tail of the DB if he has no front-end to it.

4) Refer to 3a.

Posted: Fri Aug 25, 2006 9:53 am
by Benjamin
Storing the product description for every sale is not practical, especially for a high volume store. I propose the following, if I left anything out let me know.

For each product ordered, the Product Name, Quantity, Product Options, Product ID & Product Price will be copied into the customers order history table.
When a customer views their order history, if the product no longer exists, only this information will be displayed. If the product does exist, there will be a link to the product which allows them to view it.
There will be a disclamer stating that product descriptions and prices are subject to change.

I believe that will solve the problems your all talking about.

Posted: Fri Aug 25, 2006 9:53 am
by jamiel
I feel I should elaborate on my second point to avoid confusion. The Price that was paid for the Order should be stored in the Transaction table. The price of the actual Product in the Prices table. That way when Prices change, Transaction records remain in tact. And the reasoning to moving the Transaction details out of the order is so that multiple payment gateways, currencies, credit cards are easily supported and accounted for without cluttering the order table.

Posted: Fri Aug 25, 2006 9:58 am
by jamiel
That disclaimer sounds like an unneccessary compromize. I'm pretty sure we can agree to a schema. And yes I do realize I have stood up again ;)

Posted: Fri Aug 25, 2006 10:01 am
by Oren
astions wrote:Wiki Board

http://astions.com/projects/wiki/
Get a better template (see Patterns for PHP) :wink:

Posted: Fri Aug 25, 2006 10:02 am
by jamiel
Another scenario why I dont like moving the product into the order, is if I buy 2 jersey's and 1 pair of socks, its three products but one order. Its NOT 3 orders. Its 3 Items, 1 Order, 2 Products. Hence the need for an order table, an Item table and a Product Table.

Posted: Fri Aug 25, 2006 10:03 am
by feyd
Just store the line item information. Link to the full product if available. Use the product SKU, not the product table's primary key for linking.

Posted: Fri Aug 25, 2006 10:04 am
by jamiel
Another key feature is to handle cancelled Orders or Refunds. Which is where the Transaction table comes in handy.

Posted: Fri Aug 25, 2006 10:07 am
by jamiel
How are these SKU's generated, how are they stored and how are they any better than the primary key?

Posted: Fri Aug 25, 2006 10:08 am
by Benjamin
Using the sku ensures that it's linked to the correct product. Problems could occur with imported data or a corrupt database.

Posted: Fri Aug 25, 2006 10:11 am
by Jenk
And what is an SKU I hear you cry (I did)?

http://en.wikipedia.org/wiki/Stock_Keeping_Unit

Posted: Fri Aug 25, 2006 10:11 am
by jamiel
If the SKU is simple sitting in the product table, and we copy it into the order table with the description etc. when the order is made, then surely if we assume the admin is stupid enough to change his products to something completely different rather than adding a new product (which was onions case), we have to assume that he is stupid enough to use the same SKU for his newly edited product too.

Which brings me back to Products shouldn't change or be deleted, only made inactive.

Posted: Fri Aug 25, 2006 10:12 am
by Luke
jamiel wrote:How are these SKU's generated, how are they stored and how are they any better than the primary key?
SKUs are generated by the user... I would find a commerce solution useless if I had to reform my sku system just to use it. They are stored just like any other information about the product. They are better than primary key because primary key means nothing to our user.... skus are what he uses.