Re: Understanding Schema's

On Tue, 14 Dec 2010 19:20:37 -0500
Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com> wrote:

> On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> 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