Re: Question on Structuring Product Attributes

From: Derek Asirvadem <derek.asirvadem_at_gmail.com>
Date: Sun, 16 Oct 2011 19:17:20 -0700 (PDT)
Message-ID: <61a729a3-a15e-4337-b7e0-4dd844fdd1d3_at_s7g2000prh.googlegroups.com>

On Oct 17, 5:25 am, Jonathan Leffler <jleff..._at_earthlink.net> wrote:
>
> Actually, Standard SQL does not support indexes at all.  They are an
> implementation detail that actual SQL DBMS use, but they are completely
> outside the scope of the SQL standard.

Sure. But let's not get side-tracked. (a) I am not writing a paper with fully qualified statements, I am responding to a post. (b) all SQLs [that are compliant with the standard and thus SQLs] have indices. Whether it is an Extension or not is not relevant to this thread, and I did not mean to imply that indices were standard, rather that all the SQLs have them, and have them in a visible column, nonpointer,  form.

The point being argued is, Celko stated:
>> You have made the assumption that references are done by more
>> indexes. Sybase (nee WATCOM) SQL implements the constraints like
>> FOREIGN KEY (vin, vehicle_type)
>> REFERENCES Vehicles(vin, vehicle_type)
>> by building pointer chains under the covers, so each compound key
>> actually appears once in the schema. Teradata uses a hashing
>> algorithm. RDBMS companies have been working on PK-FK joins for a few
>> decades, so they are pretty good at it now.

Which I state is nonsense. Celko does not understand the effect of his own SQL:

- each PRIMARY KEY constraint builds an index
- each UNIQUE constraint builds an index
- no SQL normalises the schema
- the nonsense about "pointer-chains" is just a red herring to avoid

dealing directly with my refutation of the nonsense he has posted.

In order to avoid further misunderstandings, yes of course the B-Tree in any index on any platform that has implemented SQL has pointers at the non-leaf levels, and may have either pointers or actual data columns (the key) at the leaf level. The context here is, whether (a) the index is real (b) visible and (c) consists of visible data columns. Or not. What one platform or another does under the covers is not relevant to the method posted by Celko, or the issue I have raised, or the method posted by me.

Regards
Derek Received on Mon Oct 17 2011 - 04:17:20 CEST