English 中文(简体)
Should I split the data between multiple databases or keep them in a single one?
原标题:

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

最佳回答

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.

问题回答

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.

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.

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.





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

please can anyone check this while loop and if condition

<?php $con=mysql_connect("localhost","mts","mts"); if(!con) { die( unable to connect . mysql_error()); } mysql_select_db("mts",$con); /* date_default_timezone_set ("Asia/Calcutta"); $date = ...

php return a specific row from query

Is it possible in php to return a specific row of data from a mysql query? None of the fetch statements that I ve found return a 2 dimensional array to access specific rows. I want to be able to ...

Character Encodings in PHP and MySQL

Our website was developed with a meta tag set to... <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> This works fine for M-dashes and special quotes, etc. However, I ...

Pagination Strategies for Complex (slow) Datasets

What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...

Averaging a total in mySQL

My table looks like person_id | car_id | miles ------------------------------ 1 | 1 | 100 1 | 2 | 200 2 | 3 | 1000 2 | 4 | 500 I need to ...

热门标签