6

I'm creating a multi-user/company web application in PHP & MySQL. I'm interested to know what the best practice is with regards to structuring my database(s).

There will be hundreds of companies and thousands of users of this web app so this needs to be robust. Each company won't be able to see other companies data, just their own. We will be storing mainly text data and will probably only be a few MB per company.

Currently the database contains 14 tables (for one sample company).

Is it better to put the data for all companies and their users in a single database and create a unique companyID for each one?

or:

Is it better to put each company's data in its own database and create a new database and table set for each new company that I add?

What are the pluses and minuses to each approach?

Thanks,

Stephen

Gruber
  • 4,478
  • 6
  • 47
  • 74
salonMonsters
  • 1,237
  • 3
  • 16
  • 26

4 Answers4

7

If a single web app is being used by all the different companies, unless you have a very specific need or reason to use separate databases (it doesn't sound like you do), then you should definitely use a single database.

Your application will be responsible for only showing the correct information to the correct authenticated users.

philfreo
  • 41,941
  • 26
  • 128
  • 141
  • Thanks philfreo. That is the route we thought made the most sense, but wanted to make sure that we weren't heading down the wrong road. – salonMonsters Dec 16 '09 at 00:10
  • 1
    As you create your ddl, be sure to be consistent about your company and user id data. You may want to make your primary keys (company_id, whatever) in all your tables. – O. Jones Dec 16 '09 at 00:54
3

Multiple databases would be a nightmare to maintain. For each new company you'd have to create and administer each one. If you make a change to one schema, you'll have to do it to your 14+.

Thousands of users and thousands of apps shouldn't pose a problem at all as long as you're using something that is a real database and not Access or something silly like that.

holmes
  • 578
  • 1
  • 7
  • 16
3

Multi-tenant

Pluses

  • Relatively easy to develop: only change database code in one place.
  • Lets you easily create queries which use data for multiple tenants.
  • Straightforward to add new tenants: no code needs to change.
  • Transforming a multi-tenant to a single-tenant setup is easy, should you need to change your design.

Minuses

  • Risk of data leak between tenants if coding is sloppy. Tenant view filters can in some cases be employed to reduce this risk. This method is based on using different database user accounts for different tenants.
  • If you break the code, all tenants will be affected.

Single-tenant

Pluses

  • If you have very different requirements for different tenants, several different database models can be beneficial. This is the best case for using a single tenant setup.
  • If you code sloppily, there's practically no risk of data leak between tenants (tenant A will not be able to access tenant B's data). In addition, if you accidentally destroy the schema of one tenant through a botched update, other tenants will remain unaffected.
  • Less SQL code when you don't need to take tenant ID values into account in your queries

Minuses

  • Database schemas tend to differentiate with time, often resulting in a nightmare. Using a database compare tool, you can alleviate this problem, but potentially many schemas need to be compared.
  • Including data from several databases in one query is typically complex, and often requires prepared statements.
  • Developing is hard, since you need to make the same changes to multiple schemas.
  • The same database entity can appear in many databases with different ID keys, resulting in confusion.
  • Transforming a single-tenant to a multi-tenant setup is very hard, should you need to change your design.
Gruber
  • 4,478
  • 6
  • 47
  • 74
2

A single database is the relational way. One aspect from this perspective is that databases gather statistics about database usage and make heavy use of this. If you split things up you will be shooting yourself in the foot as the statistics will be fragmented.

Hassan Syed
  • 20,075
  • 11
  • 87
  • 171