CODEWITHSUNDEEP
mysqldatabasemysql-workbench
Karl Johan Vallner
Karl Johan Vallner

Reputation: 4310

MySQL Workbench intertwined schemas, that need to be separately developed

I am developing a small CMS that my clients are going to use. Each of those clients are also going to have custom SQL tables built on-top of the core-CMS SQL data/table structure, to support their separate needs.

Now, when using MySQL workbench, I would like to:

1) Keep separate schemas for each client on the MySQL workbench.

2) Be able to easily distribute core-SQL changes between all the clients schemas. (No need to distribute client specific changes!)

3) keep relations between the core tables and the client specific tables, because they are going to exist in the same schema later.

-> I.e. my core SQL will have users and one of my (just one, not the others) client's database also needs to have users. Thus it would be convienient to have the client's users in the core SQL-s users table.

-> All media files should be located in the core SQL "media" table.

Can someone provide me with at least some breadcrumbs on how to manage multiple databases, in which the core tables are the same, but which all have some parts built on top of them (Aka. there exist logical relations between the core and client specific tables)? Is there even a good solution for this?

Upvotes: 1

Views: 80

Answers (1)

David Soussan
David Soussan

Reputation: 2736

Well, it is quite simple to query across schemas so keep the core tables in core schema and clients' tables each in their own schema. You would then select across schemas by eg:

SELECT u.* FROM core.users u;
SELECT c.* FROM client.table c:
SELECT m.*, t.* FROM core.media m INNER JOIN client.table t ON m.table_id = t.id;

etc.

Upvotes: 1

Related Questions