Re: Question on Structuring Product Attributes

Hugo

Since you are taking such detailed interest in the posted example code, and forming conclusions about what standard Subtypes can and cannot do, especially in regard to CHECK Constraints; Functions; transactions; etc, it is probably worth explaining a couple of other points.

As stated in my IDEF1X Notation (linked doc), both the RM, and IDEF1X as its Standard Modelling method, support a full range of Subtype configurations. The above examines Exclusive Subtypes and their integrity in detail. There are also Non-exclusive Subtypes, and the integrity for those is exactly the same. Again, I have given the code required for integrity of the Non-exclusive Subtype, but not for ordinary Data and Referential Integrity, which is required for all tables, Subtype or not. Consider this for Non-exclusive Subtype combinations: • a ProductBook must have a ProductCD
• before there is a PersonWidowed, there must be a pre-existing PersonMarried • etc

There is a correct method, using FKs between the Subtypes, but that does get cumbersome. Given that the Subtypes each have integrity, which we can rely on for basic existence, etc, and that we do not have to repeat that within the Subtypes cluster, it is often simpler to put the code required for the above in the single function Function that will be called by the single CHECK constraint. I have hundreds of those.

The second point is a general issue re the CHECK Constranit and the use of a function therein, not limited to Subtypes. The CHECK Constraint allows checking of the current inserted/updated row only. We can check other columns in the row, but we cannot check rows in other tables.

After 2007, with Functions, we can now check rows in other tables, and therefore, columns in rows in other tables. Which is what I am doing in the Subtype Check Function. That is a very important capability to note. We can now check: • PersonMarried.SpouseId exists in PersonSingle(SpouseId) and Person(SpouseId).Sex is not Person(PersonId).Sex • ProductCD.ArtistId exists in Artist and is not dead

Point being, Functions give us:
a. a vehicle to extend Business Rules in ways we could not before, and b. to do so using Declarative Constraints

It is not limited to Subtypes.

Cheers
Derek Received on Mon Feb 04 2013 - 03:02:46 CET