On Tue, 14 Dec 2010 19:20:37 -0500 Carlos Mennens <carlos.mennens@xxxxxxxxx> wrote: > On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake <jd@xxxxxxxxxxxxxxxxx> wrote: > > You can cross query a schema but not a database. > > > > So you can create: > > > > create table fire.foo() > > create table ice.foo() > > > > And they are isolated from each other physically and logically but you > > can query them both: > > > > SELECT fire.*, ice.* join on (id) > > Why would anyone in a random scenario want to have independent > schema's to cross query? I'm just trying to see how this would be > useful in any scenario. SELECT * FROM public.userinfo JOIN dmv.license_info USING (user_id) JOIN concealed_carry.license_info USING (user_id) JOIN medical.license_info USING (user_id); ... as a (somewhat contrived) example. Since all these different things (motor vehicles, personal firearms, and medical doctors) require licensing, one way to organize the data is to have a license_info table for each type of license and put them in schemas. In this case, it's simply a namespacing tool. However, the application I code for at work has about 300 tables, and that namespacing comes in pretty damn handy. (in our case, the medical schema also has a bunch of other tables related to medical data) Schemas can also be used for security purposes. As an example, we have a temp_table schema, and the general users are allowed to create tables in that schema, but nowhere else. This makes it rather easy to have a cron job that comes through and cleans out old temp tables and temp tables that don't conform to our naming conventions, since we don't have to worry about it dancing around tables that aren't temp tables. When you're starting out, an easy way to think of schemas is like directories on an operating system. It's not an exact analogy, but it helps one to understand the purposes, benefits, etc. -Bill -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
