SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Junction tables referencing other junction tables

    Working on something (again/still) that has me wondering if I'm going a little too far down the rabbit hole...

    I'm dealing with some hierarchical data where I've got a junction (many-to-many) table that references a couple lookup tables. Then I have another junction table that references a lookup table, a foreign key from another table (not just a lookup list, if it matters)... and then uses the first junction table as another lookup table. Is that a viable method, or am I borrowing trouble by doing this?

    TIA,

    Monte

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by memilanuk View Post
    Is that a viable method
    can't tell, without the actual table definitions (hint, hint)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by memilanuk View Post
    Is that a viable method......
    agree with r937


    absolutely no idea without more info on table structures.

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <sigh> kinda still at the ERD and planning stage...

    I'll to put some table statements together and post 'em up here.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Those are fine, post those.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Specifically looking at the relation between tbl_tournament_category / tbl_tournament_type (lookup tables), tbl_discipline (junction table), tbl_competitor_class (another junction table that references tbl_discipline).



  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm a little bit confused in the lower left corner

    a discipline belongs to a category, so it carries the category FK, check

    a discipline belongs to a type, but i see no type FK to the types table, instead i see a discipline FK? and an enum type?

    maybe explain what you were trying to do there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Minor typo, due to multiple (and ongoing) revisions. Should have been 'type_fk' instead of 'discipline_fk'. The enum is there because it needs to be - the whole setup shares many common details - same type of matches, same classification system, same scoring, same general rules... but there is a distinct dividing line between 'sling' & 'f-class' such that a competitor may have a classification in 'Fullbore' and in 'Fullbore F-class' which are separate and distinct from one another. Similarly, while people may physically compete on the same day and on adjacent targets, for scoring purposes the matches need to be kept separate.

    The thing that I'm kind of eyeballing now as I'm working towards it on my CREATE TABLE statements is how to reference a composite primary key that envelops two columns of type 'int' and one 'char' column...?

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the SQL for all the tables...

    PHP Code:
    -- phpMyAdmin SQL Dump
    -- version 3.3.2deb1
    -- http://www.phpmyadmin.net
    --
    -- 
    Hostlocalhost
    -- Generation TimeNov 132010 at 03:35 PM
    -- Server version5.1.41
    -- PHP Version5.3.2-1ubuntu4.5

    SET SQL_MODE
    ="NO_AUTO_VALUE_ON_ZERO";


    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;

    --
    -- 
    Database: `xcount`
    --

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_classifications`
    --

    CREATE TABLE `tbl_classifications` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_classifications`
    --

    INSERT INTO `tbl_classifications` (`name`, `descr`) VALUES
    ('EX''Expert'),
    (
    'HM''High Master'),
    (
    'MA''Master'),
    (
    'MK''Marksman'),
    (
    'SS''Sharpshooter'),
    (
    'UM''Unclassified Master');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_competitors`
    --

    CREATE TABLE `tbl_competitors` (
      `
    idint(11NOT NULL AUTO_INCREMENT,
      `
    first_namevarchar(20NOT NULL,
      `
    mid_initchar(1) DEFAULT NULL,
      `
    last_namevarchar(30NOT NULL,
      `
    genderenum('M','F'NOT NULL,
      `
    birth_datedate DEFAULT NULL,
      `
    nra_idvarchar(10) DEFAULT NULL,
      `
    date_addedtimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      
    PRIMARY KEY (`id`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=;

    --
    -- 
    Dumping data for table `tbl_competitors`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_competitor_category`
    --

    CREATE TABLE `tbl_competitor_category` (
      `
    competitor_fkint(11NOT NULL,
      `
    spec_category_fkchar(5NOT NULL,
      
    PRIMARY KEY (`competitor_fk`,`spec_category_fk`),
      
    KEY `spec_category_fk` (`spec_category_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_competitor_category`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_competitor_class`
    --

    CREATE TABLE `tbl_competitor_class` (
      `
    competitor_fkint(11NOT NULL,
      `
    classification_fkchar(5NOT NULL,
      `
    discipline_fkchar(5NOT NULL,
      
    PRIMARY KEY (`competitor_fk`,`classification_fk`,`discipline_fk`),
      
    KEY `classification_fk` (`classification_fk`),
      
    KEY `discipline_fk` (`discipline_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_competitor_class`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_contact_info`
    --

    CREATE TABLE `tbl_contact_info` (
      `
    idint(11NOT NULL AUTO_INCREMENT,
      `
    competitor_fkint(11NOT NULL,
      `
    streetvarchar(50NOT NULL,
      `
    cityvarchar(50NOT NULL,
      `
    state_provchar(2NOT NULL,
      `
    post_codevarchar(10NOT NULL,
      `
    countrychar(3NOT NULL DEFAULT 'USA',
      `
    phonevarchar(10) DEFAULT NULL,
      `
    emailvarchar(50) DEFAULT NULL,
      
    PRIMARY KEY (`id`),
      
    KEY `competitor_fk` (`competitor_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=;

    --
    -- 
    Dumping data for table `tbl_contact_info`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_discipline`
    --

    CREATE TABLE `tbl_discipline` (
      `
    namechar(5NOT NULL,
      `
    category_fkchar(5NOT NULL,
      `
    type_fkchar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      `
    typeenum('sling','f-class'NOT NULL,
      
    PRIMARY KEY (`name`),
      
    KEY `category_fk` (`category_fk`),
      
    KEY `type_fk` (`type_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_discipline`
    --

    INSERT INTO `tbl_discipline` (`name`, `category_fk`, `type_fk`, `descr`, `type`) VALUES
    ('300m''300m''300m''Int''l 300m''sling'),
    (
    'FB-F''FB''FB''Int''l Fullbore F-Class''f-class'),
    (
    'FB-P''FB''FB''Int''l Fullbore Prone''sling'),
    (
    'LR-F''HP''LRP''HP Rifle Long-Range F-Class''f-class'),
    (
    'LR-P''HP''LRP''HP Rifle LR Prone''sling'),
    (
    'MR-F''HP''MRP''HP Rifle Mid-Range F-Class''f-class'),
    (
    'MR-P''HP''MRP''HP Rifle Mid-Range Prone''sling'),
    (
    'XTC''HP''XTC''HP Rifle Conventional''sling');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_equipment_categories`
    --

    CREATE TABLE `tbl_equipment_categories` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_equipment_categories`
    --

    INSERT INTO `tbl_equipment_categories` (`name`, `descr`) VALUES
    ('A''Any Rifle'),
    (
    'AA''Any Rifle - Any Sight'),
    (
    'AI''Any Rifle - Iron Sight'),
    (
    'FO''F-Class Open'),
    (
    'FTR''F-Class TR'),
    (
    'M''Match Rifle'),
    (
    'MA''Match Rifle - Any Sight'),
    (
    'P''Palma Rifle'),
    (
    'S''Service Rifle'),
    (
    'TR''Target Rifle');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_matches`
    --

    CREATE TABLE `tbl_matches` (
      `
    match_numberint(11NOT NULL,
      `
    discipline_fkchar(5NOT NULL,
      `
    tournament_fkint(11NOT NULL,
      `
    namevarchar(50) DEFAULT NULL,
      
    PRIMARY KEY (`match_number`,`discipline_fk`,`tournament_fk`),
      
    KEY `discipline_fk` (`discipline_fk`),
      
    KEY `tournament_fk` (`tournament_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_matches`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_sanction`
    --

    CREATE TABLE `tbl_sanction` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_sanction`
    --

    INSERT INTO `tbl_sanction` (`name`, `descr`) VALUES
    ('A''NRA Approved'),
    (
    'CMP''Civilian Marksmanship Program'),
    (
    'P''Practice'),
    (
    'R''NRA Registered');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_scores`
    --

    CREATE TABLE `tbl_scores` (
      `
    idint(11NOT NULL AUTO_INCREMENT,
      `
    competitor_fkint(11NOT NULL,
      `
    stage_fkint(11NOT NULL,
      `
    eqpt_fkchar(5NOT NULL,
      `
    pointsint(11NOT NULL,
      `
    xcountdecimal(10,0NOT NULL,
      
    PRIMARY KEY (`id`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=;

    --
    -- 
    Dumping data for table `tbl_scores`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_special_categories`
    --

    CREATE TABLE `tbl_special_categories` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_special_categories`
    --

    INSERT INTO `tbl_special_categories` (`name`, `descr`) VALUES
    ('CIV''Civilian'),
    (
    'COL''Collegiate'),
    (
    'GS''Grand Senior'),
    (
    'IJ''Intermediate Junior'),
    (
    'JR''Junior'),
    (
    'LE''Police'),
    (
    'MIL''Active-duty Military'),
    (
    'NG''National Guard'),
    (
    'RES''Reserve other than NG'),
    (
    'SCH''Primary/Secondary school students'),
    (
    'SJ''Sub-Junior'),
    (
    'SR''Senior'),
    (
    'W''Woman');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_stages`
    --

    CREATE TABLE `tbl_stages` (
      `
    stage_numberint(11NOT NULL,
      `
    match_fkint(11NOT NULL,
      `
    namevarchar(50) DEFAULT NULL,
      `
    shotsint(11NOT NULL,
      `
    distanceint(11NOT NULL,
      
    PRIMARY KEY (`stage_number`,`match_fk`),
      
    KEY `match_fk` (`match_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_stages`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_tournaments`
    --

    CREATE TABLE `tbl_tournaments` (
      `
    idint(11NOT NULL,
      `
    namevarchar(50NOT NULL,
      `
    descrvarchar(255) DEFAULT NULL,
      `
    start_datedate DEFAULT NULL,
      `
    end_datedate DEFAULT NULL,
      `
    sanction_fkchar(5NOT NULL,
      
    PRIMARY KEY (`id`),
      
    KEY `sanction_fk` (`sanction_fk`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_tournaments`
    --


    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_tournament_category`
    --

    CREATE TABLE `tbl_tournament_category` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_tournament_category`
    --

    INSERT INTO `tbl_tournament_category` (`name`, `descr`) VALUES
    ('300m''International 300m'),
    (
    'FB''International Fullbore'),
    (
    'HP''High Power Rifle');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `tbl_tournament_type`
    --

    CREATE TABLE `tbl_tournament_type` (
      `
    namechar(5NOT NULL,
      `
    descrvarchar(50NOT NULL,
      
    PRIMARY KEY (`name`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- 
    Dumping data for table `tbl_tournament_type`
    --

    INSERT INTO `tbl_tournament_type` (`name`, `descr`) VALUES
    ('300m''300m'),
    (
    'FB''Fullbore'),
    (
    'LRP''Long-Range Prone'),
    (
    'MRP''Mid-Range Prone'),
    (
    'SR''Sporting Rifle'),
    (
    'XTC''Conventional');

    --
    -- 
    Constraints for dumped tables
    --

    --
    -- 
    Constraints for table `tbl_competitor_category`
    --
    ALTER TABLE `tbl_competitor_category`
      
    ADD CONSTRAINT `tbl_competitor_category_ibfk_1FOREIGN KEY (`competitor_fk`) REFERENCES `tbl_competitors` (`id`),
      
    ADD CONSTRAINT `tbl_competitor_category_ibfk_2FOREIGN KEY (`spec_category_fk`) REFERENCES `tbl_special_categories` (`name`);

    --
    -- 
    Constraints for table `tbl_competitor_class`
    --
    ALTER TABLE `tbl_competitor_class`
      
    ADD CONSTRAINT `tbl_competitor_class_ibfk_1FOREIGN KEY (`competitor_fk`) REFERENCES `tbl_competitors` (`id`),
      
    ADD CONSTRAINT `tbl_competitor_class_ibfk_2FOREIGN KEY (`classification_fk`) REFERENCES `tbl_classifications` (`name`),
      
    ADD CONSTRAINT `tbl_competitor_class_ibfk_3FOREIGN KEY (`discipline_fk`) REFERENCES `tbl_discipline` (`name`);

    --
    -- 
    Constraints for table `tbl_contact_info`
    --
    ALTER TABLE `tbl_contact_info`
      
    ADD CONSTRAINT `tbl_contact_info_ibfk_1FOREIGN KEY (`competitor_fk`) REFERENCES `tbl_competitors` (`id`);

    --
    -- 
    Constraints for table `tbl_discipline`
    --
    ALTER TABLE `tbl_discipline`
      
    ADD CONSTRAINT `tbl_discipline_ibfk_1FOREIGN KEY (`category_fk`) REFERENCES `tbl_tournament_category` (`name`),
      
    ADD CONSTRAINT `tbl_discipline_ibfk_2FOREIGN KEY (`type_fk`) REFERENCES `tbl_tournament_type` (`name`);

    --
    -- 
    Constraints for table `tbl_matches`
    --
    ALTER TABLE `tbl_matches`
      
    ADD CONSTRAINT `tbl_matches_ibfk_1FOREIGN KEY (`discipline_fk`) REFERENCES `tbl_discipline` (`name`),
      
    ADD CONSTRAINT `tbl_matches_ibfk_2FOREIGN KEY (`tournament_fk`) REFERENCES `tbl_tournaments` (`id`);

    --
    -- 
    Constraints for table `tbl_stages`
    --
    ALTER TABLE `tbl_stages`
      
    ADD CONSTRAINT `tbl_stages_ibfk_1FOREIGN KEY (`match_fk`) REFERENCES `tbl_matches` (`match_number`);

    --
    -- 
    Constraints for table `tbl_tournaments`
    --
    ALTER TABLE `tbl_tournaments`
      
    ADD CONSTRAINT `tbl_tournaments_ibfk_1FOREIGN KEY (`sanction_fk`) REFERENCES `tbl_sanction` (`name`); 

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by memilanuk View Post
    ... how to reference a composite primary key that envelops two columns of type 'int' and one 'char' column...?
    where is this problem?

    your dump script loaded successfully, it's always easier to look at actual tables, than a vague discussion of table1 and table2, col_a, col_b, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In a past revision that went away as I did the CREATE TABLE dance...

    So no major problems using a junction table as a lookup / foreign key for another junction table - tbl_discipline referenced by tbl_matches and tbl_competitor_class ?

    I still have two areas that work for now, but are going to need some work if this ever expands... a better way of dealing with the hierarchical structure for the tournament categories/types and then tbl_equipment_categories... I need to figure how to tie it back to what discipline is being used for a match - a Service Rifle is not a valid equipment option for an F-Class match, and a Palma rifle isn't a viable choice for a XTC match. Right now I'm looking at either having to make that distinction in the application code, or depend on the user being intelligent enough to not choose options that like that which make no sense... yeah, right


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •