How to make a custom CMS go multi-user

I have made a custom CMS and I'd like to make it multi-client while duplicating as little as possible. I don't mean multi-user as in different people in the same organisation accessing the same program, I mean multiple-clients as in different organisations using their own access of the same program as though they are independent applications.

I understand the principle of 开发者_如何学Csharing functions, and I imagine I'd need to put all the functions I've created into a shared folder in a parent directory.

I think I have got my head around at least the way the code works, but the database(sql) structure seems like the biggest challenge.

How is this typically accomplished?

My tables are fairly basic and I can see after doing some reading that its normal to simply add a 'client_id' or 'app_id' or something like this to every table and entry. This way there is not a duplicate of the databases, however then you get a mixture of all the clients data in the same tables. The problem it seems comes with if this program were to get very large with many clients that the data multiplies and so does the speed of the system for everybody. I'm not at that stage yet however, so should I not worry about that far ahead and cross that bridge when it comes since for now the speed sacrifice would be negligible?

Is it possible to somehow keep databases separate without doubling up on work if I change the structure of a table in the future or add extra fields etc?

I understand this might be difficult to answer without knowing the way I've structure my tables but they are quite simple like:

unique_id    |      title    |      modified_date       |      content
  xx                hello           0000-00-00 00:00:00       i am content     

The best I can think so far is that this would then become:

client_id     |    unique_id    |      title    |      modified_date       |      content
 xx                  xx                hello           0000-00-00 00:00:00       i am content     

like I said, I see this could run into some problems mostly with becoming bloated down the track but right now I don't see another way - perhaps you have another way of looking at this. Thanks.

---

Keep it as a single database with the client_id column added. If it gets large with many clients, partition the tables by LIST: http://dev.mysql.com/doc/refman/5.5/en/partitioning-list.html

Horizontal partitioning allows you to have one logical table be sub-divided so when your SQL includes "... WHERE client_id = 1", it will only ever have to read the index(es) or partition that contain "client_id = 1" data. Other partitions get ignored, almost as if you have a separate table for each client_id.

DISCLAIMER: I haven't used partitioning in MySQL myself. I'm just familiar with the concept from Oracle. Be sure your MySQL storage engine supports partitioning: http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

---

Your best bet is to make it use separate databases for each of the client's data and retaining all shared information (users, etc.) in one central database. Then when a client grows they can be moved off to another database server without affecting anyone else.

---

I have a similar situation with my web-app: many users sharing one database, where most of the tables have a client-identifier in them. It's not a CMS, but similar enough, the users are performing CRUD operations on their data.

There are pros and cons, but I wouldn't worry about performance overly. Since you will have to re-create your existing unique indexes to contain the client-id, you should see no great difference in performance: your look-ups now have an additional predicate, which appears in the indexes.

As George3 said, if you have significant volumes from one or a few clients, horizontal partitioning could be worth pursuing. But premature optimization and all that: I would wait and see if it becomes an issue first.

Managing multiple databases, or multiple versions of tables for different clients doesn't scale well, and is a maintenance nightmare. Just get the security on the content right.