1

I'm building a kind of "multi site application" based on wildcard subdomain. And every site will of course have the same files but also the same database structure. But it's not necessary for each and other to share tables. They will never use each other's datas.

So the best would be if they had their own tables, in their own databases. But then it's more difficult to develop the service since I have to do all the changes to all the databases when I change something.

So the best would be if every database could mirror each other in the terms of structure.

Is this possible? Or what is the most recommended solution for this situation?

Peter Westerlund
  • 123
  • 1
  • 1
  • 7

1 Answers1

2

The problem lies on the border of the DBA and the software engineering.

There are multiple solutions, all depending on, exactly how close database structures are you wanting.*

As the site databases (thus, also the frontend engine using your DB) are more closer and closer to eachother, so should you use the more and more closer options also for the DB.

Likely (but not sure), that you will have at least a little part of your database, which will be the same, for example the user data.

Your options depend also on the applied database. For example, mysql has no schemas, but knows the cross-database queries. PostgreSQL can make cross-db queries only by extensions, but you can configure schemas for it.

The options, in the decreasing order of "close-ness":

  1. Having a multi-tenant DB. It essentially means, you will have the same DB for all the sites. On this way, your database structure will be more complex, with foreign keys directed by each per-db record to the corresponding db data. It makes also your queries more complex. Note, queries using many "WHERE"-conditions become quickly sub-optimal.

  2. Having a multi-schema solution: using a single cross-site schema, added by a per-site schema for all the sites. This can't be done with MySQL, but it would work well with PostgrSQL.

  3. Having a single cross-site DB and a per-site DB for all the sites. This either requires to have cross-db queries, which may be hard to synthetize with foreign keys. However, you may have different database versions in the per-site dbs.

  4. You may have also entirely different databases. In this case, you will likely need to have some replication/synchronization, either by triggers or by scripts.

Changing your decision in a late phase of the development, or during the maintenance, is a major refactoring work, so decide wisely as early as possible.

Note, if you want to use different software versions simultanously, you will likely need to have same database versioning and (1) falls out.

*A remark: the Stack Exchange has the same situation: they have a multi-tenant website, all with nearly the same engine and probably with the same database format. Probably they decided behind (3).

peterh
  • 2,077
  • 8
  • 28
  • 40
  • Well, I was hoping for a smart way to have option 4 and sync in a easier way. Hoped that it was like a built-in solution in MySQL. But apparently there isn't. So can't do option 4. So I have to make the best out of option 1. And there will in some tables be A LOT of rows to go through for queries. So I need the fastest way to do this. Of course the site_id will be indexed. But should I also do some VIEW-solution or is that slower than just manually add the "WHERE" to every query on the site? – Peter Westerlund Jun 10 '18 at 16:24
  • @PeterWesterlund All the solutions results increased complexity, compared to an ordinary single website, there is no easy work-around for that, because also the problem becomes increasingly complex with the multitenancy. MySQL can easily do cross-db queries (you can simply refer tables as dbname.tablename), you can have even cross-db foreign keys. MySQL is typically good in linear optimization but it is bad algorithmically. And it has no materialized views (ref), which closes out the quick processing of queries on large tables. – peterh Jun 10 '18 at 16:46
  • @PeterWesterlund I think you need either use some extensive caching (this is what facebook does), or switch to a more advanced db (this is what most large DB-oriented project does). – peterh Jun 10 '18 at 16:47
  • Isn't there any way to have a central function where all queries go though that adds the "WHERE site_id" string or parameter to INSERT and so on.. – Peter Westerlund Jun 12 '18 at 09:50
  • @PeterWesterlund I think, at least a little part of your data will be probably common between the databases. If your dbs will be surely absolutely disjunct, then I would use (4), but I don't think that it is likely. – peterh Jun 13 '18 at 08:58