[Tfug] More RDBMS stuff

[Terence Rudkin]

On 9/5/07, Bexley Hall <bexley401 at yahoo.com> wrote:
> Hi,
>
> Hopefully a *simple* question re: PostgreSQL
> (or, any other "enterprise class" RDBMS?).
>
> I understand the concept/purpose behind
> TABLESPACEs.  It adds a capability to the
> DBMS that you can't really *get* in any
> other way!
>
> But, I am having a hard time trying to
> understand the need for "DATABASEs" and,
> in particular, "SCHEMAs".  Nearest I can
> tell, they are just "convenience notions"
> that don't *really* add any new capability
> you can't get using other mechanisms (?).
The conscept of both multiple DB and SCHEMA is a bit arcane.
postgreess carries this from its ingress days.   Most other rdbms have
an environment that is one db and many schema if needed.

>
> It *appears* that they just allow you to
> partition the namespace.  Is there some
> other benefit to using / NOT using them?
>
> Since I tend to like thinking hierarchically,
> my first inclination is to group components
> of particular "applications" into DATABASEs.
> I.e., an HVAC database for environmental
> control stuff, an IRRIGATION database for
> stuff related to watering the yard, an
> ADDRESS database for "contact list" stuff,
> etc.
>
> And, have no real "need" for the concept of
> SCHEMAs (at least based on *my* understanding
> thereof).
Yes SCHEAM is wher the partioning of groupings should take place
>
> However, PostgreSQL appears to lock you into
> a single database -- as a "sandbox", of sorts.
> So, references to tables in *other* databases
> are prohibited!  :<  Of course, this is NOT
> what I want.
>
> Epiphany!  Schemas give me the partitioning
> that I desire without the "electric fence"
> between these partitions (that the database
> approach enforces).
Right.  Back in the day of BIG boxes the way to provide seperation was
with different db and each of those had many schema.  To day if I
needed another db I may place it on a different box not a different
area of the exiating system, as it were.
>
> Is *this* the sole point of schemas?  To
> allow the namespace to be artificially
> subdivided while retaining access to objects
> in other schemas in the same database?
More then just namespace, thought thai is a big part.  It can provide
function partitioning. and acess partitioning.  I can have a scheam
that provides on methosd to access data in other schhemas and not have
any table itself.  I could have one schema with data and one with
methods.   The only access to the data that is provided to end users
is through the second schema
>
> (Uh, d'uh?)
>
> Aside from the added *naming* complexity that
> this necessitates (yeah, I know about schema
> search orders), are there any other downside
> issues, here that I'm not smart enough to see?
My advvise is create one DB and use SCHEMA as needed.
>
> Can anyone point to a good *modern* text
> covering these issues?  I have some of
> Date's classic writings but I think they
> predate many of these concepts :<  The
much of ingress and the 'older' databvase implemented the concept of
both  a DB and SCHEMA this is not seen much anymore  so I am not sure
of any not PostgerssSql book that would help.
> single PostgreSQL text that I have is so
> laughable that the man(1) pages are more
> worthwhile.  :<  And, MySQL seems to lag
> PostgreSQL significantly so I doubt they
> address these issues/features, yet.  :<
> My Oracle texts are written as "references"
> so only *remind* folks of things they
> already *know*!  <frown>
Informix books follow the same concept as postgress so maybe something
related to informix may help.
>
> Thanks!
> --don
>
>
>
> ____________________________________________________________________________________Ready for the edge of your seat?
> Check out tonight's top picks on Yahoo! TV.
> http://tv.yahoo.com/
>
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org
>