Recipe/order/stock database.

[kragen2179]

Hey, my wife just started a new home business selling soups, and I want to make her a database to record her orders.

I am going to use a windows forms application and create an object based database from within Visual Studio 2015. Right now I'm working out what tables I need...

The application should hopefully allow her to keep track of ingredients used as orders are filled. I'm thinking I need the following tables...

TblIngredients (list of ingredients)
TblStock (quantities of ingredients)
TblRecipes (amounts of each ingredient)
TblProducts (there will be a multipack product so want to be able to add recipes to a ProductId to save having to always add individually)

TblCustomers (customer details)
Tble Orders (order details)

The hope is that when a product is added to an order, the system will reduce the held stock by the amount of ingredients used.

Do I have all the tables I need? Do I need to add/remove some?

[GaryMazzone]

Sounds OK

[Zvoni]

Agree, sounds OK, with one addition/suggestion:
I'm not sure you need a separate Stock-Table, since i doubt you need backtracing-capability and/or you have different stock-locations for one given ingredient (because of FIFO-principle).
In that case an additional column "StockQty" in tbl_ingredients" should be sufficient to keep your stock-balance for an ingredient.

Don't get me wrong:
A separate stock-table makes sense, especially when dealing with perishable items that have a Date of Expiry, but in that case you would have to keep track, when which ingredient was purchased by your wife, so to use the "oldest" stock first, when placing/preparing a client-order. And the easiest way to do that, is to keep different stock-locations for one and the same ingredient, separated by "Date of purchase" or in case the ingredients itself have an DoE on them.

[ChrisE]

Agree, sounds OK, with one addition/suggestion:
I'm not sure you need a separate Stock-Table, since i doubt you need backtracing-capability and/or you have different stock-locations for one given ingredient (because of FIFO-principle).
In that case an additional column "StockQty" in tbl_ingredients" should be sufficient to keep your stock-balance for an ingredient.

Don't get me wrong:
A separate stock-table makes sense, especially when dealing with perishable items that have a Date of Expiry, but in that case you would have to keep track, when which ingredient was purchased by your wife, so to use the "oldest" stock first, when placing/preparing a client-order. And the easiest way to do that, is to keep different stock-locations for one and the same ingredient, separated by "Date of purchase" or in case the ingredients itself have an DoE on them.

I think when Food is involved there should be FIFO in place

so good advice from Zvoni

[Zvoni]

