Discrete databases, prefixed tables or keys?

Developing a large project which offers a web-based application to a particular business type.

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 - 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. I could script the creation of the tables once the database connection has been established.

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

Option 3. Use a single set of tables because the data is of the same type regardless of which buisnes it is 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!)

I will be developing this using Codeigniter at the backend with MySQL.

Any thoughts?

I would go with option 1, it is much cleaner and easier to handle from security perspective as well. MySQL is well suited for this model as well.

Thanks for your response skhanal. I have had time to think this through and I came to the same conclusion.

It is helped by the fact that Codeigniter allows multiple simultaneous database connections - so I can use one database for login credentials to identify which business is logging in and use that to select the correct database for all data transactions.

Sounds easy doesn't it?

Thanks again.

Mike