用于将PHP MySQL应用程序部署到多个客户的重复表

Question

I am taking a PHP MySQL app which was built for one customer and deploying it to be used by multiple customers. Each customer account will have many users (30-200), each user can have several classes, each class has several metrics, each metric contains several observations. Several means about 2-8.

Originally I was planning to have one instance of the application code which would connect to the appropriate table set for that customer based on a table prefix. But I now considering using only one set of tables for all my customer accounts. This would simplify the application design which would be best int he long run. My question is whether I would be taxing the database server by combining all the customer data into the same tables. Most queries will be SELECTs, but due to the nature of the schema there can be quite a few JOINS required. Most INSERT or UPDATE queries are just one row in one table, and possibly one or two bridge entity tables at most.
I know this is one of those "it depends" questions but I am hoping to get a little guidance regarding how slow/fast MySQL is with what I am trying to do.

Here is an example of the longest JOIN queryvI would be doing.

SELECT $m_measure_table_name.*, $m_metric_table_name.metric_name,$m_metric_table_name.metric_descrip, $m_metric_table_name.metric_id, $c_class_table_name.class_size,$c_class_table_name.class_id,$c_class_table_name.class_field,$c_class_table_name.class_number,$c_class_table_name.class_section, $lo_table_name.*,$lc_table_name.*, $user_table_name.user_name,$user_table_name.user_id, $department_table_name.*
    FROM $m_measure_table_name
    LEFT JOIN $m_metric_table_name ON $m_measure_table_name.measure_metric_id=$m_metric_table_name.metric_id
    LEFT JOIN $c_class_table_name ON $m_metric_table_name.metric_class_id=$c_class_table_name.class_id
    LEFT JOIN $lo_table_name ON $m_metric_table_name.metric_lo_id=$lo_table_name.lo_id
    LEFT JOIN $lc_table_name ON $lo_table_name.lo_lc_id=$lc_table_name.lc_id
    LEFT JOIN $class_user_table_name ON $c_class_table_name.class_id=$class_user_table_name.cu_class_id
    LEFT JOIN $user_table_name ON $user_table_name.user_id=$class_user_table_name.cu_user_id
    LEFT JOIN $department_class_table_name ON $c_class_table_name.class_id=$department_class_table_name.dc_class_id
    LEFT JOIN $department_table_name ON $department_class_table_name.dc_department_id=$department_table_name.department_id
    WHERE $c_class_table_name.class_semester=:class_semester AND $c_class_table_name.class_year=:class_year
    AND $department_table_name.department_id=:id
    ORDER BY $department_table_name.department_name, $lc_table_name.lc_name, $lo_table_name.lo_id

Ultimately my question is whether doing long strings of JOINS like this on primary keys is taxing to the database. Also whether using one set of tables seems like the better approach to deployment.

Answer 1

This is too long for a comment.

SQL is designed to perform well on tables with millions of rows, assuming you have appropriate indexing and table partitioning. I wouldn't worry about data volume being an issue in this case.

However, you may have an issue with security. You probably don't want different customers to see each other's data. Row-level security is a pain in SQL. Table-level is much easier.

Another approach is to create a separate database for each customer. In addition to the security advantages, this also allows you to moving different customers to different servers to meet demand.

It does come at a cost. If you have common tables, then you need to replicate them or have a "common tables" database. And, when you update the code, then you need to update all the databases. The latter may actually be an advantage as well. It allows you to move features out to customers individually, instead of requiring all to upgrade at the same time.

EDIT: (about scaling one database)

Scaling should be fine for one database, in general. Databases scale, you just have to throw more hardware, essentially in a single server, at the problem. You will need the judicious use of indexes for performance and possibly partitions if the data grows quite large. With multiple databases you can throw more "physical" servers at the problem, with one database you throw "bigger" servers at the problem. (Those are in double quotes because many servers nowadays are virtual anyway.)

As an example of the difference. If you have 100 clients, then you can back-up the 100 databases at times convenient to them and all in parallel. And, if the databases are on separate servers, the backups won't interfere with each other. With a single database, you back up once and it affects everyone at the same time. And the backup may take longer because you are not running separate jobs (backups can take advantage of parallelism).