Dont understand schema's

[hangbill]

Good afternoon
I dont understand schema's

Cant all the organizational needs of a single database be handled by related tables. For example consider tblEmployee, with fields employee_name and salary. If you need to isolate the salary details, you can create another table, tblSalary, and relate the two tables 1-1. Then set approproiate permissions on tblSalary. Or would it be better to use a new schema for this.

Also if there is a new schema for salary details, how do you relate the tables from the different schema's?

Or are schema's used for completely different things?

Thank you.

 

[ceco]

have you read this

http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html

 

[hangbill]

Yes I have the manual bookmarked and have read the schema sectionit. But still feel I am missing the point of schema's.

 

[oradba101]

Hi, hangbill

The simplest way to define a schema is the objects owned by a particular user.

Regards,



William Chadbourne
Oracle DBA

 

[ceco]

well the schemas idea is (as it is written in the docs) just like the directories ... nothing more or less (may be less, because the directories can be nested)
you can use it the way you like

it is not necessary to use schemas! they are there for convenience when having many (100 for example) tables and just want to group them somehow
(the example you gave for empoyee and salary is better to be in one schema)

the main three types of usage, are written on the page I gave you

I use them in two different ways

for large projects, I separate the modules in different schemas, thus a module can have tables/functions/etc with the same name as other module
the other thing is in hosting environments, a user have usualy access to one database, but wants to install different projects (forums, cms, etc.) and put them in different schemas so that they do not interfer (having tables with the same name)

you can find and other usages ....


oradba101, the definition you wrote is adequate for oracle, but in postgresql schemas are more flexible and by default are not bound to the users

 

[hangbill]

Thanks for this ceco, appreciate it.