By: Sebastiao Pereira | Updated: 2024-08-07 | Comments | Related: > Database Design
Problem
An Enterprise Resource Planning (ERP) System is software companies use to manage and integrate the essential parts of their business. Normally, an ERP software system integrates planning, purchasing, inventory, sales, marketing, finance, and more. The main benefits of having this system are the free flow of communication between business areas, a single source of information, and accurate, real-time data reporting.
How can a minimum basic model of an ERP be built using SQL Server?
Solution
Only essential information will be used in the tables to streamline our work and build a fundamental ERP system. For our example, I will use a company that produces bottled water.
The renowned SQL-Datalake Bottle Company Inc. specializes in crafting the popular SQL Natural Mineral Water in 330 ml bottles sold in a package of 12 units. They've approached us to implement an ERP program for their operations.
Terms and Definitions
Before we dive into the coding, let's define terms to make sure we are on the same page.
Specification
Specification is defined as a detailed description of the design and materials used to make something, is the fundamental basis of an ERP, and everything else comes from it. One typical example of a kind of specification is a Granny cake recipe: 2 cups all-purpose flour, 1 cup of white sugar, …
There are two types of specification:
- Incoming Materials Specifications: For raw materials, components, or parts received from suppliers or vendors to be used in the manufacturing process.
- Product Specifications: Detailed descriptions of the characteristics, features, and requirements of a product. We will focus on this one for this article.
Product
Product is defined as something that is made to be sold.
Packaging
Packaging is defined as a process of designing, evaluating, and producing containers or wrappers for products. It can be classified as:
- Primary Packaging: The layer of packaging that directly encloses the products. It is in direct contact with the product and serves to protect, preserve, and contain it. Examples: bottles, cans, blisters, and others.
- Secondary Packaging: The outer packaging that contains and protects the primary packaging and product during storage, handling, and transportation. Examples: cardboard boxes, corrugated cartons, shrink wrappers, pallets, and others.
Material Consumption
Material consumption refers to the quantity of raw materials utilized in the production process to manufacture a product.
Material Procurement Cost
Material Procurement Cost is the expense of acquiring raw materials from suppliers for use in the manufacturing process. It includes the actual cost of the materials themselves, as well as any additional expenses such as taxes, duties, shipping fees, and supplier fees.
Standard Loss Acceptable
Standard Loss Acceptable refers to a predetermined level of loss or waste that is considered acceptable within a manufacturing process due to factors such as machine inefficiencies, human errors, scraps, or defects in raw materials. The minimum value is one (1) plus the acceptable percentage of waste.
Standard Yield
Standard yield is the expected or target yield for the amount of material consumption, including the standard loss that is acceptable.
Creating a Table for Resources
This table will store data for the resource types of incoming materials (I) and final products (P).
CREATE TABLE [dbo].[Resources]( [ResourceId] [nvarchar](10) NOT NULL, [ResourceName] [nvarchar](250) NOT NULL, [ResourceUnity] [nvarchar](50) NULL, [ResourceType] [char](1) NULL, [ResourceEmbType] [smallint] DEFAULT (0), [ResourcePrice] [money] NULL, [ResourceInventory] [numeric](12, 4) NULL, [ResourceMinQty] [numeric](12, 4) NULL, CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ( [ResourceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
The columns:
- [ResourceEmbType] Will use 1 for primary packaging, 2 for secondary packaging, and 0 for others.
- [ResourcePrice] The material procurement cost for incoming materials (I) or the selling price for products (P).
- [ResourceInventory] The amount of resources already in the warehouse.
- [ResourceMinQty] The minimum quantity for the resource to be purchased or produced.
INSERT INTO [dbo].[Resources]
([ResourceId]
,[ResourceName]
,[ResourceUnity]
,[ResourceType]
,[ResourceEmbType]
,[ResourcePrice]
,[ResourceInventory]
,[ResourceMinQty])
VALUES ('BTL330', 'Plastic bottle 330 ml', 'unity', 'I', 1, 0.25, 180.0000, 100.0000)
,('CAP', 'Plastic cap', 'unity', 'I', 1, 0.003, 195.0000, 100.0000)
,('CBX1234', 'Cardboard box 330 ml X 12', 'unity', 'I', 2, 0.70, 40.0000, 50.0000)
,('LBL330', 'Self-adhesive printed label 330 ml', 'unity', 'I', 2, 0.005, 370.0000, 1000.0000)
,('MW330', 'SQL Mineral Water bottle 330 ml x 12', 'unity', 'P', 0, 4.75, 10.0000, 100.0000)
,('MWT', 'Mineral water', 'liter', 'I', 0, 0.12, 15000.0000, 1000.0000);
GO
Creating a Table for Product Specifications
CREATE TABLE [dbo].[ProductSpecs]( [ProductId] [nvarchar](10) NOT NULL, [ResourceId] [nvarchar](10) NOT NULL, [Yield] [numeric](18, 6) NULL, [StdLoss] [numeric](18, 6) NULL, [StdYield] AS ([Yield]*[StdLoss]), CONSTRAINT [PK_ProductSpecs] PRIMARY KEY CLUSTERED ( [ProductId] ASC, [ResourceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
Our first specification will be for the product already registered at the Resources table with the code "MW330" and the description "SQL Mineral Water bottle 330 ml x 12".
Note that:
- We will sell this product as one box with 12 bottles of 330 ml each.
- To ensure that a bottle has at least 330 ml, we will add 340 ml per bottle, so in a box of 12, we will use 12 x 340 = 4,080 ml. Once the water is commercialized in Liters, the value will be 4.08 L.
- We assume that the process loses, on average, 3 ml per 330 ml, then we will assign as a Standard Loss Acceptable for mineral water the value of 1 + 3 / 330 = 1.009.
- We defined as Standard Loss Acceptable for plastic bottles, caps, and cardboards with a value of 1 + 1 / 2000 = 1.0005
- We defined a Standard Loss Acceptable for self-adhesive labels as 1 + 1 / 250 = 1.004.
INSERT INTO [dbo].[ProductSpecs]
([ProductId]
,[ResourceId]
,[Yield]
,[StdLoss])
VALUES ('MW330','BTL330',12,1.0005)
,('MW330','CAP',12,1.0005)
,('MW330','CBX1234',1,1.0005)
,('MW330','LBL330',12,1.004)
,('MW330','MWT',4.08,1.009);
GO
Creating the Product Specs View
CREATE VIEW [dbo].[vProductSpecs]
AS
SELECT [Specs].[ProductId]
,[Specs].[ResourceId]
,[Resources].[ResourceName]
,[Specs].[Yield]
,[Specs].[StdLoss]
,[Specs].[StdYield]
,[Resources].[ResourceUnity]
,[Resources].[ResourcePrice]
,[Resources].[ResourceInventory]
,[Resources].[ResourceMinQty]
,[Resources].[ResourceEmbType]
FROM [dbo].[ProductSpecs] AS [Specs] INNER JOIN
[dbo].[Resources] AS [Resources] ON [Specs].[ResourceId] = [Resources].[ResourceId]
WHERE ([Resources].[ResourceType] = 'I');
GO
Creating a Function for the Quantity of Incoming Materials Needed
-- ==================================================
-- Author: SCP
-- Create date: 20240610
-- Description: Quantity of resources needed
-- ==================================================
CREATE FUNCTION [dbo].[ufnResourceQty]
(@ResourceId nvarchar(10)
,@ResourceQty numeric(18,6))
RETURNS numeric(18,6)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Result numeric(18,6)
SELECT @Result = CEILING((([StdYield] * @ResourceQty)-[ResourceInventory]+[ResourceMinQty])/[ResourceMinQty])*[ResourceMinQty]
FROM [dbo].[vProductSpecs]
WHERE [ResourceId] = @ResourceId;
IF @Result < 0
SET @Result = 0;
RETURN @Result;
END
Creating a Function to Return the Product Revenue
-- ==================================================
-- Author: SCP
-- Create date: 20240611
-- Description: Product Revenue
-- ==================================================
CREATE FUNCTION [dbo].[ufnProductRevenue]
(@ProductId nvarchar(10)
,@ProductQty numeric(18,6))
RETURNS numeric(18,6)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Result numeric(18,6)
SELECT @Result = [ResourcePrice] * @ProductQty
FROM [dbo].[Resources]
WHERE [ResourceType] = 'P' AND
[ResourceId] = @ProductId;
IF @Result < 0
SET @Result = 0;
RETURN @Result;
END
GO
Creating the Store Procedure to Estimate the Purchasing Needs
-- =============================================
-- Author: SCP
-- Create date: 20240610
-- Description: Purchasing Incoming Materials
-- =============================================
CREATE PROCEDURE [dbo].[uspPurchasingOrders]
@SalesProductId nvarchar(10)
,@SalesProductQty numeric(18,6)
AS
BEGIN
SET NOCOUNT ON;
SELECT [ResourceName]
,CONVERT(float,[StdYield]) AS [StdYield]
,CONVERT(float,[StdYield] * @SalesProductQty) AS [QtyNeeded]
,FORMAT(CONVERT(float,[StdYield] * @SalesProductQty * [ResourcePrice]), 'C', 'en-US') AS [ResourceCost]
,FORMAT([dbo].[ufnProductRevenue] ('MW330',1000) - SUM([StdYield] * @SalesProductQty * [ResourcePrice])
OVER (ORDER BY [ResourceEmbType] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'C', 'en-US') AS [Margin]
,CONVERT(float,[ResourceInventory]) AS [Inventory]
,CONVERT(float,[ResourceMinQty]) AS [MinQty]
,CONVERT(float,[StdYield] * @SalesProductQty - [ResourceInventory] + [ResourceMinQty]) AS [UsageForecast]
,[ResourceUnity] AS [Unity]
,CONVERT(float,[dbo].[ufnResourceQty] ([ResourceId],@SalesProductQty)) AS ToPurchase
,FORMAT([ResourcePrice], 'C', 'en-US') AS UnityPrice
,FORMAT([dbo].[ufnResourceQty] ([ResourceId],@SalesProductQty) * [ResourcePrice], 'C', 'en-US') AS [PurchasePrice]
,FORMAT(SUM([dbo].[ufnResourceQty] ([ResourceId],@SalesProductQty) * [ResourcePrice])
OVER (ORDER BY [ResourceEmbType] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'C', 'en-US') AS [PurchaseSum]
FROM [dbo].[vProductSpecs]
WHERE [ProductId] = @SalesProductId
ORDER BY [ResourceEmbType];
END
GO
Simulating a Sales Order
Numbers for prices and yields are arbitrarily chosen and do not represent real values.
The sales department issued a sales order of 1,000 units of "MW330 - SQL Mineral Water bottle 330 ml x 12" product.
- The sales order of 1,000 units of the Product "MW330" has a revenue of:
- To obtain the quantity of resources to be purchased, we should execute the following:
Understanding the Results
Let's look at the first row, the Mineral water:
- The STDYIELD shows the quantity for 12 bottles, including the acceptable waste in the process.
- The QTYNEEDED is the STDYIELD * @SALESPRODUCTQTY units of the product sold.
- The RESOURCECOST is obtained using the formula QTYNEEDED * UNITYPRICE.
- The MARGIN is the cumulative remaining value of the PRODUCTREVENUE – RESOURCECOST and shows the revenue after deducting the cost of the raw materials.
- The INVENTORY is the quantity of mineral water available in the incoming materials warehouse.
- The MINQTY is the number that our production planning department assumes that it is safe to keep at the warehouse.
- The TOPURCHASE is obtained using the formula QTYNEEDED – INVENTORY + MINQTY ceiling by MINQTY
- The PURCHASEPRICE is obtained using the formula TOPURCHASE * UNITYPRICE.
Next Steps
- The table RESOURCES can be improved by including additional parameters: Shelf life, Stability, Reorder Point, and others.
- Any specific resource may have multiple suppliers with different prices for each batch delivered. Also, take into consideration that products also may have different sales prices.
- When a resource arrives at the warehouse, it will be submitted to incoming materials inspection, which could result in rejection. The quantity accepted for each batch should be identified at the warehouse to respect the FIFO rule (First-in, First-out).
About the author
Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-08-07
