SQL joining supertype and subtype tables

Question

I have a supertype table along with two child subtype tables, and a third normal table. My question relates to joining the normal table to one of the subtype tables without including the intermediate supertypeTable. As seen by the CREATE statement below, my indexes for normalTable1 all relate to supertypeTable and not either of the subtype tables, however, the subtype tables have the same PK as the supertype table. Could I simply do SELECT whatever FROM subtypeTable1 AS s INNER JOIN normalTable1 AS n ON n.supertypeTable_id=s.supertypeTable_id..., or do I need to include additional indexes or also include supertypeTable in the join? Thank you

supertypeTable
-id (PK)
-data

subtypeTable1
-supertypeTable_id (PK and also FK with a 1-to-1 relationship to supertypeTable.id)
-data

subtypeTable2
-supertypeTable_id (PK and also FK with a 1-to-1 relationship to supertypeTable.id)
-data

normalTable1
-id (PK)
-supertypeTable_id (FK with a many-to-one relationship to supertypeTable.id)
-data

  CREATE normalTable1 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  supertypeTable_id INT UNSIGNED NOT NULL ,
  data VARCHAR(45) NULL ,
  PRIMARY KEY (id) ,
  INDEX fk1 (supertypeTable_id ASC) ,
  CONSTRAINT fk2
    FOREIGN KEY (supertypeTable_id )
    REFERENCES supertypeTabl (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  ENGINE = InnoDB;

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

As long as the keys match, you don't need to include unneeded intermediate tables in your query. You can just join your normal table to a subtype table. SQL (and relational) joins are based on matching values, not on following a path.

But your table structure has some problems unrelated to this question. See this SO answer, and look closely at the "party_type" column in it.

edited May 23 '17 at 12:12

Community

1
1

answered Feb 20 '13 at 15:03

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Thanks Catcall. I thought and hoped that, but never knew for sure. Other than asking this question on SO, how would someone know this? – user1032531 Feb 20 '13 at 15:12
Thanks for the additional info regarding party_type. I did know that some use this practice, but thought it was redundant. After reading your recommended post, I will probably implement. – user1032531 Feb 20 '13 at 15:17
Back to the party_type idea, note the last comment: "(There's a unique constraint on parties.party_id.)" - Yup, sorry, missed that. So the query doesn't require the party_type and the structure works as expected". Agree off topic and no need to respond (unless you want to :) – user1032531 Feb 20 '13 at 15:23
1

@user1032531: *"Other than asking this question on SO, how would someone know this?"* It's implied by normalization, and it's part of the history of the relational model. One of the problems the relational model solves is "access path dependence". See Codd's [A relational model of data for large shared databanks](http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf) (pdf), sections 1.2.3 and 2.1.3. – Mike Sherrill 'Cat Recall' Feb 20 '13 at 16:07
Read Codd's article. Didn't know history on SQL went back to June 1970. Glad you were able to point me in the right direction. – user1032531 Feb 21 '13 at 01:51

SQL joining supertype and subtype tables

1 Answers1