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 yourthe size was enormous and yourthe relations so complex and redundant, then my second and current approach is as follows:
People_Schema
containing general tables liketbl_person
,tbl_city
,tbl_ocupation
, etc.Arts_Schema
containing tables liketbl_art_center
,tbl_enrollment(art_center_id FK, person_id FK)
, etc.Sports_Schema
containing tables liketbl_sport
,tbl_team
,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.
Thanks in advance and excuse my bad english.
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.