Re: Question on Structuring Product Attributes
Date: Fri, 21 Oct 2011 15:31:20 -0700 (PDT)
Message-ID: <ba171cca-87b7-4799-865a-84908ec76490_at_u13g2000vbx.googlegroups.com>
On Oct 21, 3:44 pm, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 20 Oct 2011 14:07:41 -0700 (PDT), Kevin Kirkpatrick
>
> <kvnkrkpt..._at_gmail.com> wrote:
>
> (snip)>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 .
>
> (snip)
>
> Thanks for your reply. I have no experience with Oracle, but I think I
> can parse the code sufficiently to understand what is going on. You
> create a materialized view (probably very similar to the indexed view
> feature SQL Server has) that joins data from subtype and supertype
> table together. Then you add an extra index on that materialized view,
> but not on one of the columns itself, but on a virtual computed column
> that is computed using the DECODE function (not supported by SQL
> Server, but from the looks of it it appears to be a shorthand for a
> CASE expression), that will invoke a function if the type in the
> supertable does not match what should be in the subtable; and that
> function then invokes an error.
>
> I *think* a similar approach would be possible in SQL Server, but
> using a few different angles. I can't force an error from a function
> (SQL Server functions allow no side effects), but I could add a column
> to the view that is computed using a CASE expression with possible
> results 1 or 1/0. That would not give a similar user-friendly error
> message, but it would provide the functionality (I think - I have not
> tried it, though!).
>
Correct.
> But I am a bit surprised, as your first critique on Joe Celko's...
<snipped>
I do think Joe's solution suffers from the problem of exposing the implementation of the constraint at the logical level. I was merely proposing an SQL solution (one that admittedly is going to vary in mileage with different SQL vendors) that does not suffer that downfall. For the record, I'll sacrifice disk space usage 10 times over in favor of proper constraint enforcement, and do not consider 220% or any similar linear overhead to be problematic. I bench-marked my own solution and found it entailed a 75% overhead in disk space (misleadingly large because it doesn't account for the fact that VEHICLES, SEDANS, and SUVS are going to be much larger than 2 - 3 columns in practice) usage and 100% overhead in performance; meaning that adding 1 million vehicles unconstrained took 30 seconds, and adding 1 million vehicles, constrained, took 60 seconds. That, to me, is a perfectly acceptable overhead (consider, if I could take the KEY constraint off VEHICLES and load 1 million rows in 15 seconds... would that justify removing the constraint?).
I should note that, upon reading Erwin's response (the lousy cheat, using a TRDBMS) my solution /is/ overly complex and still exposes the constraint at the logical level. There is no need whatsoever to have a VEHICLE_TYPE in VEHICLES in the first place; VEHICLE_TYPE can be inferred based on which table the vehicle is inserted, SUVS or SEDANS. The following revision would be more appropriate (and actually has significantly less overhead to boot).
SQL>
SQL> CREATE TABLE VEHICLES
2 (VIN NUMBER PRIMARY KEY);
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.
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 DISJOINT_VEHICLE_TYPES_MV
2 BUILD IMMEDIATE REFRESH FAST ON COMMIT AS
3 SELECT SUVS.ROWID V_ROW, SEDANS.ROWID S_ROW 4 FROM SUVS , SEDANS 5 WHERE SUVS.VIN = SEDANS.VIN;
Materialized view created.
SQL> SQL> SQL> CREATE OR REPLACE FUNCTION DISJOINT_VEHICLE_TYPES_FUN2 RETURN NUMBER
3 DETERMINISTIC
4 IS
5 BEGIN
6 RAISE_APPLICATION_ERROR (-20001,'Vehicles must be entered as exactly one type.');
7 END;
8 /
Function created.
SQL>
SQL> CREATE INDEX DISJOINT_VEHICLE_TYPES_IDX ON
DISJOINT_VEHICLE_TYPES_MV
(DECODE(V_ROW,V_ROW,DISJOINT_VEHICLE_TYPES_FUN));
- note: Shame on Oracle, the following is not acceptable:
- CREATE INDEX DISJOINT_VEHICLE_TYPES_IDX ON DISJOINT_VEHICLE_TYPES_MV (DISJOINT_VEHICLE_TYPES_FUN)
SQL> SQL> SQL> INSERT INTO VEHICLES VALUES (1);
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);
1 row created.
SQL>
SQL> INSERT INTO SUVS VALUES (2, 'MY SUV');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> INSERT INTO SUVS VALUES (1, 'ALREADY A SEDAN!');
1 row created.
SQL>
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-20001: Vehicles must be entered as exactly one type. ORA-06512: at "APPS.DISJOINT_VEHICLE_TYPES_FUN", line 6
SQL>
SQL> INSERT INTO SEDANS VALUES (2, 'ALREADY AN SUV');
1 row created.
SQL>
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-20001: Vehicles must be entered as exactly one type. ORA-06512: at "APPS.DISJOINT_VEHICLE_TYPES_FUN", line 6 SQL>
SQL>
SQL> SPOOL OFF Apart from the superior design, this approach is nifty in that the materialized view will always be empty (although it's not a free lunch - there is disk usage for the MV Logs!)
> There are various ways to not expose these columns to the database
> user, such as building views on top of the tables that don't expose
> these columns, or creating stored procedures to implement the data
> access layer.
Right, and again, I'm attempting to offer a solution that avoids this. The whole "expose as a view" is fine for end-user querying, but I would never put application developers in a position to work with anything but base tables (INSTEAD-OF triggers??? blech!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Received on Sat Oct 22 2011 - 00:31:20 CEST