How to store information in a field in Database?

Ok so I am currently building a simple shopping cart for my website and have researched this question, but have been unsuccessful at finding an answer.

I have these tables so far:

Item
------
id
categoryID
brandID
name
price



Categories
-----------
id
name



Brands
-------
id
name



Shopping Carts
--------------
id
userID
itemIDs
itemPrices
itemQuantities

Based on the other answers I have found this seems to be the correct way, except I am not sure about the Shopping Carts table. I was considering storing the information in this table like so:

id: 1
userID: 1
itemIDs: 1;2;3;5
itemPrices: 40;50;60;70
itemQuantities: 1;2;3;4

Is this the correct way of tackling this or is the开发者_如何学Gore a better way? (I was also thinking of using the explode() function in PHP for separating the data when I will be retrieving it.)

Ideally, you want to store shopping cart info either in cookies or in sessions. If you want to store each shopping cart in your DB, you could use the following implementation.

Essentially, one shopping cart can have multiple items, with each item having a price and quantity associated with it.

For storing carts per user, use this schema:

Shopping Carts

cartID (primary key)
sessionID (unique ID identifying the user session in which the cart was created)
purchased (Boolean TRUE/FALSE to store whether cart was purchased or discarded)

Shopping Cart Items

id (primary key)
cartID (unique ID identifying the user cart)
userID: (use only if you know that the user will always be logged in while adding items to the cart)
itemID: Unique identifier for an item in the cart.
itemQuantity: Quantity of the item ordered.
itemPrice: (optional - use only if the item price is customizable, or pull it from the item table)

With the above schema, if a user adds 2 items to your cart, lets say

• item X (quantity= 2pcs, itemID = 1001) &
• item Y(quantity= 1pc, itemID = 1002).

Your records in the table would look like:

id: 1
sessionID: abc1
itemID: 1001
itemQuantity: 2

id: 2
sessionID: abc1
itemID: 1002
itemQuantity: 1

Now, when you want to fetch the order for a shopping cart - just query based on the sessionID that was in use when the user was creating the cart.

No, that is normally not a good idea. What you are doing here is multiple values in one field and that is no good in case you ever want to query that field.

And for your pressing need, add a new table like this:

CartContent
-----------
cartId
itemId
itemCount

Also see http://en.wikipedia.org/wiki/Database_normalization for a good primer on database normalization.

You shouldn't use semicolon separated values in columns because you wont be able to join them to their respective foreign tables while doing DB queries. It is better to store them as separate line items and have a header table that aggregates these line items/