Mysql – Database structure for a system with multisite

Architecturedatabase-designdatabase-recommendationMySQL

The system I'm working is structured as below. Given that I'm planning to use Joomla as the base.

enter image description here

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 ?

Best Answer

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.

Related Question