Database Outline Design for Shopping cart

Question

Hello,

I'm looking for the best way to design a shopping cart. I'm at a crossroads with how to handle a product that has multiple options being color and size. The main issue being inventory management for the colors and sizes.

Currently on admin panel:

textfield for color textfield for quantity

User seperates colors by comma along with quantity so color & quantity match when I explode/implode them into arrays, thus allowing me to manage the quantity whenever something is purchased by their keys after doing an array search for the color.

Right now I just have one table holding upc/name/color/quanity/price etc...

Should I be using some type of foreign key and having tables for color/quantity on their own?

This is more of a design question and not a show me exactly how to do it question as I'm just trying to learn the most optimum way to manage a database.

Thanks!

Answer 1

Why not start by exploring how open source shopping carts have handled similar tasks. osCommerce is one that comes to mind.

Answer 2

Use 3NF

http://www.troubleshooters.com/littstip/ltnorm.html

http://en.wikipedia.org/wiki/Third%5Fnormal%5Fform

Answer 3

I'm not quit sure I understand your question but I think you are looking for suggestions on how to data model this and how to represent arrays of data from this model as strings that can be manipulated browser-side.

For the data model, it sounds like you need a 5 tables:

1) Product, 2) Color, 3) Size, 4) an associative table between Product and Color, 5) an associative table between Product and Size. Tables 4 and 5 implement the many-to-many relationships between products and the different colors offered, and between products and the different size offered.

Then you can settle on a standard way of representing shopping cart items as strings. Say:

<productId>,<qty>,<colorId>,<sizeId>

Arrays of these shopping cart items would be semicolon separated.

Looking for a way to represent the color and size choices for a particular product? I often use this type of query to retrieve choices as a comma-separated list which are then easy to deal with client-side:

-- =============================================
-- Author: Joe Blo
-- Create date: Jan 1, 2010
-- Description: Returns list of color choices for
-- a product in CSV format
-- =============================================
CREATE FUNCTION [dbo].[fn_GetProductColorsCSV] 
(
   @pProductUPC VARCHAR(30)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result VARCHAR(MAX)

 -- Add the T-SQL statements to compute the return value here
 SELECT  @Result = COALESCE(@Result + ',', '') + CAST(C.[ColorId] AS varchar) 
 FROM    dbo.[ProductColorJunction] PCJ
 INNER JOIN dbo.Color C ON C.[ColorId] = PCJ.Color
 WHERE   PCJ.ProductUPC = @pProductUPC

 -- Return the result of the function
 RETURN @Result
END