We are working on an application in CakePHP that will be used by several companies. We want to provide performance, scalability, code manageability and security for our application.
Our current beta version creates a new database for each client. When a new company joins the site, we run an SQL script to create an empty database. This has the following advantages: - Increased security (company users are separated from each other) - We can install the database through a subdomain (IE: monkey.site.com, uses the site_monkey database) - A separate code database. - Performance for SQL queries is usually good, as the data is divided into smaller databases.
Now, unfortunately, this has many shortcomings - Manageability: changes to the database must occur in all existing databases - The method of creating the SQL script is awkward and not as reliable as we would like - We want to allow users to log in from the main page ( EG. Www.site.com), but at the moment we cannot do this, because the subdomain determines which database to use. - We would like the central place is occupied by the metric / use of customers.
Thus, we are torn / unsure of what is the best solution for our database structure for our application.
Currently, we see three options: - Maintain a multiple database design - Combine all companies in one database and define each of them as "companyId", - Some kind of split model, where certain tables are in the "base database" and others are in the client database.
Can you guys offer some of your valuable advice on how you think we are best off doing this? Any feedback / information would be appreciated.
Many thanks,
kSeudo
source
share