To expand on that (i'm working for a trading company, for which FIFO is a basic tenet for everybody working there):

To give you an idea how it's done in my company (very abstract of course!):
we have a table "Stock Locations" that looks kind of like this
ID
Designation
SomeMoreFields
and a sample entry looks like this:
ID - Designation
"123" - "0H-35-05" (read as coordinates)

Then we have a table "Stock Matrix" which basically serves as an "m:m" connecting table between Stock Locations and Products (or in your case "Ingredients")
ID
IngredientID - Foreign Key to Ingredient
LocationID - Foreign Key to Stock Locations
Quantity
DateOfPurchase
DateOfExpiry
LotNo
SomeMoreFields

I think you can see where i'm going with this.
This is a setup for when you allow multiple, different (!) Items/Ingredients to be stocked at the same location (to save space).
Of course, you run the danger when preparing a client order/recipe, that you "mis"-grab the pepper instead of the salt

If you decide: "No, one stock Location=one Ingredient" then you wouldn't need a "m:m"-table but could directly implement it in the table "Stock Locations" like this:
Table "Stock Location"
ID
Designation
IngredientID
Quantity
DoP
DoE
SomeMoreFields

It all depends how you want to set up that shop of your wife.

EDIT: On a sidenote regards your TblProducts containing multiple TblRecipes (Multipack-Thing):
In that case, never ever sell Recipes directly!
Your worst case would be a "1:1"-relation between TblProducts and TblRecipes
IOW: A Product always has at least one Recipe, and a Recipe must be part of a Product and cannot be sold directly (as in: directly entering a recipe in the orders-table).
You would need a "Matrix"-table between Products and Recipes
Table ProdRecipe
ID
ProductID
RecipeID
SomeMoreFields

Your offered Products (TblProducts) would have entries like
"345" - "Tomato-Soup"
"567" - "X-Mas Multipack"

ID "345" would have a virtual "1:1" relation with the Recipe "Tomato-Soup" (interrelated through Table ProdRecipe)
ID "567" would have a "1:m" relation with many recipes as we're used to in an "1:m"-scenario (and the Tomato-Soup-Recipe (!) can appear again here!)

[kragen2179]

Thank you for the constructive feedback. I like the suggestion of integrating FIFO. I have been looking at my tables and trying to get it all orgainised and this is what I have come up with...

Table to store customer information

CREATE TABLE [dbo].[TblCustomers] (
    [CustID]       VARCHAR (6)  NOT NULL,
    [CustFName]    VARCHAR (20) NOT NULL,
    [CusLName]     VARCHAR (20) NULL,
    [CustAddress1] VARCHAR (50) NULL,
    [CustAddress2] VARCHAR (50) NULL,
    [CustCity]     VARCHAR (20) NULL,
    [CustProvince] VARCHAR (20) NULL,
    [CustEmail]    VARCHAR (75) NULL,
    [CustPhone]    NCHAR (10)   NULL,
    PRIMARY KEY CLUSTERED ([CustID] ASC)
);

Table to create order number and associate with customer (one to many) [one customer can be associated with many orders, but each order can only be associated with one customer]

CREATE TABLE [dbo].[TblOrders] (
    [OrderID]   INT         NOT NULL,
    [CustID]    VARCHAR (6) NOT NULL,
    [OrderDate] DATE        NOT NULL, 
    PRIMARY KEY CLUSTERED ([OrderId] ASC), 
    CONSTRAINT [FK_TblOrders_TblCustomers] FOREIGN KEY ([CustID]) REFERENCES [TblCustomers]([CustID])
);

Table to associate an order with one or more product (many to many) [one order can be associated with many products, likewise each product can be associated with many orders]

CREATE TABLE [dbo].[TblOdersProducts] (
    [OrderID]   INT         NOT NULL,
    [ProductID] VARCHAR (6) NOT NULL, 
    CONSTRAINT [FK_TblOdersProducts_TblOrders] FOREIGN KEY ([OrderID]) REFERENCES [TblOrders]([OrderID]), 
    CONSTRAINT [FK_TblOdersProducts_TblProducts] FOREIGN KEY ([ProductID]) REFERENCES [TblProducts]([ProductID])
);

Table to identify products for sale

CREATE TABLE [dbo].[TblProducts] (
    [ProcuctID]   VARCHAR (6)   NOT NULL,
    [ProductName] VARCHAR (30)  NOT NULL,
    [ProductCost] MONEY         NOT NULL,
    PRIMARY KEY CLUSTERED ([ProcuctID] ASC)
);

Table to associate a product with one or more recipes (many to many)[one product can be associated with many recipes, likewise each recipe can be featured in many products]

CREATE TABLE [dbo].[TblProductsRecipes] (
    [ProductID] VARCHAR (6) NOT NULL,
    [RecipeID]  VARCHAR (6) NOT NULL,
    CONSTRAINT [FK_TblProductsRecipes_TblProducts] FOREIGN KEY ([ProductID]) REFERENCES [dbo].[TblProducts] ([ProductID]),
    CONSTRAINT [FK_TblProductsRecipes_TblRecipes] FOREIGN KEY ([RecipeID]) REFERENCES [dbo].[TblRecipes] ([RecipeID])
);

Table to identify individual recipes

CREATE TABLE [dbo].[TblRecipes] (
    [RecipeID]   VARCHAR (6)  NOT NULL,
    [RecipeName] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([RecipeID] ASC)
);

Table to associate stock items to recipes (many to many) [one stock item can be featured in many recipes but in different amounts, and one recipe can feature many stock items]

CREATE TABLE [dbo].[TblRecipeItems] (
    [RecipeItemID] INT          NOT NULL,
    [RecipeID]     VARCHAR (6)  NOT NULL,
    [StockID]      INT          NOT NULL,
    [ItemAmount]   VARCHAR (5)  NULL,
    [ItemMeasure]  VARCHAR (15) NULL,
    [ItemWeight]   FLOAT (53)   NOT NULL,
    [ItemCost]     MONEY        NOT NULL,
    PRIMARY KEY CLUSTERED ([RecipeItemID] ASC),
    CONSTRAINT [FK_TblRecipeItems_TblRecipes] FOREIGN KEY ([RecipeID]) REFERENCES [dbo].[TblRecipes] ([RecipeID])
    CONSTRAINT [FK_TblRecipeItems_TblStock] FOREIGN KEY ([StockID]) REFERENCES [TblStock]([StockID])
);

Table to identify individual stock items

CREATE TABLE [dbo].[TblStock] (
    [StockID]  INT          NOT NULL,
    [ItemName] VARCHAR (25) NOT NULL,
    [ItemUnit] VARCHAR (3)  NOT NULL,
    [Quantity] FLOAT (53)   NOT NULL,
    [ItemCost] MONEY       NOT NULL,
    [UnitCost] MONEY        NOT NULL,
    PRIMARY KEY CLUSTERED ([StockID] ASC)
);

The only issue I have right now is whether I need one more table for the FIFO or if it can be integrated into any of the existing tables. Lets say I have the following ingredient listed in [TblStock]

[StockID] "357"
[ItemName] "Tomato Powder"
[ItemUnit] "grams"
[ItemQuantity] "900"
[ItemCost] "32.99"
[UnitCost "0.3299"

One bag is purchased on 1 OCT 19, and a second bag is purchased on 1 NOV 19, but I want my stock quantity to show 1,800 grams how would I do that?


In addition to the FIFO question and possibly part of the same table, I need to deduct ingredients as they are used

the [ItemAmount] and [ItemMeasure] fields in [TblRecipeItems] will not relate to stock as they will be the measures as listed on the recipe - teaspoon tablespoon etc.
[ItemWeight] will be the weight in grams that the measured amount equals so that the value can be accurately deducted from the stocked quantity. [ItemCost] is the [UnitCost] from [TblStock] multiplied by [ItemWeight] in [TblRecipeItem]

Example
[RecipeItemID] "1234"
[RecipeID] "SOUTOM"
[StockID] "357"
[ItemAmount] "2"
[ItemMeasure] "Tablespoon"
[ItemWeight] "6.84"
[ItemCost] "2.256516"

When a recipe using this line item is added to an order, the Value from [ItemWeight] will need to be deducted from the stock quantity.

[Zvoni]

One bag is purchased on 1 OCT 19, and a second bag is purchased on 1 NOV 19, but I want my stock quantity to show 1,800 grams how would I do that?

Easy! Add a Field to table Ingredients (TblRecipeItems) called "SumQuantity" (or whatever)
I'll leave to you to either
a) update the SumQuantity withh each transaction from your Frontend
b) Write a Trigger to do that

As for your second question, let me think about it....

EDIT: @second question.
Same approach as above
a) Write an UPDATE-Statement deducting from both Tables/Fields (Quantity in TablStock as well as SumQuantitiy in TbleRecipeItems)
b) Write a Trigger to do that

btw: don't forget to deduct from/recalculate your Costs (again: Do it yourself from the Frontend or use the Trigger.)