Re: Question on Structuring Product Attributes

From: Kevin Kirkpatrick <kvnkrkptrck_at_gmail.com>
Date: Thu, 20 Oct 2011 14:07:41 -0700 (PDT)
Message-ID: <0b05846e-8fad-45e4-b943-d93cae82051c_at_m4g2000yqm.googlegroups.com>

> Hi Derek,
>
> I've tried to follow your debate with Joe Celko with interest, though
> it's sometimes hard to keep track of the arguments in between the
> mud-slingin.
>
> As Joe can confirm, I often disagree with him, on many subjects. But
> when I first saw him post the subtype/supertype implementation model
> you criticise, I liked it for its robust protection agains integrity
> violations. You are correct that the redundant columns and the
> extraneous keys require more disk space and hurt performance, but I
> always think that integrity comes first, all the rest comes later.
>
> But I have of course an open mind for better alternatives, and you
> seem to have one. Unfortunately, the most relevant part for integrity
> is left out of your post, or rather replaced by ellipsis. Given the
> two tables above (plus the implied vehicles table), how will the
> following (inconsistent) transaction be rejected:
>
> BEGIN TRANSACTION;
> INSERT INTO Vehicles (vin, vehicle_type)
> VALUES ('12345678901234567', 'SUV');
> INSERT INTO Sedans (vin, sedan_type)
> VALUES ('12345678901234567', '4DR');
> INSERT INTO TwoDoor (vin)
> VALUES ('12345678901234567');
> COMMIT TRANSACTION;
>
> I think you intend the constraints Sedan_Excl_vk and 2dr_Excl_ck to
> prevent this, but you left out their specification. In a complete ANSI
> SQL implementation, I could easily create this constraint, as the ANSI
> SQL standard permits the use of subqueries in a CHECK constraint
> (SQL-92: full compliance; SQL-2003: Feature F671, "Subqueries in CHECK
> constraints"). But the product I work with (Microsoft SQL Server) does
> not implement this feature, and a quick Google search gives me the
> impression that most (if not all) major vendors suffer from the same
> limitation.
>
> If your point is that in a fully ANSI-compliant RDBMS, the version Joe
> posted suffers from needless redundancy (just like this sentence), I
> agree. But if you have a way of coding the CHECK constraint that is
> supported by some, or preferably all, major current RDBMS
> implementations and that will disallow the inconsistent data I gave
> above, please let me know. (Or, if you already gave it and I missed it
> when wading through the discussion, please tell me where I can find
> it).
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

This seemed the most appropriate "jumping in" point. In my SQL vendor of choice (Oracle), while there is not support for ANSI CHECK constraints, I have found a combination of features that allow for cross-table constraints to be enforced in a purely declarative and high-performance manner. The basic idea is to use instant-refresh materialized-views that bring the data together, then put indexes on the materialized views to enforce cross-row constraints. All such objects (materialized view logs, materialized views, constraint functions, and constraint indexes) used to enforce the constraints should be considered physical implementation details like table indexes, i.e. NOT exposed at the logical level and NOT accessible to users/application code .

For instance, the vehicle sub-typing is easily accommodated with the following script: (Note, this is a purely declarative solution - the 2 functions merely exist to return meaningful error messages for constraint violations and play no role in enforcing said constraints... calls to these functions could be replaced with the expression "1/0", giving bizarre div-by-zero error messages, but preventing constraint violations nonetheless).

SQL>
SQL> CREATE TABLE VEHICLES

  2  	 (VIN NUMBER PRIMARY KEY,
  3  	  VEHICLE_TYPE VARCHAR2(3) CHECK (VEHICLE_TYPE IN

('SUV','SED'))); Table created.

SQL>
SQL> CREATE TABLE SEDANS

  2  	 (VIN NUMBER PRIMARY KEY,
  3  	  SEDAN_ATTRIBUTE VARCHAR2(150),
  4  	  FOREIGN KEY (VIN) REFERENCES VEHICLES(VIN));

Table created.

SQL>
SQL> CREATE TABLE SUVS

  2  	 (VIN NUMBER PRIMARY KEY,
  3  	  SUV_ATTRIBUTE VARCHAR2(150),
  4  	  FOREIGN KEY (VIN) REFERENCES VEHICLES(VIN));

Table created.

/* And now the fun begins */

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON VEHICLES WITH ROWID; Materialized view log created.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON SEDANS WITH ROWID; Materialized view log created.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON SUVS WITH ROWID; Materialized view log created.

SQL>
SQL> CREATE MATERIALIZED VIEW SEDAN_CONSTRAINT_MV 2 BUILD IMMEDIATE REFRESH FAST ON COMMIT AS

  3  	 SELECT V.VEHICLE_TYPE, V.ROWID V_ROW, S.ROWID S_ROW
  4  	 FROM	VEHICLES V, SEDANS S
  5  	 WHERE V.VIN = S.VIN;

Materialized view created.

SQL>
SQL> CREATE MATERIALIZED VIEW SUV_CONSTRAINT_MV 2 BUILD IMMEDIATE REFRESH FAST ON COMMIT AS

  3  	 SELECT V.VEHICLE_TYPE, V.ROWID V_ROW, S.ROWID S_ROW
  4  	 FROM	VEHICLES V, SUVS S
  5  	 WHERE V.VIN = S.VIN;

Materialized view created.

SQL>
SQL> CREATE OR REPLACE FUNCTION SEDAN_CONSTRAINT_FUN   2 RETURN NUMBER
  3 DETERMINISTIC
  4 IS
  5 BEGIN
  6 RAISE_APPLICATION_ERROR (-20001,'Only vehicles with type SED permitted in SEDANS table');
  7 END;
  8 /

Function created.

SQL>
SQL> CREATE INDEX SEDAN_CONSTRAINT_IDX ON SEDAN_CONSTRAINT_MV (DECODE(VEHICLE_TYPE,'SED',NULL,SEDAN_CONSTRAINT_FUN)); Index created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION SUV_CONSTRAINT_FUN

  2 RETURN NUMBER
  3 DETERMINISTIC
  4 IS
  5 BEGIN
  6 RAISE_APPLICATION_ERROR (-20001,'Only vehicles with type SUV permitted in SUVS table');
  7 END;
  8 /

Function created.

SQL>
SQL> CREATE INDEX SUV_CONSTRAINT_IDX ON SUV_CONSTRAINT_MV (DECODE(VEHICLE_TYPE,'SUV',NULL,SUV_CONSTRAINT_FUN)); Index created.

PROOF OF CONCEPT --------------------------

SQL>
SQL> INSERT INTO VEHICLES VALUES (1, 'SED'); 1 row created.

SQL>
SQL> INSERT INTO SEDANS VALUES (1, 'MY SEDAN'); 1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL>
SQL> INSERT INTO VEHICLES VALUES (2, 'SUV'); 1 row created.

SQL>
SQL> INSERT INTO SUVS VALUES (2, 'MY SUV'); 1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL>
SQL>
SQL> INSERT INTO VEHICLES VALUES (3, 'SED');

1 row created.

SQL>
SQL> INSERT INTO SUVS VALUES (3, 'NOT AN SUV!'); 1 row created.

SQL>
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view "APPS"."SUV_CONSTRAINT_MV"
ORA-20001: Only vehicles with type SUV permitted in SUVS table ORA-06512: at "APPS.SUV_CONSTRAINT_FUN", line 6

SQL>
SQL> INSERT INTO VEHICLES VALUES (4, 'SUV'); 1 row created.

SQL>
SQL> INSERT INTO SEDANS VALUES (4, 'NOT A SEDAN!'); 1 row created.

SQL>
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view "APPS"."SEDAN_CONSTRAINT_MV"
ORA-20001: Only vehicles with type SED permitted in SEDANS table ORA-06512: at "APPS.SEDAN_CONSTRAINT_FUN", line 6

SQL>
SQL> UPDATE VEHICLES SET VEHICLE_TYPE = 'SUV' WHERE VIN = 1; 1 row updated.

SQL>
SQL> UPDATE VEHICLES SET VEHICLE_TYPE = 'SED' WHERE VIN = 2; 1 row updated.

SQL>
SQL> SPOOL OFF Received on Thu Oct 20 2011 - 23:07:41 CEST

This message: [ Message body ]
Next message: Erwin: "Re: Question on Structuring Product Attributes"
Previous message: Mamma Wesolowski: "Psychopath Jack Wesolowski spamming the UseNet (was: OWS Dirtball Arrested For Exposing Himself To Chilluns)"
Maybe in reply to: Yadda: "Question on Structuring Product Attributes"
In reply to Hugo Kornelis: "Re: Question on Structuring Product Attributes"
Next in thread: Erwin: "Re: Question on Structuring Product Attributes"
Reply: Erwin: "Re: Question on Structuring Product Attributes"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message