CODEWITHSUNDEEP
phpmysqldatabasedatabase-designarchitecture
Techie
Techie

Reputation: 45124

Database structure for a system with multisite - Database & PHP

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.

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 ?

Upvotes: 1

Views: 928

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

The problem you describe is "multitenancy" - it's a fairly tricky problem to solve, but luckily, others have written up some useful approaches. (Though the link is to Microsoft, it applies to most SQL environments except in the details).

The trade-offs in your case are:

  • Does the data from your hotels fit into a single schema? Do their "vacancy" records have the same fields?
  • How many hotels will there be? 3 separate databases is kinda manageable; 30 is probably not; 300 is definitely not.
  • How large will the database grow? How many vacancy records?
  • How likely is it that the data structures will change over time? How likely is it that one hotel will need a change that the others don't?

By far the simplest to manage and develop against is the "single database" model, but only if the data is moderately homogenous in schema, and as long as you can query the data with reasonable performance. I'd not worry about putting a lot of records in MySQL - it scales very well.

In such a design, you'd map "portal" to "hotel" in a lookup table:

PortalHotelAccess

PortalID   HotelID
-----------------
A          X
B          X
B          Y
C          X
C          Y
C          Z

Upvotes: 2

Stan
Stan

Reputation: 8768

I can suggest 2 approaches. Which one to choose depends from some additional information about whole system. In fact, the main question is whether your system can impersonate (substitune by itself, in legal meaning) any of data providers (x, y, z, etc) from consumers point of view (a, b, c, etc) or not.

Centralized DB

First one is actually based on your original scheme with centralized API. It implies a single search engine, collecting required data from data sources, aggregating it in its own DB, and providing to data cosumers.

This is most likely a preferrable solution if data sources are different in their data representation, so you need to preprocess it for uniformity. Also this variant protects your clients from possible problems in connectivity, that is if one of source site goes offline for a short period (I think this may be even up to several hours without a great impact on the booking service actuality), you can still handle requests to the offline site, and store all new documents in the central DB until the problems solved. On the other hand, this means that you should provide some sort of two-way synchronization between your DB and every data source site. Also the centralized DB should be created with reliability in mind in the first place, so it seems that it should be distributed (preferrably over different data centers).

As a result - this approach will probably give best user experience, but will require sufficient efforts for robust implementation.

Multiple Local DBs

If every data provider runs its own DB, but all of them (including backend APIs) are based on a single standard, you can eliminate the need to copy their data into central DB. Of course, the central point should remain, but it will host a middle-layer logic only without DB. The layer is actually an API which binds (x, y, z) with appropriate (a, b, c) - that is a configuration, nothing more. Every consumer site will host a widget (can be just a javascript or fully-fledged web-application) loaded from your central point with appropriate settings embedded into it.

The widget will request all specified backends directly, and aggregate their results in a single list.

This variant is much like most of todays web-applications work, it's simplier to implement, but it is more error-prone.

Upvotes: 1

Related Questions