My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this organization needs an unique central Web app.
My first intent was modeling only one database but the size was enormous and the relations so complex and redundant, then my second and current approach is as follows:
People_Schemacontaining general tables like
Arts_Schemacontaining tables like
tbl_enrollment(art_center_id FK, person_id FK), etc.
Sports_Schemacontaining tables like
tbl_team_member(team_id FK, person_id FK), etc.
- And among others schemas in future growing.
The question is: Does this last approach is a good idea? or Does will be a hell for join queries between schemas or others painful implications that I have not seen?, considering multi-tenant expectatives, RESTfull APIs, DreamFactory Backend as a Service and Amazon EC2 Server.
Edit: My approach was inspired by this statement taken from official Postgres docs: If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.