I'm trying to set up a MySQL backend to a Shopping Cart system. The program creates a random session ID and cookies it. What I need is some suggestions on the backend database.

I was thinking, do I create a table for each individual ID and then add each product the user selects into a new row in the table? Would this be the most efficent way?

By the way, if anyone's interested... I could actually be braking the law by creating my own shopping cart system. Did you know that Amazon have succefuly patented the idea? Check out http://www.noamazon.com. Crazy!

- wil

Aren't you doing this for a paying customer?....that site you keep linking to...can't remember the name.

No. I'm replacing a rather clumsy one on one of my sites. Why?

- wil

Surely you only need one table like ProductID, ProductName, Price etc...

If you aren't even planning on saving the data then you can make a shopping cart with just cookies.

No. That's dangerous. Ever visited a shopping cart just built in cookies? I could go in and edit the cookie file sent to my browser, and change the price of a product, just by reading a plain text file! Not the most secure solution when you think about it.

I will only use one table to hold data about the individual products. I just need to find a good way of storing all session information, like what they ahve ordered etc.

- wil

>>I could go in and edit the cookie file sent to my browser, and change the price of a product, just by reading a plain text file! <<

I'd like to see you do it with encrypted cookies

The algorithm used for encrypted cookies is still not that strong, although I couldn't begin to crack it personally.

But you do get my point? There's a lot of shopping cart systems on www.hotscripts.com who uses unencrypted cookies with this kind of setup.

- wil

>>The algorithm used for encrypted cookies <<

Huh? ....there isn't one alororithym...you can encrypt however you want.

Basically, you should step back and look at the relations between the products and customers...Ask yourself the following questions:

1) Can a customer order more than one product?
2) Can a customer order more than one quantity of a product?
3) How are you tracking the information? For what purpose?

Based on what you have stated so far, I think that you would have a M<->M connection between a Products and Customer table. Basically, this means that many customers can purchase many products and many products can be owned by customers.

The (ER) schema could like the following (without most extraneous attributes):

tbl_Customer

CustomerID (INT, PRIMARY, AUTO-INCREMENT, NOT NULL)

tbl_Products

ProductID (INT, PRIMARY, AUTO-INCREMENT, NOT NULL)

tbl_Orders


OrderID (INT, PRIMARY, AUTO-INCREMENT, NOT NULL)
CustomerID (INT, INDEX, NOT NULL, NOT UNIQUE)
ProductID (INT, INDEX, NOT NULL, NOT UNIQUE)
Quantity (SMALLINT, NOT NULL, NOT UNIQUE)


Now you could be more complex if you wanted to in terms of Product_Types (meaning that you can categorize the products into multiple categories, like Home Appliances, Electronics, etc.)...You would then add another M<->M connection between Products and Product_Type:

tbl_Products

ProductID (INT, PRIMARY, AUTO-INCREMENT, NOT NULL)

tbl_Product_Type

ProdTypeID (INT, PRIMARY, AUTO-INCREMENT, NOT NULL)

tbl_Product_Product_Type


ProductID (INT, INDEX, NOT UNIQUE)
ProdTypeID (INT, INDEX, NOT UNIQUE)


Hope this helps.
========================================
Buh Bye!

Cheers,
Me

Thank you, Eliot. That does help me a lot. I'm going to try and set up a quick database and I'll see how it goes.

Thanks

- wil

You're welcome.

Hope it works out.
========================================
Buh Bye!

Cheers,
Me

OK. I've got the following tables set up:


The only problem I see with this now is ordering multiple products. There is room in my orders table for multiple quantaties of the same product, but I think I'll have to write another row for each inidivudal product I want to add which could pose problems at a late stage.

Any suggestions on how to overcome this?

- wil

... which leads me back to thinking the answer is to create new tables on the fly for each session id. I wonder how this would work performance-wise with a heavy traffic site.

- wil

If you followed my original schema, you would not have this problem. Look again at the schema...

It is a many-to-many relationship between Customers and Products within the Orders table, meaning that Customers can order as many products as they like.

Using your randomid with no Customer table is what is flawed...and I would recommend scraping that idea.
========================================
Buh Bye!

Cheers,
Me

