From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: 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: Mon, 10 Sep 2018 17:28:53 +1200
Message-ID: <f9f9418d-ce78-c7ea-960a-c83d34d3ab4d@catalyst.net.nz> (raw)
In-Reply-To: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com>
References: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt>
<871sa25nys.fsf@gmail.com>
<7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com>
Another option is to embed the domain id (assuming there is one) into
each table's primary key (i.e compound primary key) and thus use 1
schema and 1 database, without the need to dynamically change table
prefixes.
regards
Mark
On 10/09/18 14:13, Evan Bauer wrote:
> 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 <mailto:eb@evanbauer.com>
> +1 646 641 2973
> Skype: evanbauer
>
>
>> On Sep 9, 2018, at 18:02, Tim Cross <theophilusx@gmail.com
>> <mailto: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
>
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: mark.kirkwood@catalyst.net.nz, evanbauer@mac.com, theophilusx@gmail.com, joao.ribeiro@foursource.pt
Subject: Re: Schema's vs Single Database with prefix on tables
In-Reply-To: <f9f9418d-ce78-c7ea-960a-c83d34d3ab4d@catalyst.net.nz>
* 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