Help with setting up a Database

Question

My site is going to have many products available, but they'll be categorised into completely different sites (domains).

My question is, am I better off lumping all products into one database and using an ID to distinguish between the sites, or should I set up a table and /or DB per site?

Here are my thoughts

SEPARATE DATABASES

• Easier to read from a backend
• Categorised better
• Makes backups more difficult
• If I need to make a change to the schema, it will need to be pushed out to all databases

SAME DATABASES

• All in one place
• Could get unwieldy
• One database will have a massive file size and lookups could suffer

Can someone please offer me some advice on which way is best and why?

Answer 1

As you already say, both options have their pros and cons. Since you're talking about two stores, it probably doesn't matter much.

However, a few questions you might want to ask yourself:

• Will it really be two stores, or possibly more? If more, one database might be smarter.
• Are the products really the same? If you're gonna have to squeeze products in one general database, because they are of a different kind (eg. cars vs. food; the amount and nature of the details you want to store are completely different), then don't; use two databases / tables instead.

The central question is: what is most likely to become more elaborate in the future: the stores, or the products?

Answer 2

If data needs to be shared among all the sites, then it will be recommended to share the same database since data transfer is eliminated. Also data is more centralized.

If data does not need to be shared among all sites, it'll be good to split up one database per site. Talking about difficulty to update table structures, you can just simply record down the database changes (saving the ALTER, UPDATE, DELETE queries in a SQL file) you make for one, and update the other databases with the same SQL file.

Storing in different databases might also help with security. You can set different user permissions for each of the site. and if one gets compromised, you protect the other sites.

Also, you are able to easily maintain and track database when the databases are clearly split up.

Answer 3

I think separate databases will be easier. You can have a quick-start template database from which you can build a new store database. You can even create a common database and contain common tables and list of stores and their databases. After all you can access to any database within the same server using qualified name, observe:

SELECT value FROM CommonDB.currencies WHERE type='euro';
SELECT price FROM OldTownDB.Products WHERE id=newtownprodid;

Answer 4

You didn't give too many details (which makes it difficult to provide a good answer), though the words you chose to use in your question lead me to believe that this is a single application with different "skins".

My site is going to have many products available, but they'll be categorised into completely different sites (domains).

My assumption is that you will have a single web store with several different store fronts: cool-widgets.com, awesome-sprockets.com, neato-things.com, etc. These will all be the same, save for maybe a CSS skin or something simple like that. The store admin stuff will all be done in some central system, and the domain name will simply act as a category name.

As such, splitting the same data into two different containers using an arbitrary criterion (category_ name=='cool-widges.com') is data partitioning, which is an anti-pattern. Just as you wouldn't have two different user tables based on the user name ([Users$A-to-M] and [Users$N-to-Z]), it makes little sense to have two different tables (or databases) for category names.

There is, and will be, lots of code common among the categories: user management, admin, order processing, data import, etc. It will be far more difficult to aggregate the multiple datastores in the common code than it will be to segregate the categories in the store display code. Not only that, the segregation bugs will be much more obvious: the price comparison page shows items from all three stores. The aggregation bugs will be much less: only three of the four stores were updated. This is why it's an anti-pattern.

Side note: yes, before you say that data portioning has its uses (which it does), those uses come in far after performance problems occur. Many serious database platforms allow behind-the-scenes partitioning as not to create a goofy data model.