Not sure about this design concept

One Quote has many Sections (factory floor space) One Section has many Ingredients (floor surfacing materials) An Ingredient is a Material (Primer, Resin etc) A Material has many Container Sizes (20L _at_ $20, 30L @ $25 etc)

So Quotes 1-m Sections 1-m Ingredients
Materials 1-m Ingredients
Materials 1-m Containers

tblQuotes(QuoteID<pk>, Date etc)
tblSections(SectionID<pk>, QuoteID<fk>, Area etc) tblIngredients(IngredientID, SectionID<fk>, MaterialID<fk>, Rate etc) tblMaterials(MaterialID<pk>, MatName etc) tblContainers(ContainerID<pk>, MaterialID<fk>, Size etc)

So the same Material can be used in various Sections (also can be used more than once in the same Section).
From the Ingredients I can query the Quantity of each Ingredient in each section for the quote.

Now for the problem: I don't want to have to keep entering the Container Size for every Ingredient. I'd prefer to enter it once for the quote. So I guess it shouldn't be a property of every Ingredient in every Section. Instead I made a table to act as a storer of Container Size for each Material in each Quote.
tblIngredientContainers(QuoteID<fk>, MaterialID<fk>, ContainerID<fk>) I would then have to check programmatically (probably use an outer join query with tblIngredients to help) to make sure sizes have been assigned to all (unique) materials in the quote.
The wierd thing with this concept is that by specifying the ContainerID, this *implies* a particular Material due to the 1-m relationship between tblMaterials and tblContainers. So does this mean that I only need this the following? tblIngredientContainers(QuoteID<fk>, ContainerID<fk>) It just seems strange to do this.

Any comments?
Graham Received on Tue Jul 30 2002 - 15:12:56 CEST