I'd consider 3 tables to describe the items, and a table to describe the transactions against those items. Something like:
CREATE TABLE dbo.Manufacturers
(
ManufacturerName NVARCHAR(100) NOT NULL
CONSTRAINT PK_Manufacturers
PRIMARY KEY CLUSTERED
, ManufacturerWebsite NVARCHAR(100) NULL
);
CREATE TABLE dbo.Models
(
ManufacturerName NVARCHAR(100) NOT NULL
, ModelName NVARCHAR(100) NOT NULL
, ModelDescription NVARCHAR(1000) NOT NULL
, CONSTRAINT PK_Models
PRIMARY KEY CLUSTERED
(ManufacturerName, ModelName)
, CONSTRAINT FK_Models_Manufacturer
FOREIGN KEY (ManufacturerName)
REFERENCES dbo.Manufacturers(ManufacturerName)
);
CREATE TABLE dbo.Parts
(
ManufacturerName NVARCHAR(100) NOT NULL
, ModelName NVARCHAR(100) NOT NULL
, PartNumber NVARCHAR(100) NOT NULL
, ReorderLevel INT NOT NULL
, ReorderQuantity INT NOT NULL
, CONSTRAINT PK_PartNumbers
PRIMARY KEY CLUSTERED
(ManufacturerName, ModelName, PartNumber)
, CONSTRAINT FK_PartNumbers_Model
FOREIGN KEY (ManufacturerName, ModelName)
REFERENCES dbo.Models(ManufacturerName, ModelName)
);
The above tables can contain whatever extra columns you need that make sense for each table. Perhaps the manufacturer's phone number, or perhaps where you order parts from, etc.
Here is the table where you keep track of when items were consumed or received:
CREATE TABLE dbo.Transactions
(
TransactionID INT NOT NULL
CONSTRAINT PK_Transactions
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, ManufacturerName NVARCHAR(100) NOT NULL
, ModelName NVARCHAR(100) NOT NULL
, PartNumber NVARCHAR(100) NOT NULL
, TransactionDate DATETIME NOT NULL
, TransactionDescription NVARCHAR(1000) NOT NULL
, Quantity INT NOT NULL
, CONSTRAINT FK_Transactions_Parts
FOREIGN KEY (ManufacturerName, ModelName, PartNumber)
REFERENCES dbo.Parts(ManufacturerName, ModelName, PartNumber)
);
To show this design, we'll insert some sample data:
INSERT INTO dbo.Manufacturers (ManufacturerName, ManufacturerWebsite)
VALUES ('HP', 'www.hp.com');
INSERT INTO dbo.Models (ManufacturerName, ModelName, ModelDescription)
VALUES ('HP', 'LaserJet II', 'Laser printer');
INSERT INTO dbo.Parts (ManufacturerName, ModelName, PartNumber, ReorderLevel, ReorderQuantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', 10, 5);
INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 5);
INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 15);
INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -6);
INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -8);
INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -5);
This shows how you can see a running total and when you need to reorder:
SELECT t.ManufacturerName, t.ModelName, t.PartNumber
, t.TransactionDate
, t.TransactionDescription
, t.Quantity
, Balance = SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING)
, NeedToReorder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN 'X' ELSE '' END
, QuantityToOrder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN p.ReorderQuantity ELSE 0 END
FROM dbo.Transactions t
INNER JOIN dbo.Parts p ON t.ManufacturerName = p.ManufacturerName
AND t.ModelName = p.ModelName
AND t.PartNumber = p.PartNumber
ORDER BY t.TransactionID;
The output from the above query: