6차 스프린트 기준 ERD

ERD

SQL

create table category
(
    id          bigint       not null auto_increment,
    name        varchar(255) not null,
    member_id   bigint       not null,
    is_default  bit          not null,
    created_at  datetime(6)  not null,
    modified_at datetime(6)  not null,
    primary key (id)
) engine = InnoDB;

create table likes
(
    id          bigint      not null auto_increment,
    member_id   bigint      not null,
    template_id bigint      not null,
    created_at  datetime(6) not null,
    modified_at datetime(6) not null,
    primary key (id)
) engine = InnoDB;

create table member
(
    id          bigint       not null auto_increment,
    name        varchar(255) not null,
    password    varchar(255) not null,
    salt        varchar(255) not null,
    created_at  datetime(6)  not null,
    modified_at datetime(6)  not null,
    primary key (id)
) engine = InnoDB;

create table source_code
(
    id          bigint       not null auto_increment,
    template_id bigint       not null,
    ordinal     integer      not null,
    filename    varchar(255) not null,
    content     TEXT         not null,
    created_at  datetime(6)  not null,
    modified_at datetime(6)  not null,
    primary key (id)
) engine = InnoDB;

create table tag
(
    id          bigint       not null auto_increment,
    name        varchar(255) not null,
    created_at  datetime(6)  not null,
    modified_at datetime(6)  not null,
    primary key (id)
) engine = InnoDB;

create table template
(
    id          bigint                                     not null auto_increment,
    member_id   bigint                                     not null,
    category_id bigint                                     not null,
    title       varchar(255)                               not null,
    description TEXT,
    visibility  enum ('PRIVATE','PUBLIC') default 'PUBLIC' not null,
    created_at  datetime(6)                                not null,
    modified_at datetime(6)                                not null,
    primary key (id)
) engine = InnoDB;

create table template_tag
(
    template_id bigint      not null,
    tag_id      bigint      not null,
    created_at  datetime(6) not null,
    modified_at datetime(6) not null,
    primary key (tag_id, template_id)
) engine = InnoDB;

create table thumbnail
(
    id             bigint      not null auto_increment,
    template_id    bigint      not null,
    source_code_id bigint      not null,
    created_at     datetime(6) not null,
    modified_at    datetime(6) not null,
    primary key (id)
) engine = InnoDB;

create index idx_member_id
    on category (member_id);

alter table category
    add constraint name_with_member unique (member_id, name);

alter table member
    add constraint UK9esvgikrmti1v7ci6v453imdc unique (name);

create index idx_tag_name
    on tag (name);

alter table thumbnail
    add constraint UKi6dbyoqre1oeei7309am7m65p unique (source_code_id);

alter table thumbnail
    add constraint UKqvae6g2xuj6an3wjyc7wau2eb unique (template_id);

alter table category
    add constraint FKd7qtd46ngp06lnc19g6wtoh8t
        foreign key (member_id)
            references member (id);

alter table likes
    add constraint FKa4vkf1skcfu5r6o5gfb5jf295
        foreign key (member_id)
            references member (id);

alter table likes
    add constraint FKbrgthyu05fsswb9ysi2l79g7r
        foreign key (template_id)
            references template (id);

alter table source_code
    add constraint FKjax3t3l28tqs65iqfd3ty5dxw
        foreign key (template_id)
            references template (id);

alter table template
    add constraint FKfke34lch7qf4xixqnyj3h12m7
        foreign key (category_id)
            references category (id);

alter table template
    add constraint FKodvst19gfi7in0ox3dfocuejx
        foreign key (member_id)
            references member (id);

alter table template_tag
    add constraint FK460d2kdmrpffumqaahvh414mv
        foreign key (tag_id)
            references tag (id);

alter table template_tag
    add constraint FKnq3tyggcae26yafql1l0rc91l
        foreign key (template_id)
            references template (id);

alter table thumbnail
    add constraint FKssc68bv1tyrlqpi3fet97rbi
        foreign key (source_code_id)
            references source_code (id);

alter table thumbnail
    add constraint FKgxg8st4s8hfkmbb3mbm34ww49
        foreign key (template_id)
            references template (id)

DBML

https://dbdiagram.io/d/code-zap-6-668f39759939893dae9cef63

Table category {
  id bigint [not null, pk, increment]
  name varchar(255) [not null]
  member_id bigint [not null, ref: > member.id]
  is_default bit [not null]
  created_at datetime(6) [not null]
  modified_at datetime(6) [not null]
  indexes {
      (member_id)
  }
}

Table likes {
  id bigint [not null, pk, increment]
  member_id bigint [not null, ref: > member.id]
  template_id bigint [not null, ref: > template.id]
  created_at datetime(6) [not null]
  modified_at datetime(6) [not null]
}

Table member {
  id bigint [not null, pk, increment]
  name varchar(255) [not null, unique]
  password varchar(255) [not null]
  salt varchar(255) [not null]
  created_at datetime(6) [not null]
  modified_at datetime(6) [not null]
}

Table source_code {
  id bigint [not null, pk, increment]
  template_id bigint [not null, ref: > template.id]
  ordinal integer [not null]
  filename varchar(255) [not null]
  content TEXT [not null]
  created_at datetime(6) [not null]
  modified_at datetime(6) [not null]
}

Table tag {
  id bigint [not null, pk, increment]
  name varchar(255) [not null]
  created_at datetime(6) [not null]
  modified_at datetime(6) [not null]
  indexes {
      (name)
  }
}

Table template {
    id bigint [not null, pk, increment]
    member_id bigint [not null, ref: > member.id]
    category_id bigint [not null, ref: > category.id]
    title varchar(255) [not null]
    description TEXT
    visibility enum('PRIVATE', 'PUBLIC') [default: 'PUBLIC', not null]
    created_at datetime(6) [not null]
    modified_at datetime(6) [not null]
}

Table template_tag {
    template_id bigint [not null, ref: > template.id]
    tag_id bigint [not null, ref: > tag.id]
    created_at datetime(6) [not null]
    modified_at datetime(6) [not null]
    indexes {
        (tag_id, template_id) [pk]
    }
}

Table thumbnail {
    id bigint [not null, pk, increment]
    template_id bigint [not null, ref: > template.id]
    source_code_id bigint [not null, ref: > source_code.id]
    created_at datetime(6) [not null]
    modified_at datetime(6) [not null]
}