Thread: What's the point of creating a schema?
(Sorry for asking so many questions, but I can't find answers in the documentation.) Why would one bother to create a schema (with "create schema blah blah")? Isn't a database enough to keep tables, etc. separate? I can see that schemas *may* be useful for complicated designs having a few hundred tables (i.e. one in which name collisions could become a common problem), but I can't think of any other situation. Thanks! kj
--As of Saturday, September 10, 2005 1:47 PM -0400, kynn@panix.com is alleged to have said: > Why would one bother to create a schema (with "create schema blah > blah")? Isn't a database enough to keep tables, etc. separate? I can > see that schemas *may* be useful for complicated designs having a few > hundred tables (i.e. one in which name collisions could become a > common problem), but I can't think of any other situation. --As for the rest, it is mine. Why run several copies of Postgresql when you don't have to? A schema allows you to say 'All these tables belong to this project', and have it clear. And since you can limit users to one schema (or to just some tables in a schema!), they don't have to see anything outside it. Then you can optimize your storage and logic separately, and people don't even need to know that they all are working with the same database backend. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
kynn@panix.com <kynn@panix.com> schrieb: > Why would one bother to create a schema (with "create schema blah > blah")? Isn't a database enough to keep tables, etc. separate? I can For instance: I have several locations in our company. Now i want to have individual tables for every location, and a summarized view over all locations. Thats why i create a schema for every location with individual tables, and a summarized view over all locations. Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
On Sat, 2005-09-10 at 13:47 -0400, kynn@panix.com wrote: > Why would one bother to create a schema (with "create schema blah > blah")? Isn't a database enough to keep tables, etc. separate? I can > see that schemas *may* be useful for complicated designs having a few > hundred tables (i.e. one in which name collisions could become a > common problem), but I can't think of any other situation. Here's one: Schemas provide a way to solve a problem in setting up accounting systems - that of the separation of duties. This is something that ought to be enforced in any organisation that employs sufficent accounting staff to make it practicable. The principle is that the people who deal with sales or purchases should not be the same people as those who deal with receiving and making payments; when this can be enforced, opportunities for theft and fraud are significantly reduced. If an accounting system is implemented in a SQL database and those accounting functions are organised into different schemas, it is much simpler to let the database enforce the separation of duties, by refusing access to information to those who do not need to see or change it. Of course, the same effects could be attained by setting permissions on individual tables, but their distribution into schemas makes a much simpler organisational problem. Use of schemas also helps the database designer to think in advance about the kind of access that different groups will require and it provides a framework in which to set up the system. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html