Subject: Re: Schema's vs Single Database with prefix on tables
To: Evan Bauer <evanbauer@mac.com>, Tim Cross <theophilusx@gmail.com>
Cc: Joao Ribeiro <joao.ribeiro@foursource.pt>, pgsql-admin@postgresql.org
References: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt>
 <871sa25nys.fsf@gmail.com> <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <f9f9418d-ce78-c7ea-960a-c83d34d3ab4d@catalyst.net.nz>
Date: Mon, 10 Sep 2018 17:28:53 +1200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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
>