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