Creating Tables at runtime vs Creating Databases at runtime

TD

New Member
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 cssdatabase: containing 20+ tablesThe 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. [*]At runtime I can create a new databse with the 20+ tables using CREATE DATABASE[*]At runtime I can create additional 20+ tables with the company name as prefix for the tables using CREATE TABLES[*]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?
 
Back
Top