Table structure (1 Viewer)

i am trying to get the table structure right before i get ahead of myself
my daughter makes custom mixed glitters ...yes glitters and of course she said Daddy can you help? So here I am

my objectives are to :

1. Be able to track Inventory in Production
2. Track inventory on hand
3. Track raw material usage



So my basic tables are

1. Raw materials that includes a list of all items used to make the product

2. Finished Product name & SKU

now here is where i get lost

When making a specific product it requires various raw materials

Examples Product :green bay contains 1 part green glitter 1 part gold glitter and

1 bottle
Product: Starburst contains 3 parts silver 1 part gold and 1 bottle

Should i build a table with the formula for each product? is there a way to build a drop down listing all raw materials to choose for the formula table?

Then a table with product production

Then my inventory

Then my orders table


Any suggestions before i try this would be greatly appreciated

Should i build a table with the formula for each product?

Click to expand...

Depends on how you are using 'formula'. What you would use is a junction table/ associative entity (https://en.wikipedia.org/wiki/Associative_entity) that would determine what product uses what raw materials in what quantity.

You have a Product table that holds all the product she makes. Each product gets its own autonumber primary key in a field called ProductID. You have a Material table that holds all the raw materials. Each gets its own autonumber primary key in a field called MaterialID. Now the junction table is called ProductMaterials and has this structure:

ProductMaterials
ProductMaterialsID, autonumber, primary key
ProductID, number, foreign key to Products.ProductID
MaterialID, number, foreign key to Materials.MaterialID
ProductMaterialsQuantity, number, holds how many of the material go into this product

That table stores your formulas essentially. So for your Starburst product you would have 3 records in that table (1 for each material it is comprised of).

You also want a formula table.
TProduct is the master table.
TFormula is a child of tProduct.

TProduct.Item. (Green bay)
Will have items in tFormula
Silver ,3
Gold, 1

The tOrder table will also have a child table, tOrderDetails.
Like an online shopping cart, the tOrder is the master record,
Joe smith
Order 1234
Date
ShipTo
BillTo

And the child table, tOrderDetails, has all items in the order:
Item, Qty, price,ExtPrice
Green Bay, 2, $1.25, $2.50
Etc.

Hey Thanks again when i built the table as you described everything came into focus

I highly recommend studying this: Inventory Control: Quantity on Hand