Database Dispatcher Part 1

I am working on a new project that will have a lot of data. MySQL has been selected as the database, Doctrine 2 for the ORM and Zend Framework for application framework. The project is going to be a data warehouse where each customer will have similar data. Instead of cramming all the data into a single database, I am going to partition the data in advance. Each client will have their own database. A dispatcher will be setup to handle managing the database connection to use based on the customer.

Initial Thoughts

In order to route the application software to the correct database settings I must have a monolithic database for storing the following fields:

1. Id
2. Username
3. Password
4. Database Settings
1. Username
2. Password
3. Database name
4. Host name or IP address
5. Active

An alternative design might have

1. Id
2. Username
3. Database Settings
4. Active

Implementation Considerations

After implementing some of code I realized that a session must be setup in order to link the end-user to the proper database. The end-user must be logged into the web site before the session can be set. A default database must be setup that can be assigned to the end-user before they login. The alternative method for implementation would be to not run the database dispatcher code if a session has not been assigned and limit what actions are accessible.

I am going to implement a hybrid where a default database is assigned and all actions except the few pages are accessible.

Advertisement

« Doctrine 2 DQL IN statement

Video Encoding and Video Distribution (Youtube VS Akamai) »