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

closed as too broad by datagod, mustaccio, hot2use, Erik Darling, LowlyDBA Oct 19 '17 at 13:12

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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:


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