paiza.ioでmysql その17

概要

paiza.ioでmysqlやってみた。
練習問題やってみた。

練習問題

データーベース設計せよ。

サンプルコード

-- ユーザー管理
create table users (
    email varchar(255) NOT NULL,
    encrypted_password varchar(10) NOT NULL,
    reset_password_token varchar(10),
    reset_password_sent_at datetime,
    remember_created_at datetime,
    name varchar(40),
    user_id integer,
    introduction text,
    profile_image_id varchar(10),
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- アセット機能
create table books (
    title varchar(255),
    body text,
    user_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- いいね機能
create table favorites (
    user_id integer,
    book_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- コメント機能
create table book_comments (
    comment text,
    user_id integer,
    book_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- フォロー機能
create table relationships (
    follower_id integer,
    followed_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- アドレス機能
create table adrresses (
    user_id integer,
    target_user_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- メール機能
create table mail (
    from_user_id integer,
    to_user_id integer,
    title varchar(255),
    body text,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- チャット機能
create table caht_rooms (
    chat_room_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
create table chat_room_users (
    chat_room_id integer,
    user_id integer,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
create table chat_messages (
    chat_room_id integer,
    user_id integer,
    body text,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL
);
-- ポイント機能
create table point (
    name varchar(40),
    user_id integer,
    point integer
);
-- bbs
CREATE TABLE myposts (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    comment VARCHAR(100) NOT NULL,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
CREATE TABLE list (
    id INTEGER PRIMARY KEY, 
    dbname TEXT, 
    dbnamekana TEXT, 
    kensu INTEGER, 
    updtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    flg INTEGER DEFAULT 0
);
-- bbs
CREATE TABLE `boards`( 
    `id`   integer PRIMARY KEY, 
    `name` TEXT NOT NULL,    
    `text` TEXT NOT NULL     
);

CREATE TABLE `threads`( 
    `timestamp` INT  NOT NULL, 
    `board_id`  INT NOT NULL, 
    `title`     TEXT NOT NULL, 
    PRIMARY KEY( `timestamp`, `board_id` )
);

CREATE TABLE `posts`( 
    `id`               INTEGER PRIMARY KEY AUTO_INCREMENT,
    `board_id`         INT NOT NULL, 
    `thread_timestamp` INT  NOT NULL, 
    `posted_at`        TEXT NOT NULL, 
    `name`             TEXT NOT NULL, 
    `email`            TEXT NOT NULL, 
    `author_hash`      INT NOT NULL, 
    `message`          TEXT NOT NULL, 
    `ip_addr`          TEXT NOT NULL  
);
CREATE INDEX `post_board_thread` ON `posts`( `board_id`, `thread_timestamp` );
CREATE INDEX `post_author_hash` ON `posts`( `author_hash` ); 
-- bbs
create table IF NOT EXISTS bbs (
    id integer primary key,
    name text,
    msg text,
    bbs_date timestamp);
-- bbs
create table mposts (
    id int auto_increment,
    post varchar(255) not null,
    primary key (id)
);
-- bbs
create table article (
    article_id integer primary key auto_increment,
    title text not null,
    body text not null,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp
);

create table tag (
    article_id integer not null,
    tag text not null,
    primary key(article_id)
);

成果物

以上。