using one table for all forms of stock (1 Viewer)

hi all

i think ive pretty much figured this out, however id like a little bit of input from people with more experience than I have in database design
In our company we have several different types of stock we keep track of (we blend cake mixes for people to buy and make at home, think betty crocker but allergen free and more delicious )

We have Ingredients and Packaging (which we more or less treat as one entity, theyre both stored in tblIngredients)
Then theres Products (which is stored in tblProducts), these are the packets of mix that are produced, they are linked to ingredients via tblRecipes (which is just a simple break table that contains a product id, an ingredient id and a percentage of the ingredient that is used in the product)
and finally theres Finished Goods, stored in tblFG, a finished good may have several products in it (like a cake mix and an icing mix for example), theres a break table in between finished goods and products called tblComponents, which is more or less the same as tblRecipes, to link FGs and products together and then theres also a break third table linking Finished Goods with Ingredients (tblBOM - Bill of Materials), again the same concept as tblComponents and tblRecipes, this is where we assign packaging used

as you can imagine all these tables are quite cumbersome and are confusing for anyone other than myself to keep track of (only reason it makes sense to me is because i designed it, all the other users in the office keep asking the difference between components and bill of materials and why they need to do both), as well as this we now have what we're calling composite goods, boxes that contain several of our finished goods and i honestly do not want to have to add two more tables just to handle that

im trying to simplify the whole process by just having tblStock, which has all the fields in tables Ingredients, Products and FG, but also has an extra field to differentiate what each item is (ie, whether its an ingredient or a product, or a finished good) and based on what a user selects the form their on will hide or disable the fields that arent relevant to it (ie only an ingredient would need a supplier field and only a finished good or composite good would need a sale price field, but all stock needs a name field)
then i would have only one break table (tblBOM) which will connect finished goods to products, products to ingredients and so forth

it seems simple to me but just wondering if anyone has any experience with this and if they can offer any insights to what im doing, does it make sense to do it this way or should i be looking into a different form of design?
are there any other suggestions you guys can think of that might help me in this?

Thank you,
Ben

I believe you are on the right track with combining into the one table.

The only comment I would make is the storing the "percentage" of the ingredient amounts to a normalization issue. It is really a matter of definition. All those percentages would have to add up to 100. If they don't then you will have conflicting information in the data. Potential for conflict always indicates a normalization error.

You should avoid assuming the quantities add up to 100 percent. When calculating the required quantities for a batch you should sum the values for all ingredients and divide by that to get the amounts of each ingredient.

This also has the advantage of not needing to adjust values of all the other ingredients if you add another one to the recipe.

I agree that a single items table is the way to go

I presume your finished goods are different from the raw materials.

ie a pack of cake mix for sale includes the box, the flour mix, the icing mix, and so on, and these constituents are no longer in the separate raw materials stock

this makes it fairly easy.

------
A more complex system is one for a business such as a plumbers type shop, selling both individual parts and packages of parts.

ie, you can sell an individual toilet or cistern
you can also sell a bathroom suite, consisting of a toilet, a cistern, a handle a bath and so on.
you can have a different suite, with the same toilet and cistern, but maybe different handles and different bath.
you can still do this with a single items table - but you need a separate table to manage the "parts explosion".

-----

edit. now I read again, this latter idea corresponds to your "composite goods".

one issue is how you manage a partial order. ie, someone orders 100 of a particular composite pack, but you can't do them all because you are short of part of one product. This get's you inti the complex area of fulfilling partial orders, and managing back orders.

Good luck!