Database design - one database, multiple sites

I know this question has been asked so many times before but I couldn't find the exact answer for mine. So please let开发者_如何学Python me ask it here.

We built a CMS to control one site. Now the company is expanding and we have a couple more sites with almost identical core structure. We decided to go with one database for easy maintenance later on.

We have about 10 tables. For example, Pages, News, Settings (different for each site), ...

It doesn't sound like a good idea if we add one App_ID (or Site_ID) column to every each of these tables so we know what records from which particular site we should pull out.

For instance,

PAGES:
PageID   |   Body   |   SiteID
1        | abc      |   1
2        | cde      |   1
3        | aafd     |   2
4        | gsgs     |   2
5        | feg      |   3

I think it is very abundant to add this SiteID Column to every table in this Database.

I looked carefully at the Multi-Tenant Architecture but I don't know how to apply it to our sites CMS.

What is the best way to handle this situation, please help. Any enlightenment is appreciated.

Simple Code

---

We've recently been reviewing various strategies for multi-tenant single database.

If you don't want to add a Site identifier to your tables then you could give each tenant their own schema and set of tables. Each tenant could have their own connection string which only provides access to their schema (you would obviously need to switch connection at runtime (easy enough with both EF and NH if you are using these).

However, the option we opted for was to introduce an additional level into our application such that each component of the application (in your case News, Pages etc.) was represented as a feature in the database.

Each site then has a collection of feature "instances" and the data stored for each of those features (a blog might have posts, tags, categories) has a reference to the feature instance (not the site).

This does add additional complexity but we have found it to be extremely flexible and decouples our feature data from the site (make it possible to move feature instances between sites if we wanted to).

---

Mixed answer. Work in a similar project, same database structure, several sites.

We tried several stuff.

We are fan of "best practices", "database normalization", "design patterns", but, we ended using a practical approach, more than theorical.

We had one or more databases for each site / company, and each database table had a "site_id", and it worked.

We had some cases where a single company decided to split their database site for each division, so one database become several databases sometimes within the same database server, sometimes different database server.

We had a case where a company with a single site, buy a smaller company, added a new site with same database structure, and after 5 yeas, they merge the data.

The several sites plus "site_id", worked well.

---

Multiple databases would be the recommended way since it allows simpler backup and restoration of a single site. Also, if you find you need to introduce replication slaves, replication can be more efficient with the use of separate databases. Most multi-site hosting solutions I know of, use multiple databases.

However, if you are intent on a single database, the other option is to duplicate the tables with a prefix indicating which site it belongs to.