Page 9 of 27
Posted: Fri Aug 25, 2006 10:15 am
by jamiel
Don't get me wrong I'm all for a SKU in the Product Table. Sounds like a very good idea in fact.
Posted: Fri Aug 25, 2006 10:17 am
by feyd
I've been talking in terms of two tables so far for keeping the order. Order table itself and the line items table which, in my book is product SKU, it's short description, price per item, quantity, shipping package id and status. The short description would be at best a full VARCHAR.
The order table would contain information global to the order. No shipping information. A final subtotal which involves the line item table, coupon/rebate table, and so forth.
Normally I would be all for normalization, but since I've worked on several cart systems I found some denormalization helps. Not too much, but a tiny bit.
Posted: Fri Aug 25, 2006 10:23 am
by Benjamin
jamiel, your arguments are valid, but I don't feel that your solutions are practical. If everytime a customer made a change to a product, it created a new product rather than modify the original, the database would become very, very cluttered over time.
The best solution is to simply educate the store owners on how to properly use the system, and do the best we can, within reason, to prevent things like that from happening. I feel that the solution I already mentioned is probably the best one if you look at the big picture. As feyd recommened, using the product sku is better than using a product ID.
I really can't see any organized business owner using the same sku for multiple products. That would make reporting a nightmare.
Posted: Fri Aug 25, 2006 10:25 am
by Luke
astions... I like your solution 1 vote here

Posted: Fri Aug 25, 2006 10:29 am
by onion2k
Maybe this is an opportunity to do something better. Just a thought .. what about a journalling system for the products? Store the product once, and on each update store a record of what changes (Sort of like a wiki). When someone buys something you'd just need to record the item SKU and the version number .. then when you view the product in the order history the application would get the base product, run all the modifications up to the right version, and you'd have the correct product details.
It'd be damn complicated to code .. but really cool.
Posted: Fri Aug 25, 2006 10:31 am
by sike
i am unsure if marking products as "inactive" will allways work. what if i have a shop where the products are changing frequently (e.g. selling sesonal stuff)? after a couple of seasons i have a lot of products that never will get used again (including e.g. 3 images per item) resulting in rather huge tables and a lot of images.
chris
ps. i think database related things should be discussed when we have solid requirements and some use cases
Posted: Fri Aug 25, 2006 10:31 am
by Benjamin

That would be awesome onion, but would it be able to work on a high traffic site? Is it pratical? Would you like to build that module

Posted: Fri Aug 25, 2006 10:38 am
by onion2k
sike wrote:i am unsure if marking products as "inactive" will allways work. what if i have a shop where the products are changing frequently (e.g. selling sesonal stuff)? after a couple of seasons i have a lot of products that never will get used again (including e.g. 3 images per item) resulting in rather huge tables and a lot of images.
That's not a problem .. flag things as inactive, but also give the admin an option of purging products with some filtering .. delete all products flagged as inactive and over 1 year old for example, or flagged as inactive and never been sold, or flagged as inactive and priced less than £10 .. you get the idea. That's really a code problem more than a database problem.
Besides, I have a database that has nearly 25,000 old events stored in it .. and growing at a rate of 1,000 a month. Lots of old records isn't a problem if they're hidden away from sight in the database.
Posted: Fri Aug 25, 2006 10:39 am
by feyd
Re: wiki style; just make sure that "minor edit" is available.
If you go that route, I would actually do the reverse. Keep the current state complete and diffs for past versions. This has the benefit of not needing to run a compact when viewing products normally, but only when someone wants to see an older version.
On versions you have two choices: individual versions associated with each product (ala CVS) or atomic versions whereby everything is the same version (ala SVN)
Posted: Fri Aug 25, 2006 10:42 am
by onion2k
astions wrote:
That would be awesome onion, but would it be able to work on a high traffic site?
Obviously you'd hold a copy of the current product description alongside the journalled version .. it'd only be old versions that would need to be built on the fly. Traffic shouldn't be a problem.
astions wrote:Is it pratical?
My ideas often aren't. Practically is for the weak.
astions wrote:Would you like to build that module

I'd love to. That's the kind of thing I enjoy.

Posted: Fri Aug 25, 2006 10:44 am
by jamiel
Astions, then if thats a solution, with my schema a customer could change anything in the product he likes, we would just need to educate him not to change what the product actually is. I think an eCommerce system that contains all the order details in one row is heading for problem's. I propose splitting things as far as the actual purchase goes into Transaction, Order, Item, Product at the least for what you guys are trying to do unless you want to limit yourselves heavily later.
I need to put my head down and work for the last few hours of friday before the weekend, so cannot contribute any longer unfortunately but hope some of my ideas will be considered later in the project.
Jamie
Posted: Fri Aug 25, 2006 2:36 pm
by Benjamin
Jamie, thanks for the input, we will definetly be considering all of options.
Everyone, I threw up a test store. It required 6000 code changes (changing $HTTP_GET_VARS to $_GET in every file) and 3 bug fixes to get it running but it seems to be somewhat decent now.
http://astions.com/projects/store/index.php
Posted: Sat Aug 26, 2006 10:06 am
by mikesmith76
Hi all,
Just a quick note from a long time reader - first time poster on this message board. I've gotta say it's really impressive that you've all got together to develop a full e-commerce application. I've only been doing php development for the last two years but if there's anything i can help with on this project i will.
Anyway see you all around the forum
Mike
Posted: Tue Aug 29, 2006 5:23 am
by Jenk
Right.. so then, what should we get started on first.
Application overview? (i.e. high level design, possibly UML or similar diag's etc.)
or Framework/Codebase?
(fyi: added my details to the 'Offical Volunteers List')
Posted: Tue Aug 29, 2006 6:01 am
by Benjamin
I believe we need to make a list of pages, along with the features that each page should have.