Inheritance like at OOP with RDBMS

inheritance.sql

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

https://dbfiddle.uk/ovE7_Ktb

Some scenarios

-- make it inaccessible a parent's all childs with one query

UPDATE parentTable
  SET expiredAt = "2023-01-01 00:00:00"
  WHERE id = 1;

SELECT * FROM _childTable
  HAVING _visible=1 AND (_expiredAt > CURRENT_TIMESTAMP OR _expiredAt IS NULL);

-- set visible a parent's a child for exception behavior

UPDATE childTable
  SET visible = 1
  WHERE id = 5;

SELECT * FROM _childTable
  HAVING _visible=1 AND (_expiredAt > CURRENT_TIMESTAMP OR _expiredAt IS NULL);

-- It is necessary to set the same field to null to break the exception behavior
-- SET visible = null