Creating Tables at runtime vs Creating Databases at runtime.

Question

I am building a customer sales and invoicing app for a company.The app is in PHP MYSQL, but I guess that shouldn't matter much.

The app structure is as follows:

website files: .php, ,.htm, images and css

database: containing 20+ tables

The app is currently being used by the company and 2 other sister concerns(beta testing mostly). Since the user base is small, I manually copy the website files and the database to set the app up for usage by a new compnay. I am looking for a way to make the app more 'scalable' without having to manually do the 'scaling'.(meaning I don't want to manage three different filesets and dbs manually) Since the code is company neutral and the databases contain the company info, I will only have to recreate the database when a user requests a new company to be setup. There are multiple ways that I can create the database for a new compnay.

1. At runtime I can create a new databse with the 20+ tables using CREATE DATABASE

2. At runtime I can create additional 20+ tables with the company name as prefix for the tables using CREATE TABLES

3. I can add a company column to all of my tables and then continue adding info as before.

The new database method appeals to me because backup and maintenance would be easy, it would probably be a bit more secure since a hacker will only be able to access the details of one company(probably...). This option wont work on a shared hosting with a limit on number of databases.

The second option would mean I can create everything in one database. But this option is a bit more 'shared'.

I wouldn't go for the third option due to table level locking issues in MySQL (I am not using InnoDb for all my tables).

So my choices are between option 1 and 2. Developers who've managed financial apps , please advice, as once the beta testing phase is done with, the usage base will increase, and I don't wish to manually change the same thing in 10 databases and filesets. What will be the best thing to do?

Answer 1

From the security point of view, customers should have separate databases, which restricted access from MySQL users. That user should only have the permissions needed by the application (often SELECT, INSERT, DELETE and UPDATE), and not administrative permissions (DROP, CREATE, GRANT, ...). In this way, you've a clear overview on databases and tables.
When you need to alter a table structure, you just executes the (thoroughly tested) SQL query on your database.

CSS, images and other static content could be put in a subdomain, or Alias (Apache)
Libraries and neutral classes should be put in one directory too, using include_path to include such a file, so you have only one fileset that needs to be changed.