I am sorry for my newbie like question but I'm a newbie to PostgreSQl and schemas. I've got a difficult time grasping the objective of schemas in PostgreSQL.. as well as in general. Do you know the potential use-cases for schemas?
It I've states that schemas are just like sites that can not be nested. OK, so I'm guessing schemas are a way to group tables inside a db. It doesn't address how this really is implemented, nor will it mention the possibility uses aside from one trivial example (next paragraph).
An Advantage &lifier Potential use situation #1
So far I've only understood one (apparently trivial) help to schemas. This benefit being that you could have multiple tables with similar title so that as lengthy as each such table is within another schema, there won't be any conflict since the namespace qualifier (the schema title) may be used to address the specific table preferred.
I do not really realise why you'd have multiple tables of the identical title to start with. I'd think it is really an very rare situation, the paperwork stumble upon in my experience as though schemas should be utilised by everybody. Getting multiple tables of the identical title appears like poor project management software in my experience and permitting for such bad practice does not appear like value. Ultimately you're just permitting for any mess to become made.
The only real scenario I'm able to think about enabling you to have title conflicts of tables that ought to be permitted is if you have a category that's learning SQL and also the school IT admin wants each student to have the ability to create tables of the liking. Obviously, the question i believe is the reason why does not the admin just create 1 db per student instead of 1 db for those and 1 schema for every student? Q1: Why?
What exactly are another use cases that demonstrate the advantage of schemas?
IMPLEMENTATION - OR - Character OF
I am also unclear about the implementation/character of schemas.
Let's think that we've 1 DB that has 3 schemas. 1 schema per user as a result:
user1='admin' -- tableA, tableU, tableJ, tableK,
user2='joe' ------ tableU, tableJ,
user3='kate ----- tableU, tableK.
Within the above example, my intention is perfect for tableU to become shared between your customers (accross the schema joe &lifier schema kate). They should not obtain own stand-alone copy on the table, they ought to share common use of this table. This kind of usage is sensible in my experience. Customers ought to be adding/modding/potentially getting rid of records... not adding/modding/getting rid of tables. I don't know however that you could do with PostgreSQL schemas. Q2: Basically desired to share tableU when i referred to above, would schema joe &lifier schema kate each get thair own copy on the table or can one specify they shouldn't obtain own copy and rather just share use of a current table?
tableJ may be the same as tableK... with the exception that Kate can't use tableJ and joe can't use tableK. This appears to become the essence of schemas in my experience based on my limited knowledge of schemas. Q3: What's the reason for creating a copy of the table for every user? The Two tables have a similar structure (posts and constraints) which is a total waste of space for storage to create a standalone copy of these a table for every user. I'd also feel that it might be nightmare if each user had their very own copy of each and every table. We'd be tossing key concepts like normalization the window. It might be an unmaintainable mess. I believe each user ought to be adding/modifying/ getting rid of records to some common table inside a common DB.
I have seen folks my Google searches about schemas create a separate schema+tables for every online customer for their website. They've like 100,000 schemas. Q4: What shall we be held missing here? This appears extreme as you would expect. They must be adding record(s) to straightforward table(s) for every customer not making schemas and tables for every customer. This only contributes to my confusion.
Anyway, I really hope I have mentioned the important thing points of my confusion clearly enough.
What I am searching for is:
(1) To obvious up the advantages of schemas via realistic use situation good examples.
(2) To obvious in the implementation detail of schemas.
Potential use situation #2
Basically understood Neville K properly, he suggest like a use situation:
1 DB that has 3 schemas. 1 schema per user as a result (username==schema_title in ex.):
user1='admin' -- tableA, tableLogin, tblFin1, tblFin2, tblFin3, tblPrj1, tblPrj2, tblPrj3.
user2='joe' ------ tableLogin, tblFin1, tblFin2, tblFin3
user3='kate ----- tableLogin, tblPrj1, tblPrj2, tblPrj3.
Here, joe is within Fin department, and Kate is project manager. The application that joe uses is fixed to invest in related tables, the applying that kate uses is fixed to project management software tables. This restriction is enforced via their user title being associated with a schema that is associated with searching path (enforced at DB level).
Q5: Wouldn't exactly the same restriction maintain place without schemas? What tables are for sale to which application is really a purpose of what tables were set for use through the application at that time the applying is made through the dev team. No? Or shall we be presuming that people are utilizing out of the box programs that you simply indicate a db and that we are worried the application can't in the configurations be limited to certain tables (which restriction locked having a password inside the off-the-shelf application)?
Potential use situation #3
Basically understood Catcall properly, he suggest like a use situation:
A predicament where you want to rent/lease the expertise of a database server located on 1 physical system to multiple clients requiring database service. As a result, a multi-tenant like scenario arises. You now can pick to possess:
(1) Another database for every tenant (customer)
-Safer and convenient but could support less tenants per system.
(2) One shared database having a schema for every tenant (customer)
-Less secure and slightly less convenient but could support more tenants per system.
Potential use situation #4
Basically understood Scott Marlowe and Catcall properly, another use situation could be:
Using schemas to isolate the brand new content of designers throughout development. Later can merge try to another schema.