Mysql – Structure database(s) dealing with different time points

That's a long question.

First off, my current project (I'm the database guy, there are MMO engine experts to deal with that) is a form of MMORPG based on an off-the-shelf engine. Volumes would be like Eve Online" or "World of Tanks" volumes.

Now for an orthogonal short answer:

• separate DB and Engine completely
  Don't mix and match because of hardware optimisations
• hardware: DB and engine servers will be way different specs
• design your database normally

There is a whole lot more of course, but I'd suggest you're over-thinking the problem and shooting yourself in the foot. I'm simply applying the same techniques to my MMO that I used in Investment Banking because IMO most high volume systems should converge to the similar architecture

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.