Closed railgun-0402 closed 1 week ago
DDL作成
-- hotels
CREATE TABLE IF NOT EXISTS houses (
id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
image_name VARCHAR(255),
description VARCHAR(255) NOT NULL,
price INT NOT NULL,
capacity INT NOT NULL,
postal_code VARCHAR(50) NOT NULL,
address VARCHAR(255) NOT NULL,
phone_number VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- users
CREATE TABLE IF NOT EXISTS users (
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
furigana VARCHAR(50) NOT NULL,
postal_code VARCHAR(50) NOT NULL,
address VARCHAR(255) NOT NULL,
phone_number VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
enabled BOOLEAN NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
role_id INTEGER REFERENCES roles(id)
);
-- reservations
CREATE TABLE IF NOT EXISTS reservations (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
hotel_id INTEGER REFERENCES hotels(id),
check_in_date DATE NOT NULL,
check_out_date DATE NOT NULL,
hotel_name VARCHAR(255) NOT NULL,
number_of_people INTEGER NOT NULL,
fee DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- verification_tokens
CREATE TABLE IF NOT EXISTS verification_tokens (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
token varchar(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- roles CREATE TABLE IF NOT EXISTS roles ( id SERIAL PRIMARY KEY, role_name varchar(50) NOT NULL );
テーブルは入っているのでクローズ
必要なデータを洗い出そう