2

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? – Laurenz Albe Oct 1 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. – M. Boligsou Oct 1 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. – Laurenz Albe Oct 2 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. – M. Boligsou Oct 2 at 20:03
  • Great. Edit the question and add all this information, together with some sample data that illustrate the question. – Laurenz Albe Oct 3 at 5:45
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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