Re: Schema's vs Single Database with prefix on tables

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(at)evanbauer(dot)com <mailto:eb(at)evanbauer(dot)com>
> +1 646 641 2973
> Skype: evanbauer
>
>
>> On Sep 9, 2018, at 18:02, Tim Cross <theophilusx(at)gmail(dot)com
>> <mailto:theophilusx(at)gmail(dot)com>> wrote:
>>
>>
>> Joao Ribeiro <joao(dot)ribeiro(at)foursource(dot)pt
>> <mailto:joao(dot)ribeiro(at)foursource(dot)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
>