MySQL Shopping Cart Database Schema Design

MySQLPHPschema

I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry time stamp, a boolean indicating whether or not the purchase was completed, and then a list of products, and their associated quantities and prices. Every user will have only one ID, origination time stamp, expiry time stamp, and purchase boolean, but they may reference one or many products with varying quantities and prices.

My initial thought was to have a session table and a product table, but I can't seem to get past thinking the session ID and the product information will become redundant in big orders and across different sessions. In short, if different rows of the product table consistently reference repeat product numbers and session IDs, is that going to get messy?

Am I on the right track? Or can you propose an alternative design pattern that I have yet to consider?

Please let me know if you need more information. Thank you in advance!

Best Answer

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.

Related Question