Re: Design Problem: Products Pricing Affected by Optional Extras
- From: "David Cressey" <cressey73@xxxxxxxxxxx>
- Date: Mon, 15 Oct 2007 12:38:21 GMT
"ijoxley" <ijoxley@xxxxxxxxxxxxxx> wrote in message
news:1192447983.340390.137010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am struggling to come up with a design for a products database and
would appreciate any help.
The client has a number of products, each with different options: the
problem is that the product's price
is affected by which options and in some cases the combination of
options.
For example, a product can have the following attributes:
code
handing (left-handed, right-handed or both)
width
length
height
glass options
frame options
Some products do not have any options, some have a different price
depending on the frame selected, and
others have a different price depending on the frame and colour
selected e.g. Product A with a silver frame costs £600.00, with a
white frame costs £700.00, but with a white frame and blue glass costs
£750.00.
So far I have come up with a table structure something like this:
CREATE TABLE products (
code char(10) primary key,
name varchar(20) not null,
width int not null,
length int default 0,
height int default 0,
notes varchar(255),
price decimal (7, 2)
)
CREATE TABLE glass_options (
code char(10) NOT NULL,
colour char(10) NOT NULL,
notes varchar(255)
PRIMARY KEY (code, colour)
FOREIGN KEY (code) REFERENCES products.code ON DELETE CASCADE ON
UPDATE CASCADE
)
CREATE TABLE frame_options (
0code char(10) NOT NULL,
colour char(10) NOT NULL,
notes varchar(255),
price decimal (7, 2) NOT NULL
PRIMARY KEY (code, colour)
FOREIGN KEY (code) REFERENCES products.code ON DELETE CASCADE ON
UPDATE CASCADE
)
Each product can have 0 or more glass options.
Each product can have 0 or more frame options.
The fact that price is in both the products table and the
frame_options table worries me as it seems less than ideal, but if a
product has neither glass or frame options then it still needs a price
so I cannot think of another way around this at the moment. I was
thinking of removing the relationship between glass_options and
products and instead making glass_options related to frame_options,
but then that could introduce a third price attribute in the
glass_options table which just does not seem right either.
Any help here would be much appreciated.
Response:
Do these products have a Universal Product Code (UPC)? If they do, can
one product with one UPC carry multiple prices depending on glass and
frame options, or does each unique configuration get its own UPC?
If the answer is the latter, one design might be to include a separate row
in the product table for each UPC, and put the price in the product table,
as you might do with any other product table. You then might need some kind
of table of what I'll call "product groups" that has one entry for each
product, as your current product table does.
.
- Follow-Ups:
- References:
- Prev by Date: Design Problem: Products Pricing Affected by Optional Extras
- Next by Date: Re: Design Problem: Products Pricing Affected by Optional Extras
- Previous by thread: Design Problem: Products Pricing Affected by Optional Extras
- Next by thread: Re: Design Problem: Products Pricing Affected by Optional Extras
- Index(es):
Relevant Pages
|
