How to make a custom CMS go multi-user

Question

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 sharing 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.

George3 · Accepted Answer · 2011-09-12 00:30:18Z

2

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

edited Sep 12, 2011 at 0:30

answered Sep 12, 2011 at 0:24

George3

2151 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnD · Accepted Answer · 2011-09-11 23:48:36Z

1

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.

answered Sep 11, 2011 at 23:48

JohnD

4,0121 gold badge30 silver badges41 bronze badges

2 Comments

willdanceforfun Over a year ago

Thanks for that. Do you know if there is a way to have separate databases that are somehow connected to a parent structure so that if the parent structure changes, the children remain in sync?

JohnD Over a year ago

No, sorry. You're best bet is to have a script that keeps track of database version and updates the schema for each change. Sort of like database migrations in Ruby on Rails.

RET · Accepted Answer · 2011-09-12 00:53:38Z

1

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.

answered Sep 12, 2011 at 0:53

RET

9,1881 gold badge32 silver badges33 bronze badges

1 Comment

willdanceforfun Over a year ago

Thanks for that RET. I appreciate the input.

Collectives™ on Stack Overflow

How to make a custom CMS go multi-user

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related