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
