db design for storing inventory per customer

Question

We have a product microservice which stores drugs and we have an inventory microservice that stores inventory for that product.

inventory

id	productid	quantity on hand
1	1	10
2	2	10
3	3	10

One of the new requirements is to store inventory for patients.

What would be the best practice to accommodate such a case?

Should we just add a new column, patientid, to the inventory table or is there better way to handle this?

Adding a new column to the same table will lead to a case where 2 or more rows, one with patientid null and other rows that have patientid value. This will lead us to chang all existing db calls to fetch data based on context (patientid).

id	productid	patientid	qoh
1	1	p1	10
2	2	p2	10
3	3	null	10

One of the suggestions from my team member is create new product for each patient owned item. eg

product table

productid	patient-drug	quantity
1	john-tylenol	10mg,
2	ram-tylenol	10mg
3	tylenol	10 mg

And then we'd use those patient specific and non-patient specific product IDs in the inventory table to store quantity-on-hand. However, this approach seems weird due to so many copies of tylenol being created.

Answer 1

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

InventoryId	ProductName	Quantity	TransactionDate	Transaction Type
1	Acetominophen	12	2021-01-01 00:00:00.000 +00:00	Product Received
2	Acetylsalicylic Acid	6	2021-01-02 12:34:56.789 +00:00	Product Received

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]

ProductName	TransactionDate	Transaction Type	Transaction Quantity	Balance
Acetominophen	2021-01-01 00:00:00.000 +00:00	Product Received	12	12
Acetominophen	2021-10-08 19:26:01.329 +00:00	Product Assigned to Patient	-4	8
Acetylsalicylic Acid	2021-01-02 12:34:56.789 +00:00	Product Received	6	6

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];

PatientName	ProductName	TransactionDate	Balance
Sally	Acetominophen	2021-10-08 19:26:01.329 +00:00	4

Answer 2

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