How to build the database for subdomain multisite? 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?
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":


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.
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.
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.
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).