We need to design a database model for an (internal) inventory system with the following criteria:

  • Inventory can hold only 1 type (Item) object.
  • There might be more than a million Item per user.
  • There is absolutely no use case for querying items for multiple users at once.
  • We should be able to query by properties (again user is constant).

We thought about creating a table per user, but at the end we decided to go with Solr. Is this the right approach? Is there better technology for this particular use case?

Note: We are using Java for backend and PostgreSQL as database if it matters.


I've never heard of Solr, but I don't see why a relational database table wouldn't work totally fine. Just create one single item table with a "user" column as well as a column for each item property.

If you want to be able to query by a particular property, add an index whose columns are the user column and that property column, in that order.

  • Because why would I make a table with billions of items when I can logically separate the data into smaller parts? Also I am not a db expert but wouldn't indices take too much disk space? – s.alem May 26 '17 at 13:33
  • Why would you not make one single table instead of separating the data into lots of tables? Each additional table is more complexity you have to deal with. In this case, breaking the data over multiple tables wouldn't give you any benefits at all. As for using too much disk space, the amount of space used by the indexes will probably be a fraction of the space used by the table data. That's probably not going to be too much space. – Tanner Swett May 26 '17 at 15:32

The InventoryAmount column in the Item table tells you how much of the item you have. Then the ItemAmount (maybe a better name for that) in the Item User table tells you how many units of the item the user has. You can tell your inventory by subtracting InventoryAmount from ItemAmount.

enter image description here

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.