Multi database authentication system, where should I store sessions using Zend Framework?

Question

I am writing an ERM application using the Zend Framework in which user accounts are created under a main company account, enabling me to limit the number of user accounts for a company based on the license which the company paid for. Each company account has its own database (with identical structure to other companies) on my server to store data relevant to that company. The name of each companies database is stored in my "back end" database along with the rest of the companies account information and license key. The authentication system works as follows:

1. A new user (having never used the application before) lands on the index page and is greeted by a single text field for "Company Account Number"
2. After clicking "Submit", the next step in authentication is for username and password. When the user submits this form, all three pieces of information (account number, user name and password) are sent to my application's Authentication handler.
3. My "back end" database which stores company accounts is first queried to see if the account entered by the user exists. If it does, the company_db_name column is returned and a connection established then saved in the Zend_Registry. Otherwise, authentication has failed.
4. If the company account does exist, the database that was returned then has its users table queried for the specified username and password hash which either returns a successful instance of MyApp_Auth or false if the credentials were incorrect.

At first, I planned on storing user session data in the individual companies database, however I have run into the problem that there is no connection to this database when first landing on the application's index page. I have planned a workaround as follows:

1. Move my session storage table out of the customer's database to my "backend" database, which has a connection as soon as the application launches.
2. Add a "company account number" column to the table and index this column.
3. When a user lands on the application index page, the backend database can then be queried for the current user agent's sessionid. If it is found, then return all the necessary information i.e. the company database name to establish a connection, and the user's information to build a model with.

I have a couple questions regarding this approach:

Question 1 : Is there any risk in storing all session information for every user of my application in a single back-end database table? I am thinking in the multi-thousand user mindset.

Question 2 : I am concerned that a new user may visit the index page and by complete chance (understanding that this is a very low possibility, but still possible) have the same session_id as an existing session in the back-end database. Is this a valid concern, and if so, can it be mitigated?

Question 3 : Is there a better way, or would you recommend a different method to achieve my required functionality?

Thank you for your time!

Answer 1

To answer your 3 questions:

Answer 1. The is not risk as such for the storing session information of every user as long as you remove it on session expiration. The issue here is "scalability" what approach are you using? Is it scalable enough? What is the write/read speed? MySQL is 'structured' approach just like MSSQL. What processing time are you looking for? How much of information is stored? What is the architectural studies. Is it feasible enough for your client?

Answer 2. Ideally the session_id will not be the same so that should not be your concern.

Answer 3. You need NoSQL (Not Only SQL but, even more) approach. Read this

Looking at the MASSIVE-ness of your data, I strongly suggest you to go for HBASE (uses Hadoop, easy for multi cluster) or CouchDB or if you are Amazon fan dynamoDB.

Questions? :)

EDIT: Just realized you are using Zend Framework. In that case, you can also use MongoDB, and use Shanty Mongo library.