CREATE VIEW Example
Create history tables and a current table with a view over all of them. Insert values. Select data from the current table and from the master view. Update some data and perform the select again.
DROP IF EXISTS current, history_2010, history_2011, history_2012;
CREATE TABLE current (
id INTEGER NOT NULL,
name VARCHAR(20) NOT NULL,
effective_start DATE NOT NULL,
effective_end DATE);
CREATE TABLE history_2010 AS SELECT * FROM current;
CREATE TABLE history_2011 AS SELECT * FROM current;
CREATE TABLE history_2012 AS SELECT * FROM current;
CREATE VIEW master AS SELECT id, name, effective_start, effective_end FROM current
UNION ALL
SELECT id, name, effective_start, effective_end FROM history_2010 WHERE effective_end IS NOT NULL
UNION ALL
SELECT id, name, effective_start, effective_end FROM history_2011 WHERE effective_end IS NOT NULL
UNION ALL
SELECT id, name, effective_start, effective_end FROM history_2012 WHERE effective_end IS NOT NULL;
\p\g
INSERT INTO current (id, name, effective_start) VALUES (1,'tins','2012-01-23'),(2,'bags','2012-01-30'),(3,'boxes','2012-01-30');
INSERT INTO history_2010 (id, name, effective_start, effective_end) VALUES
(1,'tins','2010-01-23','2011-09-30'),(2,'bags','2010-01-30','2010-11-01'),(3,'boxes','2010-12-25','2011-03-20');
INSERT INTO history_2011 (id, name, effective_start, effective_end) VALUES
(1,'tins','2011-09-30','2012-01-23'),(2,'bags','2010-11-01','2012-01-30'),(3,'boxes','2011-03-20','2012-01-30');
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(CASE WHEN IFNULL(effective_end,'0001-01-01') = '0001-01-01' THEN 'CURRENT' ELSE CHAR(effective_end) END) FROM current GROUP BY id ORDER BY 2;
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(effective_end) FROM master GROUP BY id ORDER BY 2;
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(effective_end) FROM history_2011 GROUP BY id ORDER BY 2;
\p\g
UPDATE current SET effective_end = CURRENT_DATE WHERE id = 2;
INSERT INTO history_2012 SELECT * FROM current WHERE effective_end IS NOT NULL;
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(CASE WHEN IFNULL(effective_end,'0001-01-01') = '0001-01-01' THEN 'CURRENT' ELSE CHAR(effective_end) END) FROM current GROUP BY id ORDER BY 2
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(effective_end) FROM master GROUP BY id ORDER BY 2
\p\g
SELECT id, MAX(name), MAX(effective_start), MAX(effective_end) FROM history_2011 GROUP BY id ORDER BY 2
\p\g