Design Problem: Products Pricing Affected by Optional Extras

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 (
code 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.

.

Relevant Pages

  • Re: Design Problem: Products Pricing Affected by Optional Extras
    ... frame options ... Some products do not have any options, some have a different price ... but with a white frame and blue glass costs ... PRIMARY KEY ...
    (comp.databases)
  • Re: Sony A100 to A700
    ... Perhaps I'm optimistic about the $1000 price for a full frame body this fall, but it is just a matter of time before the entry level models come out with it. ... The Sony a900 and Nikon D3x are 99% certainty in name and in sensor configuration. ... I guess I'm underwhelmed by the concept of Fx - particularly high resolution Fx - unless it also comes with the feature set that helps to get the shot. ...
    (rec.photo.digital)
  • Re: Sony A100 to A700
    ... Perhaps I'm optimistic about the $1000 price for a full frame body this fall, but it is just a matter of time before the entry level models come out with it. ... The Sony a900 and Nikon D3x are 99% certainty in name and in sensor configuration. ... I guess it could be the lowest price full-frame dslr, but it either isn't going to be 20+mp, or it isn't going to be featured to compete with the pro-level cameras. ...
    (rec.photo.digital)
  • Re: Help me designing that please
    ... CREATE TABLE Orders (order_num INTEGER PRIMARY KEY, ... JOIN DishOrders AS DO ... Drop the price from the Dishes table and keep a separate Price History ... CREATE TABLE PriceHistory (dish_num INTEGER NOT NULL REFERENCES Dishes ...
    (microsoft.public.sqlserver.programming)
  • Re: WTB: Old Steel Frame 52 - 54 cm
    ... hubs on GP4 tubs, Galli calipers, Ultegra cranks, 1" quill stem, etc. ... If you just want the frame, you can have it for $150. ... Paul Hobson Wrote: ... I'll be glad to talk to you about a more appropriate price. ...
    (rec.bicycles.marketplace)