Re: organizing tables?

From: Bob Badour <bbadour_at_golden.net>
Date: Mon, 14 Jul 2003 22:20:09 -0400
Message-ID: <otKQa.829$4P6.136561801_at_mantis.golden.net>


"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:beusc0$1m02$1_at_gazette.almaden.ibm.com...
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:qOCQa.798$8X5.134429842_at_mantis.golden.net...
> > "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> > news:beu2h9$26v6$1_at_gazette.almaden.ibm.com...
> > > "Marshall Spight" <mspight_at_dnai.com> wrote in message
> > > news:OJpQa.56107$H17.18016_at_sccrnsc02...
> > > > Hi all,
> > > >
> > > > I've been thinking about the need for an organization principle for
> > > > relational data. Imagine a dbms that supports many users and many
> > > > applications. After a point, the idea that one has a flat namespace
> > > > for databases within the dbms doesn't seem so nice anymore.
> > >
> > > I agree wholeheartedly that there very probably is (or at least would
be
> > in a
> > > future RDBMS) a need for non-flat namespaces.
> >
> > How do you get from wanting no names to wanting hierarchies of names?
<g>

>

> :-)

>
> Did I say I wanted no relvar names? I might of done, but I just want them
to
> be optional, that is all.

>
> > If one has a database name, an application name and a user name to
determine
> > the logical view of the database, how does one arrange them
hierarchically?
> > Why that choice?

>
> Indeed. Why 'application' and 'user' names. Are these important?

Yes, they are important. Role and application version are as well, but I tend to think of versions as identifying different applications.

> Why one
> hierarchy, why not many. Why a hierarchy at all. All good questions.
> To tackle them logically, we would propably want some agreed logical model
of
> 'users', and maybe of 'applications'.

I suggest using one or more candidate keys for namespaces where at least one candidate key is compound. One can drill down a compound candidate key in any order if one wants to pretend the namespace has a hierarchic order.

> > > > What
> > > > if there are hundreds of databases? What if you want to be able
> > > > to import some of the tables from one database and some from
> > > > another, etc., into your person database?
> > >
> > > Logically it is much better to have just one database.
> >
> > What's wrong with taking a projection of two database variables and
> > combining the resulting values using union or join to assign to a third
> > database variable?

>

> Nothing. Especially if those variables are actually Database Valued
> Attributes. However, we have to stop somewhere, and for all users except
those
> working on stuff like data integration, I suggest that one persistent
database
> is all a user ever needs to see (at any one point in time).

For the most part, I agree that users will generally want to temporally separate their interactions with different databases. For the most part, a user accessing the marketing database wants to have confidence that nothing they do will directly change the accounting database. However, some users may have access to both marketing and accounting databases with each of these databases defining only a subset of the users' view. And even users who generally want to access one and only one database may at times want to access both simultaneously (albeit very carefully).

Both marketing databases and accounting databases may have Customer relations where there are subtle differences between them. Further, the marketing database may have multiple Customer relations where a Customer means one thing to a PromotionPlanning application and something subtly different to the CRM or CallCenter application.

> > > Different users can be
> > > given different views of this one database, so they might *think* that
> > there
> > > is more than one database (assuming two database views are completely
> > > isolated), but in the full picture there is just one logical database
> > > supported in any single (or distributed) DBMS. A hierarchical (or
other)
> > > non-flat namespace could facilitate such a system.
> >
> > What is so flat about current namespaces? Do you see any strong argument
for
> > arranging namespace components in hierarchies?
>
> Not necessarily hierarchies, but we would want some way of managing names
by
> some grouping mechanism or other when we start having databases with
millions
> of relvars.

Millions of relvars seems extreme in any case. I suggest a compound candidate key provides exactly the sort of grouping mechanism you seek.

> > Does the user name define a
> > subset of the database or does the database name define a subset of the
> > user's view?
>
> Not the former. On the latter, would you want to be able to give a
database
> subset a name. Yes very probably. Would you encourage users to see
different
> subset as different databases. Probably not. Maybe you would just call
them
> database views.

The user name does define a subset of the database through the security function. I suggest that users do think in terms of separate databases like the marketing database and the accounting database, and that databases define subsets of the user's view.

> > > > It seems like you need to have some organizing principle. I hate
> > > > to say it, but the popular choice always seems to be a hierarchical
> > > > namespace.
> > > >
> > > > Anyone have any thoughts or are there any interesting prototypes
> > > > out there? It seems like this idea was the 05/25/2003: Quote of the
> > > > Week on dbdebunk.com. (Which is to say, I think they were
> > > > making fun of the idea.)
> > >
> > > I'm not aware of any particular work in this area that specifically
> > concerns
> > > databases. For me the difficulty would be how much you can make this a
> > model
> > > issue. I.e. are there any logical concerns here or is any design
little
> > more
> > > that a matter of utility?
> >
> > Of course, there are logical concerns here. And when is design ever
little
> > more than a matter of utility? Even aesthetics have utility.
> >
> > I see no utility in forcing the components of a namespace into an
artificial
> > hierarchy.

>

> Agreed.

>
> > Databases and application schemas are subordinate to the user's view of
his
> > or her universe.
>

> OK

>
> > Users and application schemas are subordinate to the database's view of
its
> > universe.
>

> OK
>

> > Databases and users are subordinate to the application's view of its
> > universe.
> >
> > If one has a namespace with database, user and application components,
how
> > does one arrange the components into a single sensible hierarchy?
>

> So, OK a hierarchy is not a good way to go maybe. It would have been a bit
> rich if that had been our suggestion.
>
> But what are we really talking about here. Naming scope? If my business
has
> 47 definitions of the term profit, all depending on the particular
context,
> then if I spend each day and every day just dealing with one particular
> definition, then I would like to be able to use the plain word 'Profit'
rather
> that 'Gross Profit Net of Sales receipts and Purchase Tax ...' or whatever
the
> corporate wide definition would be of what I know to be 'Profit'

The question then becomes: What defines context? I can make plenty of suggestions: database, application, version, role, user, schema, relvar.

> If it is to do with naming scope, then schema design and evolution are
some
> other things that play a part here..

I suppose one could propose support for versioned database views, versioned application views, versioned role views, versioned user views and versioned schema views, but I tend to think of versions applying mostly to applications.

Alas, upgrading users is easier said than done. Received on Tue Jul 15 2003 - 04:20:09 CEST

Original text of this message