Products with Many Sub-options

[sunny8760]

Hi Everyone,

I need your advice on table design.

My company has a price catalogue with about 15 products. It is only available in an awkwardly formatted excel or PDF file format. I would like to add this to an Access table and query what I need.

Each product may have 1 to 15 sub-options. These sub-options are listed using a numbering system which relates to a model code and price. For example:

Product Category: DM7
Sub-options: 1/2/5/2/6/8/9/2/0/1/0 etc.

Based on the sub-option selected, the price and model code change.

As each product may have 1-15 sub-options my thought is that if I include all these products into one table I would not comply with normalization, as some product codes are not as long as others i.e. do not have the same type or number of sub-options.

What do you recommend?

Thanks

 

[plog]

Always normalized tables. Just because your data feed doesn't come that way, doesn't mean you shouldn't store it that way.

Most likely you will just need to create an import process that converts the feed data to the proper structure. A little work on the front end makes life a lot easier down the road.

 

[sunny8760]

Always normalized tables. Just because your data feed doesn't come that way, doesn't mean you shouldn't store it that way.

Most likely you will just need to create an import process that converts the feed data to the proper structure. A little work on the front end makes life a lot easier down the road.

Thanks for the input.

In that case, would it be against normalization to have tables that store basic product details and other joining tables that have sub-option data?

 

[plog]

Not really.

Products
prod_ID, autonumber, primary key
prod_Desc, text, product description
prod_Cost, number, cost of product

Options
opt_ID, autonumber, primary key
ID_prod, number, foreign key to Products
opt_Description, text, description of option
opt_Cost, number, cost of option