Product table normalization

[devYW]

Hi there,

I need to normalize the following structure:

Wall covering wood (category)

(Product)-----(Models)-----(Prices)(Measurement)
Log Cabin-----5/4 x 6------1.85$/square foot-----0.80$/linear foot
Log Cabin-----2 x 8--------3.00$/square foot-----1.50$/linear foot
Log Cabin-----2 1/2 x 8----6.50$/square foot-----3.50$/linear foot

Doors (category)

(Product)------(Models)-----(Prices)
Panel Door------18"----------85$
Panel Door------24"----------90$
Panel Door------26"----------95$
Panel Door------28"----------100$
Panel Door------30"----------105$

Tables (category)

(Product)-------(Models)--------Not peinted-----Waxed----Lacquered
rustic table-----48 x 35 x 30----600$------------700$-----800$
rustic table-----60 x 35 x 30----700$------------800$-----900$
rustic table-----72 x 35 x 30----800$------------900$-----950$

Options---------------------------Not peinted-----Waxed---Lacquered
turned legs-----------------------50$-------------60$-----70$
1 Drawer--------------------------80$-------------85$-----90$
2 Drawer--------------------------100$------------105$----110$
Dyes(Walnut,Chocolat,Mahogany)--------------------50$-----60$

A Product belongs to a Category
A Product has many Models
A Model has many Prices
A Price has a Measurement (Square Foot, Linear Foot, Unit)

My problem is with the table category.
A Model has and belongs to many Options

I'm not sure how to approach the "Not peinted, Waxed and Lacquered" prices because the Model itself can be "Not peinted, Waxed and Lacquered" and the options can be "Not peinted, Waxed and Lacquered" as well.

If I'm clear enough and you can help me normalize this structure please let me know.

Thanks

[bigakis]

In the following schema:
*=Primary key

value "0" on "." or whatever you like, but the same everywhere, will be used as "No value"
This is because primary key columns are required.
The schema:

category
*category_code
category_name

product
*product_code
product_name
product_category
product_or_option
(Options are stored in product table in order to use the implemented functionality.
when a customer asks for an option it is like ordering another product
We can create another table to assign products to options in order to validate allowed options
)

product_model
*product_code
*model_code
model_name

coating
*coating_code
coating_name

product_coatings
*product_code
*coating_code

measurement
*meas_code
meas_name

pricelist
*product_code
*model_code
*meas_code
*coating_code
price

The data above mapped to this schema in the same column order.

Category
WOOD ---- Wall covering wood
DOORS ---- Doors
TABLES ---- Tables
OPT1 ---- Option Category #1

product
LOGCAB --- Log Cabin ---- WOOD ---- P
DOOR --- Panel Door ---- DOORS ---- P
RUSTTBL --- Rustic Table ---- TABLES ---- P
TURNLEG --- Turned Legs ---- OPT1 ---- O
1DR --- 1 Drawer ---- OPT1 ---- O
2DR --- 2 Drawers ---- OPT1 ---- O
DYES ---- Dyes(...) ---- OPT1 ---- O

Coating
. ---- No Coating
NOTP ---- Not painted
WAX ---- Waxed
LACQ ---- Lacquered

measurement
PCS ---- Piece
SQF ---- Square foot
LIF ---- Linear Foot

product_model
LOGCAB ---- 5/4X6 ---- Log Cabin 5x4/6
LOGCAB ---- 2x8 ---- Log Cabin 2x8
LOGCAB ---- 2-1/2X8 ---- Log Cabin 2 1/2x8
DOOR ---- 18 ---- Door 18''
DOOR ---- 24 ---- Door 24''
DOOR ---- 26 ---- Door 26''
DOOR ---- 28 ---- Door 28''
DOOR ---- 30 ---- Door 30''
RUSTTBL ---- 48x35x30 ---- Rustic Table 48x35x30
RUSTTBL ---- 60x35x30 ---- Rustic Table 60x35x30
RUSTTBL ---- 72x35x30 ---- Rustic Table 72x35x30
TURNLEG ---- . ---- Turned Legs
1DR ---- . ---- 1 Drawer
2DR ---- . ---- 2 Drawers
DYES ---- . ---- Dyes(...)

product_coatings
LOGCAB ---- .
DOOR ---- .
RUSTTBL ---- NOTP
RUSTTBL ---- LACQ
RUSTTBL ---- WAX
1DR ---- NOTP
1DR ---- LACQ
1DR ---- WAX
2DR ---- NOTP
2DR ---- LACQ
2DR ---- WAX
DYES ---- NOTP
DYES ---- LACQ
DYES ---- WAX

pricelist
LOGCAB ---- 5/4x6 ----- SQF ----- . ---- 1.85
LOGCAB ---- 5/4x6 ----- LIF ----- . ---- 0.80
LOGCAB ---- 2x8 ----- SQF ----- . ---- 3.00
LOGCAB ---- 2x8 ----- LIF ----- . ---- 1.50
....
....
DOOR ---- 18 ----- PCS ----- . ---- 85
DOOR ---- 24 ----- PCS ----- . ---- 90
....
....
RUSTTBL ---- 48x35x30 ----- PCS ----- NP ---- 600
RUSTTBL ---- 48x35x30 ----- PCS ----- WAX ---- 700
RUSTTBL ---- 48x35x30 ----- PCS ----- LACQ ---- 800
....
....
1DR ---- . ----- PCS ----- NP ---- 50
1DR ---- . ----- PCS ----- WAX ---- 60
1DR ---- . ----- PCS ----- LACQ ---- 70
....
....

there are many tricks to reduce the number of rows required on the pricelist table, but it beyond normalization
and requires coding (sql functions or complex views).

Akis

[devYW]

That will work.

Thanks for your help.