Re: Question on Structuring Product Attributes

On Oct 13, 2:20 am, -CELKO- <jcelko..._at_earthlink.net> wrote:
> While I love the flattery, I did not invent Nesteds Sets or this class
> hierarchy model. I just make tehm popular and publish teh code to use
> them.

Joe

Thanks for the response.

What a relief.

The flattery is real, for your *other* work, and I had difficulty accepting that the same person presented this work.

This "class hierarchy model" is one of the most idiotic things I have seen in my 34 years in databases.

1. The method of implementing RI for Subtypes. This:

   ---

> CREATE TABLE Vehicles
>   UNIQUE (vin, vehicle_type),
>
> CREATE TABLE SUVs
>   UNIQUE (vin, vehicle_type),
>
> CREATE TABLE Sedans
>   UNIQUE (vin, vehicle_type),

1. basically doubles the number of indices in the relevant tables, and that has a substantial overhead.
2. requires vehicle_type to be carried in the subtypes, which AFAIC is completely redundant (in SUVs, we know all the rows are SUV, why carry a column in which every value will be "SUV").
3. I am not entirely against concept of de-normalisation, but that assumes prior normalisation, and has a specific purpose. Whereas this is un-normalised: vehicle_type is 1::1 with vin, and belongs in Vehicles, once.
4. it is indirect, and subverts the normal or direct method of identifying the relation in the two subtypes, which would normally be:

   FOREIGN KEY (vin)

        REFERENCES Vehicles(vin)

e. introduces complexity and processing; views; instead-of triggers, etc. I would rather the database remain open as possible, without such complications for use. Particularly when such nonsense is completely unnecessary.

    > Now start hiding all this stuff in VIEWs immediately and add an     > INSTEAD OF trigger to those VIEWs.

f. Of course vin is unique, therefore adding anything to vin will be unique. As I understand it, the three added UNIQUE constraints are merely (or heavily) for the purpose of checking the Subtype is legal, that it exists in the parent with the proper Discriminator value. In that case, Why not use normal SQL capabilities (yes, we know there is not DRI for this) which provide that particular form of RI, *without*:

• one additional redundant column in every subtype
• one additional redundant index in every table (subtypes plus parent). Note the additional processing load
• complexity
• Views to "hide" the complexity
• Instead-Of triggers

/*
I saw this insanity (posted method) in a database last year, and ripped it out, in part permitting the database to run about 20 times (2000%) faster, with no loss of capability. I say "in part" because I removed a couple of other tumours in the same exercise, therefore I cannot say for certain exactly how much this tumour contributed to the negative 2000%. The developer who implemented it (and fancied himself "modeller" and "DBA") had been reading too much Ambler and Fowler; (who are famously clueless re the RM or databases; perfect examples of Maslow's Hammer). The company required a Relational Database, he created a scratch pad for his app, not a database.

He is still coming to terms with the concept that in the Relational Database world, we never need to "refactor". First the snake oil salesmen sell you the cancer, then they sell you the relief. RDB types do not buy either one.
*/

> I can continue to build a hierarchy like this ...

In which case the doubled indices; subversion; duplicated column; and complexity will continue down the hierarchy. At each level of the hierarchy, one more column needs to be duplicated, and it will always contain one value: SUVs contain a column containing "SUV" only; TwoDoors contain a column containing "SED" only, *and* a column containing "2DR" only.

To be clear, I agree with:
- the concept of the hierarchy (the Subtypes may well have subtypes) and
- the value of ensuring the integrity of Subtypes I disagree with the implementation method.

    vin        CHAR(17)               NOT NULL
        PRIMARY KEY,
    sedan_type CHAR(3)  DEFAULT '2DR' NOT NULL
        CHECK (sedan_type IN ('2DR', '4DR') ),
    FOREIGN KEY (vin)
        REFERENCES Vehicles(vin)
    CONSTRAINT Sedan_Excl_ck
        CHECK ...

        CHECK ... As the the model progresses, VehicleType and SedanType will be Normalised into separate Entities, and Vehicle and Sedan (respectively) will have Foreign Key References to them.

> If an entity doesn't have to be exclusively one subtype, you play with
> the root of the class hierarchy:

If the Subtypes are Non-Exclusive, simply exclude the CHECK Constraint.

Now that the db has first been modelled (separately, as data, using rules for modelling data) the object definitions (which are separate as process, and use the data/database) are pedestrian.

The posted method of implementing RI for Subtypes is completely and utterly without merit in any "database" (this is c.d.t.). I would respectfully suggest that you apply RDB fundamentals to the cute ideas of others, such as "class hierarchy model", and test their validity, before posting. Otherwise you run the risk of harming your reputation. Due to your standing, your post, your making them popular, lends a certain credibility to these otherwise in-credible and absurd concepts. When they get punctured (any of the RDB types on this forum can puncture them, I just happen to be the first in this instance), your credibility suffers along with it, and that is not my intent.

> I did not invent Nesteds Sets

That is a relief as well. Now I can correct my colleagues, who presented that hilarious concept to me as yours. Who do we credit for providing us with such a source of laughter ?

Regards
Derek Received on Thu Oct 13 2011 - 02:51:11 CEST