I am trying to understand how to manage multiple stores in a inventory management system (Each store has large number of data). There are 123 stores in this shop. Some stores have sub stores.

So I planned to create one master database (only super admin can access this) and one separate database for each store and database user for each database.

I have some doubts about this approach.

1). Is this design is correct? If not what are the suitable ways?

2). What is the best DBMS for this kind of projects?

3). How to update master database to get daily sales, monthly sales, employee details etc real time?

4). How many databases I can create using this

  • 2
    I vote for an RDBMS. – Rick James Oct 19 '17 at 3:04
  • Do these stores have a master catalogue from which they order their stock? In any case, I would recommend an RDBMS in particular PostgreSQL – Vérace Oct 19 '17 at 5:50

This is not a challenge for one, modern RDBMS. You will realize you do not want to have one database per store the first time executive management asks for "How many iPhone 7s do we have on hand across all stores ahead of the new Apple launch?"

I have seen retail systems managing several hundred thousand SKUs across several hundred stores. The master inventory was one ~10 million row table with columns like:

  • Store ID
  • SKU
  • Units on hand

If you use partitioning on your table, queries can be sub-second.


There is an open retail data model that handles multiple stores that you can see here:


| improve this answer | |

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