I'm building an application for a bakery company using CodeIgniter (PHP) and MySQL. The company has about 250 franchises. I've considered the following aspects when designing the databases:
- Each franchise must have its own data, since they are independent from each other
- All their products are shared between all the franchises, meaning there's only one menu
- Each franchise will have their own stock counting
- There'll be 3 different dashboards for the application: An administrative dashboard and a sales dashboard for each franchise, and one only for the main company, which will contain all franchises' reports.
I've designed a Primary Database with all their products, franchises and users that will have access to the application. Each user will be assigned to a franchise and a dashboard.
Then, each franchise will have a Secondary Database, with their stock counting, sales, customers, etc. All franchises will have their own database.
With this, the application will work as it follows:
- User will access the app login page, and will be requested to enter the franchise id, his login and password.
- All sales will be "duplicated" to de the primary database, allowing the company to keep track of all their franchises.
My questions are:
Assuming that all databases will be hosted at the same server, is it a good practice to have separate databases for each franchise? It's better to have different users assigned to each database, or only one user is enough?
Can i store all franchise database's names on the primary database, and then create a custom login model, that will set a second database connection according to which database the franchise is assigned to?
1) This would be bad practice.
- By having multiple databases, you have to duplicate table design and future changes into all those databases as you go, instead of updating in one place. If you find yourself duplicating data, you probably have a design problem, and you add a lot of complexity.
- If you store all the data in a single database, you can add a "store_id" field to your tables, designating which store the data belongs to. For individual store users, you can filter by this field. If a user is responsible for several stores, you can easily consolidate their data individually under a single log-in, because the data is available in the same database and table.
- Because the data is consolidated, you will not have to duplicate data, and company-wide reports and dashboards are much easier to generate and maintain
2) Using one database, this extra work and complexity isn't necessary. And you eliminate a huge area of problems and troubleshooting.