Re: Schema best practices

On Fri, May 15, 2009 at 6:44 AM, J. Carlos Muro <murojc(at)gmail(dot)com> wrote:

> Hi! When I have more that one project, which is better, in terms of
> efficiency/organization?
>
> * Case A: Create one database, and for every project a new schema (in the
> oracle style) containing project's objects.
>

This method is nice because if you ever need to report across projects,
it's fairly simple to do as a superuser. I have an example where I use
multiple 'trac' instances, each with its own schema in the same database.
When I need to report across them all, I have a view with 'union all'
across the tables I'm interested in.

The obvious problem here is security, but as long as you are diligent,
you shouldn't have any real problems.

>
> * Case B: Create a database for every project (in the mysql style), not
> using schemas (letting objects lay in 'public').
>
> - B.1: user postgres as owner of the database and add a new user with all
> privileges in that db, then use this user to create objects.
>

I like this method when you are more concerned about compliance and
security, it gives a bit simpler 'separation of data' without having to be
*as* diligent about security. The 'cross-project' reporting is still
possible (via dblinks) but is slightly more complicated.

>
> I guess there is not big difference, and maybe this is more a matter of
> preference, or.. "taste"? From your experience, please, can you point me
> your best practice in this matter?
> Thanks for you opinion!
>

Personally, I like to keep 'loosely similar data' in the same database
(multiple schemas) because it make (in my brain at least) logic sense to
keep them together and easily be able to report across it. If You're
talking engineering data vs. marketing email blasts, I like to keep that
separate just to avoid confusion in my own head.

Good luck!

--Scott