Shopping Cart Design Quesiton - Mailing list pgsql-general

From shawn everett
Subject Shopping Cart Design Quesiton
Date
Msg-id Pine.LNX.4.30.0103231553450.301-100000@alder.pgweb.com
Whole thread Raw
List pgsql-general
I'm developing a shopping cart using PHP and Postgres.  The existing
shopping cart stores items in a cart table which looks roughly as follows:

CREATE TABLE cart (
    SessId VARCHAR(32),
    ItemNumber INTEGER,
    Quantity INTEGER,
    PRIMARY KEY (SessId,ItemNumber)
);

This style works just fine for individual items that shoppers want to buy.

The problem is that I want to extend the application to computer systems
and give shoppers the ability to buy and customize those systems online.

I'm thinking of creating a SystemItems table that looks like:

CREATE TABLE SysItem (
    sysitemid SERIAL,
    system INTEGER,
    part VARCHAR(255),
    category INTEGER,
    parent INTEGER,
    PRIMARY KEY (sysitemid)
);

Basically each system is made up of several items and each item may have
several alternate choices (represented by the parent field).  The system
field is a FK back to the Item table and describes the computer system
itself.  Part is used to describe the items.  I may replace part with a FK
back to the Items table where there could be an entry for each item.

My problem occurs when users want to buy custom systems.  What would be a
good way to store this information in the database?

I'm thinking I'll need to store:
    - The Session ID (So I know who gets what)
    - The ItemNo for the basic System (So I know what they're buying)
    - The list of items for that system

To my somewhat untrained eye this means I need two tables, I find that a
little clunky.

Can anyone suggest a more elegant solution?

Shawn


pgsql-general by date:

Previous
From: "Christian Marschalek"
Date:
Subject: Database shutdown
Next
From: Stephen Davies
Date:
Subject: Null records inserted