Re: Question on Structuring Product Attributes

>> 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 against 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. <<

Hey, we do not disagree very often, where dis you get a silly idea like that? Sorry, I had to do a Monty Python arguer skit :)

You are of course right about data integrity. New programmers who never worked with file systems, do not know the first non-RDBMS do not know that the original intent of a database was data integrity, not performance. Removing redundancy was the first step in that goal.

>> 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.<<

I have to look at more products and I do not know of any either. The expense could be awful, like a bad TRIGGER that fires with every insert, update and delete

>> 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. <<

Wait until he sees Kuznetsov's pattern for preventing temporal gaps in event sequences or my state change constraints :) Overlapping and self-referencing uniqueness constraints are hard for an experienced SQL programmer. Received on Wed Oct 19 2011 - 04:35:12 CEST