pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Evan Bauer <evanbauer@mac.com>
To: Tim Cross <theophilusx@gmail.com>
Cc: Joao Ribeiro <joao.ribeiro@foursource.pt>
Cc: pgsql-admin@postgresql.org
Subject: Re: Schema's vs Single Database with prefix on tables
Date: Sun, 09 Sep 2018 22:13:17 -0400
Message-ID: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com> (raw)
In-Reply-To: <871sa25nys.fsf@gmail.com>
References: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt>
<871sa25nys.fsf@gmail.com>
Joao,
I strongly agree with Tim’s recommendation to create a schema for each “domain.” In addition to the reasons below, the prefix model would seem to condemn your programs and programmers to using dynamic SQL to construct your business logic in a manner that would work for any domain, as the table names for each SQL statement would vary based on the domain.
Schemas are a solid approach to multi-domain design, they should work well for you.
Cheers,
- Evan
Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer
> On Sep 9, 2018, at 18:02, Tim Cross <theophilusx@gmail.com> wrote:
>
>
> Joao Ribeiro <joao.ribeiro@foursource.pt <mailto:joao.ribeiro@foursource.pt>> writes:
>
>> Hello,
>>
>> We are using Postgres and we are with a issue. We have splited our tables by domain and
>> each domain has a separated database, but we are trying to change it to a single database
>> model. We have two options, create the same database in a single schema and add a prefix
>> on each database domain or create a schema for each database (we have about 15
>> different models). In this approach we still want to do some joins and other queries cross
>> schema, but we don’t know what would be the best approach :) Could you help us to know
>> what would be the best approach ?
>>
>> * having just one database with one schema and all domain databases with a prefix
>> * having a schema for each domain (15 domains) with the domain databases (+/ 20 tables)
>> (knowing that we do cross schema queries)
>>
>> _____
>
> It is probably just a matter of taste to some extent. I personally would
> favour separate schemas over tables in same schema with prefixes because
> I think that gives you more flexibility i.e. easier to select all the
> data associated with a domain as it is all in one schema. I also think
> it is easier to define security roles on a per schema basis rather than
> complex roles in the same schema, especially if you add new objects
> etc. Auditing is also less complex.
>
> There are no issues with cross-schema queries/joins etc apart from
> having to include the schema name in the query. Some people don't like
> this because you have to type more and have a longer search_path, but if
> the alternative is table prefixes, the amount of typing is similar
> anyway.
>
> Essentially, the schema gives you a predefined unit which many
> tools/commands understand. If everything is in the same schema, then you
> will often need to replicate some level of this functionality yourself
> and then ensure it is maintained. Extent to which this has an impact
> really depends on your use case.
>
> Tim
>
> --
> Tim Cross
view thread (6+ messages) latest in thread
Message-ID: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com> Permalink: ../7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com/ Also on: postgresql.org/message-id/7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-admin@postgresql.org Cc: evanbauer@mac.com, theophilusx@gmail.com, joao.ribeiro@foursource.pt Subject: Re: Schema's vs Single Database with prefix on tables In-Reply-To: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox