Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

1.0 KB
Raw Blame

CREATE TABLE author (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	active BOOLEAN NOT NULL DEFAULT TRUE,
	rating DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
	bio TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX idx_author_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE book (
	id INT PRIMARY KEY AUTO_INCREMENT,
	author_id INT NOT NULL,
	title VARCHAR(200) NOT NULL,
	price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
	discount DECIMAL(4, 2) NOT NULL DEFAULT 0.00,
	final_price DECIMAL(12, 4) AS (price * (1 - discount)) STORED,
	page_count INT NOT NULL DEFAULT 0,
	CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE OR REPLACE VIEW active_books AS
SELECT b.id, b.title, b.final_price
FROM book b JOIN author a ON b.author_id = a.id
WHERE a.active = TRUE;