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.

  • Do you want to store the X too or just their quantity? Oct 1, 2019 at 21:39
  • 1
    The entity, the item, and the quantity. I need the database to tell me how many of any given item each entity has. Oct 1, 2019 at 21:59
  • That's quite unclear. Incoming information says how many X someone owns, but does not contain the X in question. Yet you want to store them. Maybe sit down and write out the requirements in a clear, complete fashion. Often a design emerges naturally once you are clear about the requirements. Oct 2, 2019 at 5:57
  • 1
    I do not understand what you mean. Each piece of information states the entity, the item, and the quantity. I am fairly confident about the requirements but I do not have a lot of experience with database design. Is this the kind of problem that can be broken into tables (such as for each entity) or is it best to keep everything together? I cannot think of any other options. Thank you for your responses. Oct 2, 2019 at 20:03
  • Great. Edit the question and add all this information, together with some sample data that illustrate the question. Oct 3, 2019 at 5:45

1 Answer 1


I suggest this data model:

   name text NOT NULL UNIQUE

   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)
   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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.