Designing tables

[John G]

I want to design a database for a business that
manufactures UPVC windows and doors. My initial problem
is that each product (especially the windows)can be made
in various set sizes with set prices dependent on height
and width.

I know creating a separate table for each product is
ridiculous, epecially when products will also most
certainly be added or removed and the prices may change
over any period of time.

A more reasonable solution is to create one table with
each product's height and width matched with it's price,
but this also seems a bit much when lets say you there's
100 products with 100 different set sizes and costs.

I can accept that my thinking is probaly niave as I'm
fairly new to database design, so any ideas on how to
approach the problem would be appriciated.

thanks John

 

[Damon Heron]

John,
It seems to me that a window, say 30 x 40, and another window, 30 x 60, are
really two different products. But maybe the first solution is a unique id
for each product in a table like this:

Product ID
Product Name
Description
SizeID
PriceID

and another table with all possible sizes:

SizeID
Ht
Width

and a third table with price info:

PriceID
Price

So one product can have many sizes (one-to-many) and one product can have
many prices....

HTH
Damon

 

[Damon Heron]

Ooops! The table ids should be Product ID in Size table, product ID in Price
table! not the way I stated.

 

[Damon Heron]

The hits just keep comin'.... Only two tables are required, the product
table and the size table. Add a price field to the size table. On a form,
you can have the product, with a subform for various sizes and price of each
size...

Dumon

 

[Guest]

Thanks for that Damon, yes your correct a better way to
look at this is to assume that each variable size is
another product.

I am still unsure as to how this table design you have
proposed for me will relate the size of the product
(window) to the price? Considering the client would like
the system to up date the price automatically.

This design could work but wouldn't the user have to have
an external pricing scheme? or have I missed something

thanks agian John

 

[Damon Heron]

My posts were rather confusing because the first was done without thinking
about the product. My final table design would be like this (only two
tables):

Product table
ProductID
ProductName
Description

Size Table
SizeID
Height
Width
ProductID
Price

Establish a relationship in the rel. window - one product to many sizes.
Design a form with Products as the record source and on it a subform for
sizes - master/child relation is productID.
Now as to your question, "the client would like the system to up date the
price automatically" - are you talking about a standard across the board
price change for all product -for instance, a 10% price increase? If so,
that could be done with a query. If you are talking about price changes on
specific products, that also could be a parameter query, but someone,
sometime is going to have to input the prices for all products to start
with. I don't have enough info about the product pricing to tell you how I
would do it... Set up my tables and forms and play with that idea to get
you started.

Damon

 

[Guest]

Yes I believe that this design is probably the most
straight forward and avoids me getting into a muddle, it
still means that for every product style I will have to
manually input every height and width size and its
corresponding price.

Although I thought there was some merit in have a table
with all possible sizes (height and width) and giving
each one there own ID.

Tell me what you think of this

Product_Table
ProductID
ProductName
Description

Size_Table
SizeID
Height
Width

Price_Table
Price ID
Price

I could then have a fourth table made up of the other
three

Product_Link Table
Porduct ID
size ID
Price ID

Then my client would be able to add new products from a
form.

or does this all sound too much, its was you first
message that gave me the idea so please don't laugh out
too loud!

Thanks john

 

[Guest]

Yes I believe that this design is probably the most
straight forward and avoids me getting into a muddle, it
still means that for every product style I will have to
manually input every height and width size and its
corresponding price.

Although I thought there was some merit in have a table
with all possible sizes (height and width) and giving
each one there own ID.

Tell me what you think of this

Product_Table
ProductID
ProductName
Description

Size_Table
SizeID
Height
Width

Price_Table
Price ID
Price

I could then have a fourth table made up of the other
three

Product_Link Table
Porduct ID
size ID
Price ID

Then my client would be able to add new products from a
form.

or does this all sound too much, its was you first
message that gave me the idea so please don't laugh out
too loud!