db design for storing inventory per customer

I would create a table to hold the inventory, along with a separate table to hold patient's on-hand inventory.

This is best described using a minimal, complete, and verifiable example.

First, we'll create the tables we need. I'm doing this in tempdb to avoid conflicting with objects you may have created already.

USE tempdb; --clean up so we can run this multiple times with changes DROP PROCEDURE IF EXISTS dbo.[UpdatePatientInventory]; DROP VIEW IF EXISTS dbo.[Inventory_Balances]; DROP TABLE IF EXISTS dbo.[Inventory], dbo.[Patient_Product_OnHand], dbo.[Patient], dbo.[Product], dbo.[TransactionTypes]; GO 

--details about each product CREATE TABLE dbo.[Product] ( [ProductId] int NOT NULL IDENTITY(1,1) CONSTRAINT [Product_pk] PRIMARY KEY CLUSTERED , [ProductName] varchar(100) NOT NULL ); 

--details about each patient CREATE TABLE dbo.[Patient] ( [PatientId] int NOT NULL IDENTITY(1,1) CONSTRAINT [Patients_pk] PRIMARY KEY CLUSTERED , [PatientName] varchar(100) NOT NULL ); 

--Describes the types of transaction entries CREATE TABLE dbo.[TransactionTypes] ( [TransactionTypeId] int NOT NULL IDENTITY(1,1) CONSTRAINT [TransactionTypes_pk] PRIMARY KEY CLUSTERED , [TypeName] varchar(100) NOT NULL ); 

--contains multiple transactional rows for each drug added to inventory, or --removed from inventory, or transferred to a patient CREATE TABLE dbo.[Inventory] ( [InventoryId] int NOT NULL IDENTITY(1,1) CONSTRAINT [Inventory_pk] PRIMARY KEY CLUSTERED , [ProductId] int NOT NULL CONSTRAINT [Inventory_Product_fk] FOREIGN KEY REFERENCES dbo.[Product]([ProductId]) , [TransactionTypeId] int NOT NULL CONSTRAINT [Inventory_TransactionTypeId_fk] FOREIGN KEY REFERENCES dbo.[TransactionTypes]([TransactionTypeId]) , [TransactionDate] datetimeoffset(3) NOT NULL CONSTRAINT [Inventory_tx_date] DEFAULT ((SYSUTCDATETIME())) , [Quantity] int NOT NULL ); 

--transactions in/out of patients custody CREATE TABLE dbo.[Patient_Product_OnHand] ( [PatientId] int NOT NULL CONSTRAINT [Patient_Product_OnHand_PatientId_fk] FOREIGN KEY REFERENCES dbo.[Patient]([PatientId]) , [ProductId] int NOT NULL CONSTRAINT [Patient_Product_OnHand_ProductId_fk] FOREIGN KEY REFERENCES dbo.[Product]([ProductId]) , [TransactionDate] datetimeoffset(3) NOT NULL CONSTRAINT [Patient_Product_OnHand_tx_date] DEFAULT ((SYSUTCDATETIME())) , [Quantity] int NOT NULL , CONSTRAINT [Patient_Product_OnHand_pk] PRIMARY KEY NONCLUSTERED ([PatientId], [ProductId]) , INDEX [Patient_Product_OnHand_cx] CLUSTERED ([TransactionDate], [PatientId], [ProductId]) ); GO 

Here we're inserting some rows of data into each table.

--insert some test values INSERT INTO dbo.[Product] ([ProductName]) VALUES ('Acetominophen') , ('Acetylsalicylic Acid'); INSERT INTO dbo.[Patient] ([PatientName]) VALUES ('Sally') , ('John'); INSERT INTO dbo.[TransactionTypes] ([TypeName]) VALUES ('Product Received') , ('Product Sold') , ('Product Assigned to Patient'); INSERT INTO dbo.Inventory ([ProductId], [Quantity], [TransactionDate], [TransactionTypeId]) VALUES (1, 12, '2021-01-01T00:00:00.000Z', 1) , (2, 6, '2021-01-02T12:34:56.789Z', 1); 

This is what the inventory looks like now:

SELECT i.[InventoryId] , p.[ProductName] , i.[Quantity] , i.[TransactionDate] , [Transaction Type] = tt.[TypeName] FROM dbo.[Inventory] i INNER JOIN dbo.[Product] p ON i.[ProductId] = p.[ProductId] INNER JOIN dbo.[TransactionTypes] tt ON i.[TransactionTypeId] = tt.[TransactionTypeId] GO 

I'm using a stored procedure to perform transfers between our inventory, and patient's custody:

CREATE PROCEDURE dbo.[UpdatePatientInventory] ( @PatientId int , @ProductId int , @TransactionDate datetimeoffset(3) = NULL , @Quantity int ) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF @TransactionDate IS NULL BEGIN SET @TransactionDate = SYSUTCDATETIME(); END DECLARE @msg nvarchar(1000); BEGIN TRANSACTION BEGIN TRY INSERT INTO dbo.Patient_Product_OnHand ([PatientId], [ProductId], [TransactionDate], [Quantity]) VALUES (@PatientId, @ProductId, @TransactionDate, @Quantity); INSERT INTO dbo.Inventory ([ProductId], [TransactionDate], [Quantity], [TransactionTypeId]) VALUES (@ProductId, @TransactionDate, 0 - @Quantity, 3); IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END TRY BEGIN CATCH SET @msg = ERROR_MESSAGE() + N' at line ' + CONVERT(nvarchar(10), ERROR_LINE()); RAISERROR (@msg, 0, 1) WITH NOWAIT; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SET @msg = N'Transaction rolled back.'; RAISERROR (@msg, 0, 1) WITH NOWAIT; END CATCH END GO 

Moving product into patient's custody looks like:

EXEC dbo.UpdatePatientInventory @PatientId = 1, @ProductId = 1, @Quantity = 4; 

This view shows the current inventory, with transactional details and balances at every stage:

CREATE VIEW dbo.[Inventory_Balances] AS SELECT p.[ProductName] , i.[TransactionDate] , [Transaction Type] = tt.[TypeName] , [Transaction Quantity] = i.[Quantity] , [Balance] = SUM(i.[Quantity]) OVER (PARTITION BY i.[ProductId] ORDER BY i.[TransactionDate] ROWS UNBOUNDED PRECEDING) FROM dbo.[Inventory] i INNER JOIN dbo.[TransactionTypes] tt ON i.[TransactionTypeId] = tt.[TransactionTypeId] INNER JOIN dbo.[Product] p ON i.[ProductId] = p.[ProductId] GO 

Looking at the output of the view:

SELECT * FROM dbo.[Inventory_Balances] ib ORDER BY ib.[ProductName] , ib.[TransactionDate] 

And a simple query showing the current balance in the custody of each patient:

SELECT pa.[PatientName] , pr.[ProductName] , ppo.TransactionDate , [Balance] = SUM(ppo.Quantity) OVER (PARTITION BY ppo.[PatientId], ppo.[ProductId] ORDER BY ppo.[TransactionDate] ROWS UNBOUNDED PRECEDING) FROM dbo.[Patient] pa INNER JOIN dbo.[Patient_Product_OnHand] ppo ON pa.[PatientId] = ppo.[PatientId] INNER JOIN dbo.[Product] pr ON ppo.[ProductId] = pr.[ProductId]; 

Sounds like you should create a cross-reference table (XRef) that links a patient to inventory.

https://stackoverflow.com/questions/321127/how-do-i-create-a-cross-reference-table-query-for-my-data