That violates relational systems and also not a good idea...How are you going to administrate 1,000's of customer tables?? Huh? Doing this will also cause normalization problems meaning that your users could have duplicate contact information across different "on-the-fly" customer tables that are created.

Again, scrap the randomid approach and use the above schema I provided and it will address your goals of this project.

The randomid approach you are attempting to execute will limit the power of customers to view their order history and also they will be assigned a new "randomid" each time they login, which could pose problems in the ordering process, meaning that they will have to input all their contact information again...which is cumbersome and not user-friendly.

========================================
Buh Bye!

Cheers,
Me

Well, the reason behind the random ID, is that it creates a random ID and assigns it to a cookie that lasts maybe an hour.

Once the order has been sent using the sent information all information about the user is scrapped off the system. That's the logic behind it anyway.

I don't actually need order tracking and history because once the order has been sent it will be dealt with by a human only via telephone and email.

The other reason behind the random code was to make it difficult for others to guess your shopping cart ID. If the field was AUTO_INCR. then surely this would make it easier for people to view your basket and your personal data?

- wil

The security issues you've raised can be addressed through a login process by assigning a USERNAME and PASSWORD attributes to the Customer table. Yea, logins can pose a problem, but look at all the big boys and how they handle order processing. Simple matter of may be using Email Address and Password to verify customers rather than assigning another column of Username.

About the order processing, you may not care about viewing order histories, but I betcha that your customers will. The customer service rep can update columns in the tbl_Orders table to indicate when the product was ordered, credit card info, of course, should be kept off-line.

Again, I think that the flaw within the system you are creating is deleting out customer info everytime they order a product...this is highly cumbersome for the customer...look at it from the customer's point-of-view.
========================================
Buh Bye!

Cheers,
Me

I hear what you're saying. But the product this is built for, is basicaly a one off yearly product. However, I should model my database better, it's good practive and allows for growth and improvement.

I'll have to sit down and think about this one. Makes me want to switch back to flat-file databases sometimes. <g>.

Actually, I still can't find one shopping cart system on the web that uses a SQL database as it's backend.

- wil

>>Actually, I still can't find one shopping cart system on the web that uses a SQL database as it's backend.<<

Because most are free and written by beginner-intermediate programmers

Thats why there is a demand for GT to write a decent one.

Flat file?? Pattuie! Spit! *cough*

There is a learning curve with SQL-relational systems, but once you get the hang of it, you'll never regret staying away from flat file systems.

<soapbox>
Coding is a lot easier in SQL-based systems than flat files, plus you don't have to worry about file corruption (like you do in flat file systems) and administrating a bunch of files. In addition, you can intuitively analyze your data within a relational system (if properly set-up) rather than writing a bunch of code to view multiple flat files that may not even relate to one another...yes, relational systems can be built with flat files, but highly intensive on server resources and also a lot of risk in corrupting the data.
</soapbox>

For the "year subscription" function you want to implement, how about adding an attribute within the Order table that will trigger an email notification to the customer (by looking up the Customer by the FK of CustomerID in the Order table). You can also add additional columns to control the length that a product is in use by the customer.
========================================
Buh Bye!

Cheers,
Me

From the sounds of it, Wil is talking about the process of adding items to your cart, and Heckler is mainly talking about the completed orders. My take on these things is that you'll need a session table which keeps the product id's and the quantities that the customer wants. You differentiate each customer by storing their id in a cookie (or in url) and in the session table. Only after they've completed their order does it go into the orders table.

So you'll also want to add: Sessions with a session_id, session_date (or expiry), and session_data.


Adrian

Thanks, Adrian. That's the route I'm actually going down as we speak. I'm leaving out after-sales 'support' and tracking for the time being.

- wil

Yes...that is true...but setting up the transaction part of the process is essential in order to understand what actually would go into the shopping cart.

Research has shown that the login process at the point of check-out is much better than prior to "shopping", but if you did want to have people login first, you could put the CustomerID in the Sessions table as well, so that on the point on "checking out", you can reference both the Products and Customers tables to "auto-fill" fields, so that the checkout could essentially be one click, with the exception of adding credit card information into the form, which should not be stored in your database anyway.
========================================
Buh Bye!

Cheers,
Me