Re: tables per company - why not

[Colleen Boye]

Some reasons:

* DDL is required to add new companies, because you have to add a new table. This is a lot less flexible and a lot less secure: Any user who might need to add or remove a new company needs add/drop privileges, and could then drop your entire database!

* Views would be useless because you'd need to rewrite them all every time a new company was added.

* Altering tables and keeping data consistent becomes a rigamarole. If you decided there was a new field you wanted to add (say, number of employees), you'll need to alter every single table to add the EmployeeCount column. The odds of tables being inconsistent with each other are extremely high.

* Queries involving multiple companies would be completely unworkable because you'd need a join for every single company. For instance, how would you write a query to list the phone numbers of all the companies? It would be quicker to look them all up manually.