Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>None of that is necessary, especially not doubling up the tables.</p> <h2>Introduction</h2> <p>Since the Standard for Modelling Relational Databases (IDEF1X) has been in common use for over 25 years (at least in the high quality, high performance end of the market), I use that terminology. Darwen, <strike>despite the great work he has done to progress</strike><sup>1</sup> consistent with the great work he has done to suppress the Relation Model, he was unaware of IDEF1X until I brought it to his attention in 2009, and thus has a new terminology<sup>2</sup> for the Standard terminology that we have been using for decades. Further, the new terminology does not deal with all the cases, as IDEF1X does. Therefore I use the established Standard terminology, and avoid new terminology.</p> <ul> <li><p>even the concept of a "distributed key" fails to recognise the underlying ordinary PK::FK Relations, their iimplementation in SQL, and their power.</p></li> <li><p>The Relational, and therefore IDEF1X, concept is <strong>Identifiers</strong> and Migration thereof.</p></li> <li><p>Sure, the vendors are not exactly on the ball, and they have weird things such a "partial Indices" etc, which are completely unnecessary when the basics are understood. But famous academics coming up with incomplete new concepts when the concept was standardised and give full treatment 25 years ago ... that, is unexpected.</p></li> </ul> <h3>Caveat</h3> <p>IEC/ISO/ANSI SQL barely handles 5NF adequately, and it does not support Supertype-Subtype structures at all; there are no Declarative Constraints for this (and there should be).</p> <ul> <li>Therefore , in order to enforce the full set of Rules expressed in the Data Model, both SuperType::Subtype and Subtype::Supertype, we have to fiddle a little with CHECK Constraints, etc (I avoid using Triggers for a number of reasons). </li> </ul> <h3>Relief</h3> <p>However, I take all that into account. In order for me to effectively provide a Data Modelling service on StackOverflow, without having to preface that with a full discourse, I purposely provide models that can be implemented by capable people, using existing SQL and existing Constraints, to whatever extent they require. It is already simplified, and contains the common level of enforcement. If there is any question, just ask, and you shall receive. </p> <p>We can use both the example graphic in the linked document and your fully IDEF1X-compliant <a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/Mark%20DM.pdf" rel="nofollow noreferrer"><strong>▶Sensor Data Model◀</strong></a></p> <p>Readers who are not familiar with the Relational Modelling Standard may find <a href="http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf" rel="nofollow noreferrer"><strong>▶IDEF1X Notation◀</strong></a> useful. Readers who think a database can be mapped to objects, classes, and subclasses are advised that reading further may cause injury. This is further than Fowler and Ambler have read.</p> <h1>Implementation of Referential Integrity for Supertype-Subtype</h1> <p>There are two types of Supertype-Subtype structures.</p> <h2>Exclusive Subtype</h2> <p>Exclusive means there can be only one Subtype row for each Supertype row. In IDEF1X terms, there should be a Discriminator column in the Supertype, which identifies the Supertype row and which Subtype row exists for it. </p> <ul> <li><p>For more than two Subtypes, this is demanded, and I implement a Discriminator column.</p></li> <li><p>For two Subtypes, since this is easily derived from existing data (eg. <code>Sensor.IsSwitch</code> is the Discriminator for <code>Reading</code>), I do not model an additional explicit Discriminator column for <code>Reading</code>. However, you are free to follow the Standard to the letter and implement a Discriminator.</p></li> </ul> <p>I will take each aspect in detail.</p> <ol> <li><p>The Discriminator column needs a CHECK Constraint to ensure it is within the range of values, eg: <code>IN ("B", "C", "D")</code>. <code>IsSwitch</code> is a BIT, which is 0 or 1, so that is already constrained.</p></li> <li><p>Since the PK of the Supertype defines its uniqueness, only one Supertype row will be allowed; no second Supertype row (and thus no second Subtype row) can be inserted. </p> <ul> <li><p>Therefore it is overkill, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Supertype, as your link advises. The uniqueness is in the PK, and therefore the PK plus anything will be unique).</p></li> <li><p>IDEF1X does not require the Discriminator in the Subtype tables. In the Subtype, which is again constrained by the uniqueness of its PK, as per the model, if the Discriminator was implemented as a column in that table, every row in it will have the same value for the Discriminator (every Book will be "B"; every <code>ReadingSwitch</code> will be an <code>IsSwitch</code>). Therefore it is absurd to implement the Discriminator as a column in the Subtype. And again, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Subtype: the uniqueness is in the PK, and therefore the PK plus anything will be unique).</p></li> <li><p>The method identified in the link is a hamfisted and bloated (massive data duplication for no purpose) way of implementing Referential Integrity. There is probably a good reason the author has not seen that construct anywhere else. It is a basic failure to understand SQL and to use it <em>as it is</em> effectively. These "solutions" are typical of people who follow a dogma "SQL can't do ..." and thus are blind to what SQL can do. The horrors that result from Fowler and Ambler's blind "methods" are even worse. </p></li> </ul></li> <li><p>The Subtype PK is also the FK to the Supertype, that is all that is required, to ensure that the Subtype does not exist without a parent Supertype.</p> <ul> <li>Therefore for any given PK, whichever Supertype-Subtype is inserted first will succeed; and whichever Supertype-Subtype is attempted after that, will fail. Therefore there is nothing to worry about in the Subtype table (a second Supertype row or a second Subtype row for the same PK is prevented).<br> . </li> </ul></li> <li><p>The SQL CHECK Constraint is limited to checking the <em>inserted row</em>. We need to check the inserted row <em>against other rows</em>, either in the same table, or in another table. Therefore a User Defined Function is required.</p> <ul> <li><p>Write a simple UDF that will check for existence of the PK <strong>and</strong> the Discriminator in the SuperType, and return 1 if EXISTS or 0 if NOT EXISTS. You will need one UDF per Supertype (not per Subtype).</p></li> <li><p>In the Subtype, implement a CHECK Constraint that calls the UDF, using the PK (which is both the Supertype and the Subtype) and the Discriminator <em>value</em>.</p></li> <li><p>I have implemented this in scores of large, real world databases, on different SQL platforms. Here is the <a href="http://www.softwaregems.com.au/Documents/Tutorial/ValidateExclusive_fn.sql" rel="nofollow noreferrer"><strong>▶User Defined Function Code◀</strong></a>, and the <a href="http://www.softwaregems.com.au/Documents/Tutorial/Subtype_CHECK.sql" rel="nofollow noreferrer"><strong>▶DDL Code◀</strong></a> for the objects it is based on.</p></li> <li><p>This particular syntax and code is tested on Sybase ASE 15.0.2 (they are very conservative about SQL Standards compliance).</p></li> <li><p>I am aware that the limitations on User Defined Functions are different for every SQL platform. However, this is the simplest of the simple, and AFAIK every platform allows this construct. (No idea what the Non-SQLs do.)</p></li> <li><p>yes, of course this clever little technique can be used implement any non-trivial data rule that you can draw in a Data Model. In particular, to overcome the limitations of SQL. Note my caution to avoid two-way Constraints (circular references).</p></li> </ul></li> <li><p>Therefore the CHECK Constraint in the Subtype, ensures that the PK plus the <strong>correct</strong> Discriminator exists in Supertype. Which means that <strong>only</strong> that Subtype exists for the Supertype (the PK). </p> <ul> <li><p>Any subsequent attempt to insert <em>another</em> Subtype (ie. break the Exclusive Rule) will fail because the PK+Discriminator does not exist in the Supertype.</p></li> <li><p>Any subsequen attempt to insert another row of the same Subtype is prevented by the uniqueness of its PK Constraint.</p></li> </ul></li> <li><p>The only bit that is missing (not mentioned in the link) is the Rule "every Supertype must have at least one Subtype" is not enforced. This is easily covered in Transactional code (I do <strong>not</strong> advise Constraints going in two directions, or Triggers); use the right tool for the job.</p></li> </ol> <h2>Non-exclusive Subtype</h2> <p>The Supertype (parent) can host more than one Subtype (child)</p> <ol> <li><p>There is no one Subtype to be identified.</p> <ul> <li><p>The Discriminator does not apply to Non-exclusive Subtypes. </p></li> <li><p>The existence of a Subtype is identified by performing an existence check on the Subtype table, using the Supertype PK.</p></li> </ul></li> <li><p>Simply exclude the CHECK Constraint that calls the UDF above. </p> <ul> <li>The PRIMARY KEY, FOREIGN KEY, and the usual Range CHECK Constraints, adequately support all requirements for Non-exclusive Subtypes. </li> </ul></li> </ol> <h3>Reference</h3> <p>For further detail; a diagrammatic overview including details; and the distinction between Subtypes and Optional Column tables, refer to this <a href="http://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Subtype.pdf" rel="nofollow noreferrer"><strong>Subtype document</strong></a>.</p> <h3>Note</h3> <ol> <li><p>I, too, was taken in by C J Date's and Hugh Darwen's constant references to "furthering" the <em>Relational Model</em>. Based consistent evidence, after many years of interaction, I have concluded that their work is in fact, a debasement of it. They have done nothing to further Dr E F Codd's seminal work, and everything to damage and suppress it.</p></li> <li><p>They have private definitions for Relational terms, which of course severely hinders any communication. They have new terminology for terms we have had since 1970, in order to appear that they have "invented" it. Typical of frauds and thieves.</p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow to Implement Referential Integrity in Subtypes
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPerformanceDBA
    1. USPerformanceDBA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to Implement Referential Integrity in Subtypes
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO@Mark. Have LoggerSensor & NetworkSensir been superceded ?
      singulars
      1. PO
      1. USPerformanceDBA
    2. CO@PerformanceDBA: The Switch Sensors has different parameters to the Analog Sensors so I've been considering splitting them up (though not sure if it's worth it). On top of that, a LoggerSensor is only ever Analog. I could just put all the parameters in Sensor and use whichever are appropriate based on the IsSwitch column. For example "UpperLimit" and "LowerLimit" are AnalogSensor parameters and don't relate to SwitchSensors.. SwitchSensor has "OnTimeLimit" (i.e. time the switch can be ON before an alarm occurs).
      singulars
      1. PO
      1. USMark
    3. CO@PerformanceDBA: PS thanks for the great answer, makes sense and I've just about got it working in PostgreSQL - just need to tweak the ValidateExclusive function (PostgreSQL has no ISNULL function).
      singulars
      1. PO
      1. USMark
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload