Junction tables referencing other junction tables

Databases
1

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

can’t tell, without the actual table definitions (hint, hint)

3

agree :agree: with r937

absolutely no idea without more info on table structures.

4

<sigh> kinda still at the ERD and planning stage…

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

5

Those are fine, post those.

6

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

1024×519 184 KB

7

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?

8

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…?:xeye:

9

Here is the SQL for all the tables…


-- phpMyAdmin SQL Dump
-- version 3.3.2deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 13, 2010 at 03:35 PM
-- Server version: 5.1.41
-- PHP Version: 5.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` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `mid_init` char(1) DEFAULT NULL,
  `last_name` varchar(30) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `birth_date` date DEFAULT NULL,
  `nra_id` varchar(10) DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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


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

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

CREATE TABLE `tbl_competitor_category` (
  `competitor_fk` int(11) NOT NULL,
  `spec_category_fk` char(5) NOT 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_fk` int(11) NOT NULL,
  `classification_fk` char(5) NOT NULL,
  `discipline_fk` char(5) NOT 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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `competitor_fk` int(11) NOT NULL,
  `street` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state_prov` char(2) NOT NULL,
  `post_code` varchar(10) NOT NULL,
  `country` char(3) NOT NULL DEFAULT 'USA',
  `phone` varchar(10) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `competitor_fk` (`competitor_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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


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

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

CREATE TABLE `tbl_discipline` (
  `name` char(5) NOT NULL,
  `category_fk` char(5) NOT NULL,
  `type_fk` char(5) NOT NULL,
  `descr` varchar(50) NOT NULL,
  `type` enum('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` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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_number` int(11) NOT NULL,
  `discipline_fk` char(5) NOT NULL,
  `tournament_fk` int(11) NOT NULL,
  `name` varchar(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` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `competitor_fk` int(11) NOT NULL,
  `stage_fk` int(11) NOT NULL,
  `eqpt_fk` char(5) NOT NULL,
  `points` int(11) NOT NULL,
  `xcount` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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


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

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

CREATE TABLE `tbl_special_categories` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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_number` int(11) NOT NULL,
  `match_fk` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `shots` int(11) NOT NULL,
  `distance` int(11) NOT 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` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `descr` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `sanction_fk` char(5) NOT 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` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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` (
  `name` char(5) NOT NULL,
  `descr` varchar(50) NOT 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_1` FOREIGN KEY (`competitor_fk`) REFERENCES `tbl_competitors` (`id`),
  ADD CONSTRAINT `tbl_competitor_category_ibfk_2` FOREIGN 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_1` FOREIGN KEY (`competitor_fk`) REFERENCES `tbl_competitors` (`id`),
  ADD CONSTRAINT `tbl_competitor_class_ibfk_2` FOREIGN KEY (`classification_fk`) REFERENCES `tbl_classifications` (`name`),
  ADD CONSTRAINT `tbl_competitor_class_ibfk_3` FOREIGN KEY (`discipline_fk`) REFERENCES `tbl_discipline` (`name`);

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

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

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

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

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

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.

11

In a past revision that went away as I did the CREATE TABLE dance… :wink:

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 :wink: