Mysql – Shopping cart database, should order object have a relationship with the product, or should I duplicate the product information

I am not a database designer.

You don't make dbs but tables.

Both of your table structures can be implemented as innodb tables and as MyIsam tables.

Your first idea is not a good design. It violates first normal form, so i would propose the second idea with innodb. the table db1 can be decomposed further in two tables

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | 1
2  | some name | some details | 1333128355 | 1
3  | some name | some details | 1333128357 | 2

id | brand
1  | nike
2  | puma

From your description, you'll need the following tables ("..." means you might need additional fields in that entity):

• User (UserID, EmailAddress, Password ...)
• Session (SessionID, CreatedDate, UserID, ExpiryDate, PurchaseCompleteInd ...)
• Product (ProductID, Name, UnitPrice ...)
• SessionProductLink (ProductID, SessionID, Quantity ...)

The SessionProductLink is a Junction table, which is the 'bit of the design' you were missing.

The field names in italics are foreign keys (it should be obvious which fields they link to ...). the ID fields are the clustered key fields in the first three entities, for SessionProductLink you'd want a compound key of (ProductID, SessionID). (You can use a surrogate key there if you want, that's a style decision you can make)

Other things you might want to think about:

• Security. You want to store your user passwords securely encrypted (i.e. use bcrypt), if you support credit cards you legally are not allowed to store the full credit card details in most cases, etc.
• Invoicing - which payment methods are you going to support? Do you want people to be able to pay for multiple 'sessions' with one invoice? (This is more common for business accounts, typically retail customers are one purchase = one invoice = one payment method) Do you want to accept foreign currencies? (Between this point and the first one, you'll almost need a payment processor to make sure you don't land yourself in massive strife ...)
• What happens if a product price changes? You don't want your past purchases/invoice prices to change, right?
• You'll need to store your sessions (or the completed ones at least) forever (depending on your country's legal requirements for document storage for commercial transactions), since they're a legal record. Similarly, you can't just delete old Product records, you need to mark them obsolete so they don't show up as available for purchasing but do show up in historical purchase records (otherwise your app will show for old items that someone purchased 5 of product ID 119 and 2 of product ID 135 and you don't know what the hell they actually bought...).

That's all I can think of right now, but that should be enough to get you started.