Help Rebuild OsCommerce

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

astions... I like your solution 1 vote here :D
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
sike
Forum Commoner
Posts: 84
Joined: Wed Aug 02, 2006 8:33 am

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

8O 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 :wink:
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
Last edited by onion2k on Fri Aug 25, 2006 10:42 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

astions wrote:8O 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 :wink:
I'd love to. That's the kind of thing I enjoy. Image
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
mikesmith76
Forum Commoner
Posts: 34
Joined: Fri Aug 25, 2006 7:10 am
Location: Manchester, UK

Post 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 8)

Mike
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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')
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I believe we need to make a list of pages, along with the features that each page should have.
Post Reply