Building an Inventory Database

Question

I am building a small inventory database for copier supplies at my place of work. In the end this database will be automated to subtract taken from the shelves and add any incoming orders. Right now I am having a hard time with creating the tables. I don't have much experience with SQL except for a class I took a while back. I can't think of tables that I would need for this project. Currently we use a manual system with the following information.

Manufacturer

Model

Part #

Reorder Level

Reorder Quantity

On Hand

Quantity on Order

I don't need to know price or anything like that. I think all of that could go on one or two tables. But as stated earlier, I haven't had any experience with SQL in a while and would like some feedback.

Answer 1

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: