Re: Question on Structuring Product Attributes

On Oct 16, 11:51 am, -CELKO- <jcelko..._at_earthlink.net> wrote:
> In this particular case, the way to do this in full, repeat FULL
> Standard SQL, is with global CHECK() check constraints. Make and model
> information is in the VIN so a regular expression can check it.
>
> CREATE TABLE SUV
> (vin CHAR(17) NOT NULL PRIMARY KEY,
>     REFERENCES Vehicles (vin),
>  CONSTRAINT vin_in_motor_pool
>       CHECK (EXISTS
>             (SELECT *
>                FROM Vehicles AS V1
>               WHERE V1.vin = SUV.vin),
>  CONSTRAINT suv_vin
>       CHECK (<< regular expression >>),
>  ..
>  );

Forget about the make-and-model-in-the-vin business, as that introduces changes far to late in the progression. Stick with the meaning of the original postings.

Your suggestion has the following errors:

1 Re PK (vin). My method places the code in One Place, Once.

Your method may work, but it places the code in every Subtype, which results in *repeated code*, and without a constraint_name, which hinders administration.

2 Syntax. I used syntax that most SQLs would accept.

Your method performs the existence check within the table DDL, which most SQLs disallow.

The correlated subquery won't work, because the table does not exist yet (it is being created).

Do you ever check your code before posting ?

3 The purpose of the xxx_Excl_check is to ensure that the Subtype exists in the parent *as that subtype*. Therefore the specific combination of (vin, vehicle_type) must be checked for existence.

You check for (vin) only.

4 The existence check is not a "regular_expression", it is a full query.

5 When existence checking, one should *never* use "SELECT *", as that drags the whole row into cache, and returns it to the thread, no matter what columns are used to qualify the query. And the thread doesn't use the data it fetched. For existence checking, one should always use "SELECT 1". That allows the optimiser to avoid handling and transporting the data altogether. Further, if the query can be serviced by an index alone (covered query), it avoids accessing the data structure.

> [your other post]

Does not have any substance worth responding to.

If, and when, you produce evidence (output of SELECT <true_column_list> ... , which excludes ROWID() functions, etc), then and only then, might we have some substance to discuss. Until then, your posting that "SQL implements [FK] constraints by building pointer chains under the covers" is complete bunkum.

One of the fundamental differences between Relational and prerelational  models (I will stick to practical understanding that everyone is familiar with, refer Codd's Twelve Rules, and please do not introduce distractions such as arguments about the RM) is that in the pre-relational model, the references, links, chains, etc, was indeed implemented with *internal pointers*, and the RM demanded that all such references, links, etc, was implemented with *logical keys only*. You seem to have missed that.

All the strictly-SQL-compliant products use and display logical keys only. The "edge" products make internal pointers visible, but even that is *in addition to* the logical key, which cannot be subverted.

Regards
Derek Received on Sun Oct 16 2011 - 04:27:01 CEST