Database structure for a system with multisite

Question

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 ?

Answer 1

You can do it with multiple databases, but it will be more difficult to manage multiple schemas (rollouts, upgrades, etc) when there are changes.

The single database design is a kind of multi-tenant (now that you have the right term, you should find a lot of material about these designs) and you would need to work on the design of these grouping structures. It's certainly possible to structure search across tenants very much more easily in a single database. In separate databases, you would effectively have to query across databases. This is possible in mysql, but isn't supported within the SQL language to pick up database names out of a table to do your joins - you'd have to generate dynamic SQL.

So instead of simply:

SELECT *
FROM reservations r
INNER JOIN hotel h
    ON h.hotel_id = r.hotel_id
INNER JOIN site_hotel sh -- this table links hotels to sites and manages your search visibility in one place
    ON sh.hotel_id = h.hotel_id
INNER JOIN site s
    ON s.site_id = sh.site_id
WHERE s.site_name = 'www.a.com'

perhaps you have to use dynamic SQL build up this query as a UNION with queries where reservations is prefixed by the different database names for each database allowed for a web site from a similar hotel/site linkage table.

Like I said, the grouping structures are going to be key, since it sounds like your "tenants" are going to have a little less than simple relations. However, it's certainly possible to build this using appropriate structures once you've thought about the allowed relationships between the hotel and web site entities.

It does get difficult to scale this out where "tenants" get their own servers if they have high load if you have a lot of overlap in your tenant structures, but then again, if your tenants are allowed to see quite a bit of stuff between each other, that points even more strongly to a single database design.

Answer 2

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