How Can I do That

[clemkonan]

Sorry but I really could not find an appropriate title. I need the capability in my database to look at a product and also see its specification. I could have 100 products , these could be broken into say 5 categories and there is sufficient commonality within a category that they can share a common specification.

How so? lets take 3 example Apple Juice, Ice Cream and Frozen vegetables.

For ice cream my spec includes: Sweetness, % fat, total solids, total bacteria , % overrun, Storage Temperature and Shelf Life.

For Apple Juice its: % sweetness, %acidity, Brix/Acid Ratio, Colour, storage temperature and Shelf life.

For frozen vegetables its : Colour , % foreign matter, Tenderometer, total bacteria, coliforms, storage temperature and shelf life.

How would I put together a table(s) to manage this or do I need a table at all? There is a tableproducts that carries the product names and product ID numbers.
Hope this makes sense.
Thanks for your help.

[NoeticCC]

Re: How Can I do That

Hi,

What you seem to be pondering on is usually referred to as "lookup tables" I think, i.e. tables used to store all varieties of a property that tend to recur regularly for items in other tables.

Which of the fields to create lookup tables for really depends on the kinds of values you expect, i.e. do you want to be able to enter any of a large range of values/numbers, or do you want to limit the user's selections to a set list of selections or ranges (of values for example)?

For example colour would be something you would probably want to define a set list of colours for in a lkup_colour table (can always add more but it is up to you what they can select), so you can run reports on it by colour rather than having to deal with different spellings and so on of what is really the same word.

For things like storage temperature or %fat etc, you probably need to be more flexible so you need to let the user enter a number of their choosing, and do any grouping by ranges of values in the individual reports (e.g. group by percentage of fat with increments of 10% or something).

[clemkonan]

Re: How Can I do That

Thanks, the range of values will be small, specific and similar albeit not identical within a category. So For Apple Juice:

% sweetness = 10.7 -11.3 , Acid = 5.2 , Colour =18-22, Temperature = 25- 30C, shelf life =18 months

You do not want the user to enter this information its a spec that you are controlling and issuing to a Supplier. They are making your product and you are providing the standards.

Your Sales people may be checking on a product or a customer may request a specification for apple juice then you would access this information

In the big scheme of things the database manages vendors , who they are , what they produce, product specifications, etc.

[Peter.Greulich]

Re: How Can I do That

Here's how I would do it: Products table & spec table. Make each one of your specs a field in the Specs table. Craete a 1:1 set using Products as the parent table.

[clemkonan]

Re: How Can I do That

Thanks.
Are you suggesting that if I have 100 products I would have a 100 spec tables one per product?

The product table is easy because each record allows me to add a new product and its still one table but because of the uniqueness of the specifications I would need 100 tables. Seems like a lot of work

[Peter.Greulich]

Re: How Can I do That

Thanks.
Are you suggesting that if I have 100 products I would have a 100 spec tables one per product?

Not at all. Just one spec table. Let's say that you have 100 different specifications in total for all of the various products. Make each spec be a field in your spec table. So now your Spec table ends up w. a 100 or so fields. Each product may only use a few of the spec fields, but they are all in each record; it's just that each spec record may have a lot of blank values for each related product.

[clemkonan]

Re: How Can I do That

Not at all. Just one spec table. Let's say that you have 100 different specifications in total for all of the various products. Make each spec be a field in your spec table. So now your Spec table ends up w. a 100 or so fields. Each product may only use a few of the spec fields, but they are all in each record; it's just that each spec record may have a lot of blank values for each related product.

Thanks I get it , I will try it out.