coldfingertips has asked for the wisdom of the Perl Monks concerning the following question:

I am still new with MySQL and seek advice from the monks of PM.

I am creating a mini shopping cart and I need some help to make sure I am getting the tables correct. There are 4 tables.

--------- --------- |products| | temp | --------- --------- id id name zip desc email price userid weight shiprice shipinfo subtotal total coupon ip ----------- ----------- |temporder| |order | ----------- ----------- id id ??? userid subtotal total shipcost shipmethod name, address, zip, phone, etc..
I have one table for all the products. One temp table which puts their CURRENT order into while they are surfing the site. The order table pulls the information out of the temp table once the order was verified via PayPal's IPN. The temporder table I have no idea what to do with.

I am lost as to how to add all the user's products and qty's to a table without making a new table for each person. That seems very, very costly but I don't know any other way to do it. I need the current items and quantities in both the temporder and the order table (or somehow related so I can pull it back later).

I could in reality make an order table and make duplicate USERID rows and have items/quantities that way and just search for rows that have the USERID in question. This means more reasonable than a new table but depending on the database size, could be costly as well.

Does anyone know of a simple way to add their items/qty's to the table(s)?

Replies are listed 'Best First'.
Re: settuping up pseudo tables in mysql shopping cart
by merlyn (Sage) on Nov 28, 2005 at 19:37 UTC
    Perhaps your problem is understanding database normalization.

    The minimal structure for a shopping cart is that the Order table holds everything that exists only once for an Order (person, shipping address, method of payment, etc), and a unique Order ID. The Order Items table holds each of the items (item ID, quantity, color, etc), as well as the ID of the order to which it belongs.

    To add an item, simply insert it into the Order Items table with the proper Order ID for the current Order. To check out, select all items in the Order Items table where Order ID is your current Order ID.

    You definitely don't need "a table per person"!

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

      Are you saying to have a table set up like this?
      id unique userid itemname itemnum qty price itemweight 307 af4jsdfds frog 34 4 2.50 4.5 308 8843kksd bear 12 2 8.00 5.0 309 af4jsdfds bear 5 3 8.00 5.0
      And then when I need the information for a userid, I look up every column containing it?

        No. Create a table for each unique type of entity in your system: a Customer, perhaps an Address, an Item for sale, and an Order. Then create a table for each one to many relationship in the system. An Order contains many Items, so you need an Order_Items table.

        Then to add an order, find the Customer's id (creating a Customer or using an existing one). Create a new Order. Add a row to the Order_Items table for each Item in the Order.

        Every unique entity in the system should occur only once in the system. That's basically the normalization rule to go by.