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?