How to design database for multiple domains?

Question

How to design database and organize data if I have two or more domains and one database? All sites for e-commerce purpose and one good can be for sale on each site. So I have only two ideas:

1. I need to create one more field (site_id) in almost every table and duplicate data.
2. I need to create one table with site_id information for all other fields from other tables.

Both of ideas have a huge minuses. Any ideas? Thanks.

Answer 1

It is likely that there are a small handful of tables somewhere in your schema that link to all of your other tables. It is these tables that you need to put the site_id's in, not every table in your database.

For (a highly contrived) example, if my schema includes a Customers table, an Invoices table, and an Invoice Line Items table, I don't need site_id's in all three tables. I only need a site_id in the Customer's table.

Answer 2

I think one of the approaches used by Wordpress and Drupal is to prefix tables with a name:

dom1_Customers
dom2_Customers

This way the tables don't grow out of proportion and you don't have to maintain an extra index of site_id. That said, your code has to compensate for it, which can require some reinstrumentation (and stored procedures are basically out without some nastiness).

Answer 3

This is a classic problem when building multi-tenant systems. I've heard a few different opinions on this matter, but they basically break down into two camps:

1. Use the tenant id (site_id in your case) on every table which contains data for a specific tenant. Advocates of this approach cite ease of identifying the tenant that data belongs to as a primary benefit with implications for how the data is archived (viz. different tablespaces for different customers).

2. Use the tenant id only on high-level tables. Advocates of this approach typically describe the benefits being a cleaner database structure.

I'n not a fan of creating different physical tables for the same type of data from different customers. There are a number of unfavorable consequences to this:

• It becomes difficult to create a coherent object model via an ORM tool
• This approach doesn't scale well with large number of customers -- If you have 70,000 customers that must be serviced from a single database, you'll have 70,000 sets of tables.
• Table names must be generated dynamically for SQL statements.

Answer 4

My preference is to create mapping tables where needed. Think that a Product can exist for Site 1, Site 2, etc. The product details don't change across the sites. The products price might though! In which case the Prices table might need the SiteID and the ProductID where ProductID may be replicated for each entry across the different sites. This could be said for users too except that users may find this to be "big brother" in feeling. So while this might work for customers I generally suggest that customers have different accounts across different sites! Sometimes what will physically work doesn't mean that it logically will work. Put the SiteID where you will need it rather than just haphazardly putting it every where. Keep in mind that you may need this SiteID in places beyond where you application needs it...think about offline querying too. Having to do 5 joins to filter by the SiteID will suck! Maintaining the indexes is better than having to hunt for the filter!

With regards to horizontal partitioning by way of separate tables with similar names...use SQL Server 2005 and up. It has features for partitioning so that worrying about data size is no longer an issue.

Answer 5

Is multi-database out of the question? It seems to be the easiest and the cleanest one, you cannot possibly mess one tennants data with another one's.. You are gonna need one Master database for tenant info, and one db for each tennant.