Обсуждение: Schema best practices
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.
- A.1: a global user (different of 'postgres') as owner of all schemas
- A.2: every schema with a different user as owner of the schema
* 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.
- B.2: a new user as owner of every database
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!
* Case A: Create one database, and for every project a new schema (in the oracle style) containing project's objects.
- A.1: a global user (different of 'postgres') as owner of all schemas
create database superdatabase;
create role super_user with login password 'x';
create schema project1 authorization super_user;
create schema project2 authorization super_user;
create role super_user with login password 'x';
create schema project1 authorization super_user;
create schema project2 authorization super_user;
- A.2: every schema with a different user as owner of the schema
create database superdatabase;
create role user1 with login password 'x';
create schema project1 authorization user1;
create role user2 with login password 'x';
create schema project2 authorization user2;
create role user1 with login password 'x';
create schema project1 authorization user1;
create role user2 with login password 'x';
create schema project2 authorization user2;
* 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.
create database database1;
create database database2;
create database database2;
create role user1 with login password 'x';
grant all on database database1 to user1;
create role user2 with login password 'x';
grant all on database database1 to user2;
grant all on database database1 to user1;
create role user2 with login password 'x';
grant all on database database1 to user2;
- B.2: a new user as owner of every database
create role user1 with login password 'x';
create database database1 owner user1;
create database database1 owner user1;
create role user2 with login password 'x';
create database database2 owner user2;
create database database2 owner user2;
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!
On Fri, May 15, 2009 at 6:44 AM, J. Carlos Muro <murojc@gmail.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