Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015
    Posts
    6

    Question Answered: Database/Schema Strucutre for Data Shared Between Projects

    I have started a company and, over time, will be releasing web applications which will have some overlap around the data on which they rely. E.g. a postal/zip code look-up, an affiliate partner's product catalogue, etc.

    Because I have a fresh slate I am thinking about putting this project-agnostic data nature in its own database which will be shared across apps, either via direct database access and/or through a RESTful API.

    Assuming this is good application design choice (please correct me if I am wrong), would it be appropriate to have all data in a single database but divide it into heterogeneous schemas?

    E.g.
    Code:
    ☰ Databases
      ▯ shared_data
        ❖ Schemas  
          ◇ public
          ◇ zip_codes
          ◇ widgeco_products
    Or, would it perhaps be better to divide by databases, something like:
    Code:
    ☰ Databases
      ▯ zip_codes
        ❖ Schemas  
          ◇ public
      ▯ widgeco_products
        ❖ Schemas  
          ◇ public
    I just fear the latter option could be a pain in terms of connection strings. One other option could perhaps be a hybrid approach where the first database is used for a single point of connection and it sucks in data from the other sources using foreign data wrappers?

    I am not too new to relational databases but have only been using Postgres for about a year and just brushing the surface of its capabilities at the moment so any insight would be much appreciated

  2. Best Answer
    Posted by shammat

    "Postgres doesn't allow cross database queries. Given the names of the schemas, I guess you do probably queries that use tables from multiple schemas. So multiple databases is not an option. Schemas give you namespaces to divide your tables into which is usually good enough.

    Neither multiple schemas nor multiple databases will have any impact on performance.

    I would start with multiple schemas in a single database. If you later decide you actually have an advantage of using multiple database (because you can put them on different physical servers for example) you can still do that."


  3. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Postgres doesn't allow cross database queries. Given the names of the schemas, I guess you do probably queries that use tables from multiple schemas. So multiple databases is not an option. Schemas give you namespaces to divide your tables into which is usually good enough.

    Neither multiple schemas nor multiple databases will have any impact on performance.

    I would start with multiple schemas in a single database. If you later decide you actually have an advantage of using multiple database (because you can put them on different physical servers for example) you can still do that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Nov 2015
    Posts
    6

    Thumbs up

    Great, that is what I was thinking with schemas used for namespacing... with your info it seems clear the obvious choice is to go with a single database and to only introduce further complexity if required.

    Thanks for the help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •