I have an application where each user has their own database. I am thinking of splitting into 2 or more sets of severs to distribute the load.
The database that a user has is determined by the URL subdomain
for example:
abc.phpppointofsale.com uses the abc database.
If I add another database server; there are 2 options:
Have a database lookup table on one of the database servers that maps subdomain to database server.
make the url something like abc-server2.phppointofsale.com (looks odd to user)
The pros and cons that I see is that the 1st method requires that the main server must be up to access application and requires an extra query (but this is fast anyways).
I am leaning towards options 1 because if I need to move databases to other servers the url doesn't change. Is there a better way to do option 1?