Create Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created TIMESTAMP,
modified TIMESTAMP
);
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
slug VARCHAR(191) NOT NULL,
body TEXT,
published BOOLEAN DEFAULT FALSE,
created TIMESTAMP,
modified TIMESTAMP,
UNIQUE (slug),
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
title VARCHAR(191),
created TIMESTAMP,
modified TIMESTAMP,
UNIQUE (title)
);
CREATE TABLE articles_tags (
article_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (article_id, tag_id),
FOREIGN KEY (tag_id) REFERENCES tags(id),
FOREIGN KEY (article_id) REFERENCES articles(id)
);
INSERT INTO users (email, password, created, modified)
VALUES
('[email protected]', 'secret', NOW(), NOW());
INSERT INTO articles (user_id, title, slug, body, published, created, modified)
VALUES
(1, 'First Post', 'first-post', 'This is the first post.', TRUE, NOW(), NOW());