Re: Principle of Orthogonal Design

From: David Cressey <>
Date: Fri, 08 Feb 2008 15:55:13 GMT
Message-ID: <BX_qj.3$%x3.2_at_trndny06>

"Marshall" <> wrote in message
> On Feb 5, 4:55 am, wrote:
> > On Feb 3, 1:02 pm, Jan Hidders <> wrote:
> >
> > > On 2 feb, 13:50, mAsterdam <> wrote:
> > > [huge snips]
> > > > I think 'named perspective' was just an unfamiliar
> > > > label to a familiar concept.
> >
> > > Yes. The named perspective is the usual perspective where tuples are
> > > defined as functions from attribute names to domain values, and the
> > > unnamed perspective is where they are defined as sequences. The latter
> > > is sometimes preferred in theoretical work because it is closer to how
> > > logicians formalize things (in P(x,y,z) there are no attribute names,
> > > just the 1st, 2nd and 3rd place in the predicate) and it simplifies
> > > notation sometimes.
> >
> > I would echo that this is an important distinction to be aware of. I
> > personally favour the named perspective for a couple of reasons:
> > first, when Codd introduced attribute names there was an implicit
> > shift from a traditional ordered mathematical tuple, to a "tuple"
> > being viewed as an (unordered) partial function. Second, without the
> > attribute name a datum is in fact no datum at all, but rather simply
> > noise. Attributes are as much a component of the data under
> > consideration as the value they are mapped to. Hence, to disregard (or
> > rather externalize) attributes from a mathematical model of data seems
> > rather imprudent.
> It is a tricky issue. The approach usually used in predicate logic
> is to leave attributes unnamed and bind them to names with
> specific constructs within the context of a single sentence.
> If we bind the attributes directly to names, we get some
> convenience in the common case, but we also raise some
> issues.
> A common case in SQL is that we want to join two tables
> via an attribute that is the primary key of one table and a
> foreign key in another table, and they have the same name.
> Often the join is written out longhand anyway:
> SELECT ... from R, S WHERE R.RId = S.Rid
> The WHERE clause here is annoyingly boilerplate. Natural
> join, and named attributes, seem the obvious antidote to
> this boilerplate.

INNER JOIN ... ON seems a little less annoying than your boilerpplate. Natural join seems more elegant, but seems to depend on naming conventions.

But how about the following:

SELECT ... from S, R via S.Rid

The first of these two could be stored once in metadata, created at the same time table S is created. It could be used, if desired, to enforce referential integrity.

Note that the "via" operator connects the home table of Rid with a column from a different table. That's not a typo.

Now let's look at a self referential example.

create table EMPLOYEES

    (EmpId integer,

     First_Name varchar(50),
     Last_Name  varchar(50),
     BossId references EMPLOYEES.EmpId);

Select ... from EMPLOYEES E, EMPLOYEES B via E.BossId;

A couple of points. Bossid didn't have to be declared as "integer" because the references clause took care of that. A reference inherits the datatype of its referent. The join is reflexive, and the difference between the names EmpId and BossId is resolved in the references clause.

A reference table could be included more than once in a single select. It would need a distinct table alias for each inclusion, and the via clause would specify a different foreign key reference to the table.

> Any thoughts welcome.

Just my two cents. Received on Fri Feb 08 2008 - 16:55:13 CET

Original text of this message