Hopefully someone can point me in the right direction on this. Let’s say I have 1000 companies that I am storing the same type of info on (employee info, calendar of events, etc.). Each company could have any number of employees. Each company will be able to log in and update their own information.
Is it more logical to have a different database for each company, or 1 database with each record having a company id field, or 1 database that has a different set of tables (prefixed with company id) for each company?
My hosting gives me unlimited databases that can be up to 1 Gb each.
I think you should have some thoughts about the maintenance implications; if you create one database (e.g. company) and relate records in other tables to the specific company record, you would only have to update a single table if you make changes in the future in the layout of your tables. If you would create tables or databases per account, you would have to write a script updating all these tables or databases. A database per user, however, gives you security at the data-level.
The only reason I could see you potentially wanting multiple databases would be considerations around your host. 1gb is not a terribly huge database, but if you do near that limit, I would think that switching hosts would be a better idea than creating multiple databases.
As long as you have indexes on companyid you will likely not see a performance problem.
