eCommerce database

Ok, not going into any specifics here, but I've got a few ideas for a database which processes orders, users and shopping baskets. I'm looking for an opinion of how I can make it efficient, and whether there are any gaping holes in my theory.

Okay.

table baskets:
- basket_id (int)
- user_id (int)
- is_sessionid (bool)
- time (timestamp)

table basket_contents:
- prod_id (int)
- prod_qty (smalltext)
- total_cost (decimal)
- basket_id

When the user tries to add an item to their shopping basket, if it is the first item then the script will create a basket in "baskets", with a new basket id with their user_id (either their user_id from the users table or the session_id if they are not logged in).

It will then add the product to the basket_contents table, with their current basket ID, product id and the calculated cost and quantity.

Now... here comes the tricky bit. I need to work out how to make the guest baskets expire after a) their session ends or b) have been inactive for 6 hours, give or take a few.

I could have a scheduled task to check timestamps which runs every few minutes... but that doesn't sound very efficient to me.

Or, I could have a script that is run by the logged in users to delete them that way, along with the orphaned items in the basket_contents table.

Anyone else think of a better way to do it?

 

Don't know about MySQL but in SQL server, Timestamp is binary data and not a date/time value.
Are you going to be keeping a history of this at all? I would.. would make it nice for scalability... you could offer ideas to them as they are shopping. "You bought blah 20 days ago, do you need to re-stock?" etc etc..

 

timestamp has its own datatype in MySQL, which is handy

But yeah... it will keep a history, but in the orders table. or maybe an old_orders table.
The process being:

catalog - > basket -> checkout (transfer money) -> transfer data to orders table -> process order at shop -> set order to be "dispatched" ...

After that I can do what I like with the order data. Would be an idea to move dispatched orders somewhere else, keep the overheads down on the orders table and get rid of "useless" entries. And I can also get rid of information thats not needed for archiving (like the basket ID) which would again reduce the size, and keep it all compact.

I could add to the products table somthing like "related product id's", so it could suggest some products that are related, which would be useful immediatley, but meaning a little more data entry ... OR let the system do that by itself (i.e. People who bought this also bought....) which would take time for it to become useful (at least a few orders anyway).

The question still remains, however, how do I get rid of non-user-linked baskets and items when the user leaves? (other than having a onExit popup window, which I am certain will be blocked these days). Its gunna either be a chron job, or user executed (how unreliable!).

Hummm...

 

run a hidden I-frame with a meta refresh in it . At the refresh time have it run your clean up code.

how specific are you going to get? are you going to allow the administrator to add items by company, food type and food category? Might be a good way to track inventory too.

dude, I hate you right now because I could go into so much about this and then I'll want to build it and I don't have the time..lol..

 

Heheheh...

Well, right now I'm not sure how to organise the catagories. I'm not sure I'd even need a manufacturer/type or anything like that. Its for an interior design type shop. So you have a "walls" secion which has paint, paint effects ... then another for "windows"... that sorta thing. I dunno.

Maybe a type is a good idea. but then that would mean having a "prod_type" table as well... which has all the different types for each section. Company, probably not. I'm not sure it'd be useful. Then again, it might be. I'll keep that in mind when I talk to my client.

Hmm, a hidden i-frame. Cunning idea. So any user would accesses the site would clean up the DB for me. What if they navigate away before its executed though? Wouldnt that leave a load of half-removed baskets? Maybe I should have it look for orphaned basket contents first, then delete expired baskets.

 

you could always write a little win32 app to do this for you on a timed basis from a machine there. put it to you this way. Even if they navigate away, so long as you run it when they log in, you won't have a problem. SOMEONE will always run the i-frame at some point.

 

http://www.fest.se/vbulletin/images/smilies/11doh.gif
OH durr!! I just remembered! Theres gunna be an "order monitor" which refreshes every 5 minutes to check for new orders... why don't I just stick the code on there!