why use SCHEMA? any real-world examples?
I am in the middle of a project to convert non-schema databases to a
schema-based system. The main reason I am doing it is because I need to
do a join on tables between databases, which can only be done with an
contrib module which does not have all the "features" one might want
(such as use of indexes, etc).
For example:
SELECT a.id, b.name FROM schema1.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema2.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema3.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema4.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
.. etc...
Obviously this prevents replicating "schema_shared" into every database
whenever it gets updated...
I am sure there are many other uses - they seem very flexible to me so
far, but that's what I'm using it for...
- Greg
Show quoted text
I just noticed PostgreSQL's schemas for my first time.
(http://www.postgresql.org/docs/current/static/ddl-schemas.html)I Googled around, but couldn't find any articles describing WHY or
WHEN to use schemas in database design.Since the manual says HOW, could anyone here who has used schemas take
a minute to describe to a newbie like me why you did? What benefits
did they offer you? Any drawbacks?Thanks for your time.
- Miles
Import Notes
Reply to msg id not found: 20041125070352.070565AF655@svr4.postgresql.orgReference msg id not found: 20041125070352.070565AF655@svr4.postgresql.org
Hi!
It's really just a convenience-thing to organize your data in a more intuitive way. We're running several online magazines, each of those with a sort of "entity-database", but each with their own articles. So we've just put the entity-data in the public schema, whereas the magazine-specific data is going in their own schemata. That way we can simply use the very same queries for all of our magazines' applications, just by implementing the magazine-schema as a variable which is set at query-runtime.
Kind regards
Markus
Show quoted text
-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von
Miles Keaton
Gesendet: Donnerstag, 25. November 2004 06:13
An: pgsql-general@postgresql.org
Betreff: [GENERAL] why use SCHEMA? any real-world examples?I just noticed PostgreSQL's schemas for my first time.
(http://www.postgresql.org/docs/current/static/ddl-schemas.html)I Googled around, but couldn't find any articles describing
WHY or WHEN to use schemas in database design.Since the manual says HOW, could anyone here who has used schemas take
a minute to describe to a newbie like me why you did? What benefits
did they offer you? Any drawbacks?Thanks for your time.
- Miles
---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend
Import Notes
Resolved by subject fallback
Since the manual says HOW, could anyone here who has used schemas take
a minute to describe to a newbie like me why you did? What benefits
did they offer you? Any drawbacks?
I suspect the consumer of this feature for whom it would make the biggest
difference would be shrinkwrapped packages. For example, if you have a
database and want to add on a logging package, a billing package, and a CMS
system and you want to store the data in your existing database so you can
integrate them all closely together, then it would be nice to at least keep
the database tables in separate namespaces.
--
greg