Database Design Suggestion

I am building a shopping cart system on my localserver for practice.

Now, I am using sessions to insert the product to the MySQL database, but I am creating a table for each of the sessions. I know this is not an ideal way to do things.

My question:

Should I define a number of rows in the sessions table, so that there will be a maximum number of items added to the cart?

So if I re design the table it would be

User INT NULL,
product_1 INT NULL,
product_2 INT NU开发者_StackOverflowLL,
...
....

I know that creating a table for each of the orders will only cause problems in the future

No, this design will be incredibly difficult to maintain. Instead, have one table that will hold all cart data; something like:

User    Product    Quantity
2       5          1
2       3          3
2       7          1
3       5          2
3       6          2

where the "user" column is a foreign key on the user table (or cart table, whichever), and the "product" column is a foreign key on the product table. That way there is no restriction on the number of unique products in the cart at one time.

It would probably be better to check number of items in cart before adding a new item to the database. If number of cart items is equal to the maximum, then don't allow another item to be added or row to be added to database. I don't think it is possible to set a maximum number of rows in an SQL table and if it is possible, it probably isn't good practice.

Why not have a single table which holds all cart items (for all users) and store a unique customer ID in the Session variable which references to a row in the SQL cart table.

Something like this...

Columns: CustomerID | ProductID | QTY

Row 1: 1293993933 | 393939399 | 3
Row 2: 1293993933 | 339933992 | 4

Store the customer ID in a session variable ($_SESSION['CustomerID'] if you're using PHP)

Do a select count SQL query each time a product is added to cart. If it equals maximum allowed cart items, don't allow user to run the insert into SQL command.

Hope this helps