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

Hi - This sounds like a pretty big project that may be better suited to an actual full blown application rather than an access database. If you want to, you can look at a software package called windowmaker www.windowmaker.com - it does what you want and more (for a price) and all the application data can be manipulated with access or SQL depending on user numbers. If not then let me know more specifically what you want to do and i'll think about a simple dbase.

William

I'm hoping that I can create this system using Access as I have had a little experience with this application, A full blown application seems a little out of my league at this moment in time. I also have a good grounding in VBA which I hope will aid in the development of the system.

Perhaps I have over stated the the size of the application I am trying to develope, as the client I'm developing this for is a single user on a stand-alone PC. at present My client has around 50 products (window styles) all of which can have anything from 20 - 100 variable sizes.

At present I am toying with the idea of have two tables to deal with the windows problem.

So...

One table to deal with the the product styles

PRODUCTS

Product_ID (PRIMARY KEY)
Prod_Name
Prod_Description
etc...

Then

A table to deal with the Various sizes of the products

PRODUCT_SIZES

Size_ID (Primary Key)
Product_ID (Foreign KEY)
Height
Width
Price
etc...

A simple solution, my only worry is that my client will have the laborious task of inputting all the various sizes for each product manually every time a new product style is required.

Another solution I have pondered is to have four tables

Hence

PRODUCTS (The same as before)

PRODUCT_SIZE (With all possible variable sizes)
Size _ID
Height
Width
etc...

PRODUCT_PRICE
Price_id
price
etc...

And a final table to link the other three tables

So

PRODUCT_LINK
Product_link_ID (PK)
product_ID (FK)
Size_ID (FK)
Price_ID (FK)

Now the user can update a new product and its various sizes with a specifically designed form.

or do you think this is a bit too much

Please let me know what you think

Thanks again John