db.sql

CREATE DATABASE IF NOT EXISTS java_design;
use java_design;
-- 社团表
create table clubs(
    id int auto_increment primary key ,
    name varchar(100) not null,
    type varchar(50),
    established_date date
);

-- 组织者表
create table organizers(
    id int auto_increment primary key,
    name varchar(100) not null,
    contact_info varchar(255),
    club_id int,
    foreign key (club_id) references clubs(id) on delete cascade
);

-- 会员表
create table members(
    id int auto_increment primary key,
    name varchar(100) not null,
    student_id varchar(20) unique not null,
    photo_url varchar(255),
    club_id int,
    interests text,
    foreign key (club_id) references clubs(id) on delete cascade
);

-- 多媒体素材表
create table media(
    id int auto_increment primary key,
    member_id int not null,
    file_name VARCHAR(255) not null,
    file_path varchar(512) not null,
    type ENUM('photo','certificate','audio','video') not null,
    upload_time TIMESTAMP DEFAULT NOW(),
    description varchar(255),
    foreign key (member_id) references members(id) on delete cascade
);