Database structure for a system with multisite The system I'm working is structured as below. Given that I'm planning to use Joomla as the base.



a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for reservation.

x(www.x.com),y(www.y.com),z(www.z.com) are hotels where booking are made by users.


www.a.com's user can only search for the booking which are in
www.x.com
www.b.com's user can only search for the booking which are in
www.x.com,www.y.com
www.c.com's user can search for all the booking which are in
www.x.com, www.y.com, www.z.com


All a,b,c,x,y,z runs the same system. But they should have separate domains. So according to my finding and research architecture should be as above where an API integrate all database calls.

Given that only 6 instance are shown here(a,b,c,x,y,z). There can be up to 100 with different search combinations.

My problems,

Should I maintain a single database for the whole system ? If so how can I unplug one instance if required(EG : removing www.a.com from the system or removing www.z.com from the system) ? Since I'm using mysql will it not be cumbersome for the system due to the number of records?

If I maintain separate database for each instance how can I do the search? How can I integrate required records into one and do the search?

Is there a different database approach to be used rather than mentioned above ?
I would recommend keeping everything in one database. It will be easier to manage and administer. If you need to add more domains/clients, you can continue to use the existing instance of the database (no need for new installations). The records that are specific to certain domains/clients will need some kind of indicator. It could be as simple as an client_id field. Installed instances of the application should have their client_id (or multiple values, if they're allowed to search more than one dataset) in their configuration so that every query where it matters contains the proper client_id. 

A possible alternative to having applications configured so the know what to query would be to move that configuration into the database, maybe in a table like this:


portals_hotels
--------------
   portal_id
   client_id

sample data:

portal_id   | client_id
------------|----------
www.x.com   | www.a.com
www.y.com   | www.b.com
www.y.com   | www.a.com