Discrete databases, prefixed tables or keys?

[eluser]mikertjones[/eluser]
Hello all

Developing a large project which offers a web-based application to a particular business type. I will be developing this using Codeigniter with MySQL, with both of which I have reasonable experience.

The nature of the business type is that many practices and processes are common and so there will be a requirement for very little customisation (if there is any customisation it would be mainly interface and printed reports - logos / business names etc.)

Having common processes means that the data used by these businesses also follows a pattern - e.g. each business will have users data, each business will have customer data and the set of tables will be the same for each business.

My question is how best to design the storage and handling of the data.

I see the options as:

Option 1. A separate database for each new business using the application. To set up the business to use the application I could script the automatic creation of the tables from stored queries once a new database connection has been established.

How would a business connect to their specific database - because there would have to be a point at which the server uses stored access credentials to identify the login - thereafter Codeigniter would have to switch to whichever was the correct database?

Option 2. Use a single database with tables having prefixed names to differentiate the businesses, e.g. businessA_users, businessA_customers much like Wordpress allows multiple blogs from a single database by prefixing table names. Again this could use scripted stored queries to set up the new prefixed tables when a business takes on the application. Accessing the data would be filtered by querying the appropriate tables with the specific prefix in a system variable.

Option 3. Use a single database with one set of tables because the data is of the same type regardless of which business it is (as per
Database normalization ) and then use appropriate keys in each database query.

Options 1 and 2 allow a greater scope for addition of custom data / tables - although I can foresee little requirement for that at this stage (Famous last words!)

Any thoughts?

I apologise if I have asked this in the wrong place. Steer me right and I'll ask elsewhere.

Best wishes to all.

Mike Jones