I have multiple different “project manager” apps as: Work Projects app manager, Home Projects app manager,...
Each app has its own DB i.e separate ER diagram. Each app has it's own [projects] table(since they are all some kind of project management apps).
I want to add General Projects Manager app that will keep track of all specific projects.
One General Project can contain multiple specific projects.
1st app: Work Projects
2nd app: Home Projects
3rd app: ...etc.
I need general project manager app, that will keep track of all projects, as their: names, statuses, dates, etc.
How to design the general/central “all_projects” table and how to relate it to all this [work/home_projects] tables?
Project tables are mostly different (just few common columns), and each of those [work/home]Project tables represents an entity type that is part of a separate ERD (i.e. separate app).
I read Q & A about a supertype-subtype structure and it helped. My case is nonexclusive but JOINS (and VIEWS) seems to stay the same. One problem is that primary keys of subtype tables are already set, and supertype tbl.General_Projects should now be created. I guess that subtype tables primary keys should be updated (with Cascade).