PostgreSQL – Database Schema for Inventory Management

database-designpostgresql

I am setting up a Postgres database that keeps track of items that entities in my system own. Each incoming datum says that Entity Q has N of Item X.

My first instinct is to have a separate table for each entity (multi-tenant). But I have read a number of posts advising against that due to scalability concerns.

What is the most effective way to design a database that stores a many-to-many relationship along with quantities? Thanks.

Additional information:

My information is coming through in JSON format from an API. For example:

{
    "data": [
        {
            "entity": "John",
            "item": "apple",
            "quantity": "5"
        },
        {
            "entity": "Mary",
            "item": "apple",
            "quantity": "2"
        },
        {
            "entity": "John",
            "item": "orange",
            "quantity": "6"
        }
    ]
}

My system must be able to tell each entity what items they have and how many (John has 5 apples and 6 oranges). It does not need to show which entities own any particular item (John and Mary both own apples).

Since each entity is independent I think it might make sense to keep all information in separate tables based on the type of entity. The main concern with this is that many entity tables might contain the same item (such as apples). But I do not think there is a way to combine these duplicates because the quantity of each item is mandatory.

I hope this clarifies some of the uncertainties surrounding my problem. Thank you for your support.

Best Answer

I suggest this data model:

CREATE TABLE entity (
   entity_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE item (
   item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE inventory (
   entity_id bigint NOT NULL REFERENCES entity (entity_id),
   item_id bigint NOT NULL REFERENCES item (item_id),
   qualtity integer NOT NULL CHECK (quantity > 0),
   PRIMARY KEY (entity_id, item_id)
)

CREATE INDEX ON inventory (item_id);

If you know that the entity and item names can never change and are unique, you can use the name as natural primary key.

Whenever you store a new datum, you do it as follows:

INSERT INTO inventory (entity_id, item_id, quantity)
   VALUES (42, 123, 12)
ON CONFLICT DO UPDATE
   SET quantity = inventory.quantity + 12;

That will allow efficient searches and joins.

Don't create multiple tables. If the tables will become large, or you need an efficient way to delete old items, you could use partitioning. How that is best done depends on your queries and the number of distinct entities.

Related Question