I currently have an application that is used by a single end customer. For ease of discussion assume the application only needs a single database table for all records. I now need to support multi-tenancy and so in the future I will have about 500 customers using the same database server.1) A possible solution is to have a separate database for each customer. As there will be around 500 customers that means having MySQL/SQL Server/Oracle/etc... host 500 separate databases. This sounds like overkill for a database server running on an average cloud based server.2) Another solution is to stick with the single database I already have but to have 500 different tables, one for each end customer. Each customer only averages around 2000 records in their own table. This is easy to implement and I would guess makes it easy to migrate the customer to another server, just move the entire table data over.3) Lastly I could stick to using a single table in the single database. Instead I add an additional column that identifies the customer the record belongs to. But then the table ends up with about 1,000,000 records which is the aggregate of all 500 customers that average 2,000 each. I do not know enough about performance and scaling to know 1, 2, 3 is going to give the best performance. Any ideas?
500 databases or 1 database with 500 tables or just 1 table with all the records?
performance;mysql;sql server;scalability;oracle
Personally, I'd go with option 3 because:It is normalized and simpleEasy to query for reportsEasy to back up(just 1 database to worry about)If you index the table well,  performance shouldn't be an issueAlso, Performance aside, here are some reasons why you would want to avoid option 1 and 2.Cons if you go with 500 databases, 1 per each customer:  Database Backups will be annoyingly tedious and messyCross Database queries are normally a luxury of enterprise database servers, in other words, generating customer reports will be a pain because you can't just have a single query to pull all the relevant customer information in your databaseInelegantCons if you go with 500 tables in 1 database, 1 table per customer:Your database is horribly unnormalized, lots column information duplication going on everywhereWhile it is easier to query a lot of tables than to query from different databases, you'd still have to run the same query across god knows how many tables instead of just 1 query