Created
February 24, 2023 17:09
-
-
Save yarliganfatih/f51e6ae22a8a3fa8e9fcc6acd7ae0ab8 to your computer and use it in GitHub Desktop.
Inheritance like at OOP with RDBMS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE parentTable ( | |
id int NOT NULL AUTO_INCREMENT, | |
PRIMARY KEY (id), | |
name varchar(50) NOT NULL, | |
createdAt datetime DEFAULT CURRENT_TIMESTAMP, | |
-- Dynamic Columns : own datas | |
visible tinyint(1) NOT NULL DEFAULT 1, -- default is required | |
expiredAt datetime DEFAULT NULL | |
); | |
CREATE TABLE childTable ( | |
id int NOT NULL AUTO_INCREMENT, | |
PRIMARY KEY (id), | |
name varchar(50) NOT NULL, | |
createdAt datetime DEFAULT CURRENT_TIMESTAMP, | |
-- Dynamic Columns : from parentTable or own datas | |
visible tinyint(1) DEFAULT NULL, | |
expiredAt datetime DEFAULT NULL, | |
-- extends | |
parentTable_id INT, | |
FOREIGN KEY (parentTable_id) REFERENCES parentTable(id) ON UPDATE CASCADE ON DELETE CASCADE | |
); | |
CREATE TRIGGER insertChildsForParent | |
AFTER INSERT ON parentTable | |
FOR EACH ROW | |
BEGIN | |
-- Insert multiple rows into the child table | |
INSERT INTO childTable (parentTable_id, name) VALUES | |
(NEW.id, CONCAT(NEW.name, '_child_1')), | |
(NEW.id, CONCAT(NEW.name, '_child_2')); | |
-- Dynamic Columns is null for child rows at the beginning | |
END; | |
-- single child row | |
INSERT INTO childTable (name, visible, expiredAt) VALUES ("Foo", 1, "2025-01-01 00:00:00"); | |
-- one-to-many relationship parent-childs | |
INSERT INTO parentTable (name, visible, expiredAt) VALUES ("Bar", 1, "2026-01-01 00:00:00"); | |
-- one-to-many relationship parent-childs | |
INSERT INTO parentTable (name, visible, expiredAt) VALUES ("Bar_2", 0, "2027-01-01 00:00:00"); | |
-- Pure Tables | |
SELECT * FROM parentTable; | |
SELECT * FROM childTable; | |
-- dynamic childTable | |
CREATE VIEW _childTable AS | |
SELECT | |
ct.id, ct.name, ct.createdAt, pt.name AS parentName, | |
-- parent-independent preferences can be assigned | |
COALESCE(ct.visible, pt.visible) AS _visible, | |
COALESCE(ct.expiredAt, pt.expiredAt) AS _expiredAt | |
FROM childTable ct | |
LEFT JOIN parentTable pt ON pt.id = ct.parentTable_id | |
; | |
SELECT * FROM _childTable; | |
-- get accessable childs | |
SELECT * FROM _childTable | |
HAVING _visible=1 AND (_expiredAt > CURRENT_TIMESTAMP OR _expiredAt IS NULL); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Some scenarios