ER04C1st

1members_history.value
2	
3members:abstract
4	
5CREATE TABLE IF NOT EXISTS `members:abstract` (
6 `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
7 `type` enum('class','elected','appointed','status') NOT NULL,
8  UNIQUE KEY (`id`, `type`)
9) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
10	
11CREATE TABLE IF NOT EXISTS `members:appointed` (
12 `id` INT UNSIGNED NOT NULL PRIMARY KEY, -- not auto_increment
13 `name_prefix` varchar(8) collate utf8_bin NOT NULL COMMENT 'Prefix Added to Members Name',
14 `hours_extra` decimal(4,2) NOT NULL COMMENT 'Hours Given as Bonus for Holding this Position.',
15 `position` varchar(40) collate utf8_bin NOT NULL COMMENT 'Name of the Posisiton',
16 FOREIGN KEY (`id`) REFERENCES `members:abstract` (`id`) ON DELETE CASCADE
17) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Undefined within the SOP or By-Laws.';
18	
19DELIMITER //
20DROP TRIGGER IF EXISTS ins_appointed//
21CREATE TRIGGER ins_appointed BEFORE INSERT ON `members:appointed`
22FOR EACH ROW BEGIN
23  INSERT INTO `members:abstract` (`type`) VALUES ('appointed');
24  SET NEW.id = LAST_INSERT_ID();
25END; //
26DELIMITER ;
27	
28CREATE TABLE IF NOT EXISTS `members_history` (
29 `id` INT unsigned NOT NULL auto_increment COMMENT 'Unique Id',
30 `mid` INT unsigned NOT NULL COMMENT 'Members Unique Id.',
31 `value` INT UNSIGNED NOT NULL,
32 `table` enum('class','elected','appointed','status') NOT NULL,
33 `start` date NOT NULL COMMENT 'Value''s Effect Date',
34 `end` date default NULL COMMENT 'Value''s Expiration Date',
35 PRIMARY KEY (`id`),
36 FOREIGN KEY (`mid`) REFERENCES `members` (`id`) ON DELETE CASCADE,
37 FOREIGN KEY (`value`, `table`) REFERENCES `members:abstract` (`id`, `type`) ON DELETE CASCADE
38) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Member History';
39	
40SELECT m.username,
41  m.password, m.salt, m.name_first, m.name_last,
42  MAX(a.name_prefix) AS name_prefix,
43  COALESCE(MAX(a.hours_extra), MAX(e.hours_extra)) AS hours_extra,
44  MAX(m.date_join) AS date_join,
45  MAX(m.date_leave) AS date_leave,
46  MAX(a.position) AS appointed,
47  MAX(c.class) AS class,
48  MAX(e.position) AS elected,
49  MAX(s.status) AS status
50FROM `members` m 
51JOIN `members_history` h ON (h.mid = m.id)
52LEFT OUTER JOIN `members:appointed` a ON (h.table = 'appointed' AND h.value = a.id)
53LEFT OUTER JOIN `members:class` c ON (h.table = 'class' AND h.value = c.id)
54LEFT OUTER JOIN `members:elected` e ON (h.table = 'elected' AND h.value = e.id)
55LEFT OUTER JOIN `members:status` s ON (h.table = 'status' AND h.value = s.id)
56GROUP BY m.id;