Help with DB Design on server.

[Anon]

I have a virtual server setup (15 domains on each server). I have a web based application that has a large portion of common data for all domains but some tables that contain custom data. The custom data tables do use keys from the common data. Any suggestions on how to design a single or multiple databases that will allow me to maintain common DB tables as well as custom tables for each domain. This may even cross servers so ideas on DB synchronization is very needed. I am hoping for some really thought out answers hopefully based on experience. Thanks to all.

ps. Also, important will be the desire to prevent multiple domains from being down at the same time if a common data source is down.

[[deleted]]

So basically you just want to setup a few redundant failsafe database servers?

Which database server type are you using?

[amcgrath]

Randy,

I'm not sure if this helps, here's a situation I found myself last year and my solution:

Scope: Rewrite 20 web sites that share common data, but also have data specific to each domain.

Restrictions:
I had to keep all of the data in one database and I also had to keep all of the scripts (Cold Fusion in this example) in the same location... meaning, index.cfm for one was index.cfm for them all.

Solution (brief):
Slice the database/code into domain specific groups. On the database end, this meant having a domain specific key (as well as a "realm" -- stupid word we used -- specific key, realm being the type of site it was) that allowed for data in table X to mean "this" to one site, while meaning "that" to another.

If that's what you're thinking, let me know, if not, sorry for wasting your time.

[Anon]

This is a Unix server with PHP and Mysql code. Apache.

Primarily, I am thinking it is better to have custom information for each domain (ie. accounts, colors, etc.) in their own database. I thought this would reduce table size and be faster, and also separate the info that I provided to all customers from the data that they provide. I guess I either create a separate DB for each client and have some synchronization method or a use one database per server and somehow synchronize those when needed. (That seems like too much duplication.) If my limit is 15 domains per server I will need some method for synchronizing the databases (or is there a way to efficiently reference a DB on a single server from php code running on another server. I am sure there is but will it be fast?).

Any tips on synchronizing MYSQL or should I just write some php code.