Skip to main content
Removed chattiness.
Source Link
Colin 't Hart
  • 9.4k
  • 15
  • 36
  • 44

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 like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

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 your size was enormous and your relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

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_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

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.

added official documentation link
Source Link

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 your size was enormous and your relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

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 your size was enormous and your relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

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 your size was enormous and your relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.

Source Link

Divide a PostgreSQL database into schemas

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 your size was enormous and your relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_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.