49 lines
1.4 KiB
SQL
49 lines
1.4 KiB
SQL
create database if not exists community_activity default character set utf8mb4 collate utf8mb4_general_ci;
|
|
use community_activity;
|
|
|
|
create table if not exists sys_user (
|
|
id bigint primary key auto_increment,
|
|
username varchar(64) not null unique,
|
|
password_hash varchar(255) not null,
|
|
nickname varchar(64) not null,
|
|
phone varchar(32),
|
|
role varchar(32) not null default 'user',
|
|
created_at datetime not null,
|
|
updated_at datetime not null
|
|
);
|
|
|
|
create table if not exists activity (
|
|
id bigint primary key auto_increment,
|
|
title varchar(255) not null,
|
|
term varchar(64) not null,
|
|
summary varchar(255),
|
|
content text,
|
|
location varchar(255) not null,
|
|
start_time datetime not null,
|
|
end_time datetime not null,
|
|
signup_start datetime not null,
|
|
signup_end datetime not null,
|
|
quota int not null,
|
|
status varchar(32) not null,
|
|
cover_url varchar(255),
|
|
created_by bigint,
|
|
created_at datetime not null,
|
|
updated_at datetime not null,
|
|
index idx_activity_status (status),
|
|
index idx_activity_time (start_time)
|
|
);
|
|
|
|
create table if not exists activity_signup (
|
|
id bigint primary key auto_increment,
|
|
activity_id bigint not null,
|
|
user_id bigint not null,
|
|
status varchar(32) not null,
|
|
checkin_status varchar(32) not null,
|
|
signed_at datetime not null,
|
|
canceled_at datetime,
|
|
checkin_at datetime,
|
|
unique key uk_activity_user (activity_id, user_id),
|
|
index idx_activity (activity_id),
|
|
index idx_user (user_id)
|
|
);
|