DDL - g-market/b-shop-backend GitHub Wiki

create table if not exists category
(
    id         bigint auto_increment
        primary key,
    created_at datetime(6)  not null,
    updated_at datetime(6)  not null,
    name       varchar(255) not null,
    constraint UK_46ccwnsi9409t36lurvtyljak
        unique (name)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item
(
    id          bigint auto_increment
        primary key,
    created_at  datetime(6)  not null,
    updated_at  datetime(6)  not null,
    base_price  int          not null,
    deleted     bit          not null,
    description text         not null,
    item_status char(8)      not null,
    name        varchar(255) not null,
    open_at     datetime(6)  not null,
    thumbnail   varchar(255) null,
    year        smallint     not null,
    category_id bigint       not null,
    constraint FK2n9w8d0dp4bsfra9dcg0046l4
        foreign key (category_id) references category (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_image
(
    id         bigint auto_increment
        primary key,
    created_at datetime(6)  not null,
    updated_at datetime(6)  not null,
    deleted    bit          not null,
    url        varchar(255) not null,
    item_id    bigint       not null,
    constraint FKta6kqet3u8mv95y7jwtgwqpys
        foreign key (item_id) references item (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_option
(
    id             bigint auto_increment
        primary key,
    created_at     datetime(6)  not null,
    updated_at     datetime(6)  not null,
    deleted        bit          not null,
    description    varchar(255) not null,
    option_price   int          not null,
    stock_quantity int          not null,
    item_id        bigint       not null,
    constraint FKpiufbijq6m8l18jf9a0nwny62
        foreign key (item_id) references item (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_reservation
(
    id         bigint auto_increment
        primary key,
    created_at datetime(6) not null,
    updated_at datetime(6) not null,
    item_id    bigint      not null,
    constraint FK1x64c8jkqlo2rxumrqfpd0yjy
        foreign key (item_id) references item (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists member
(
    id           bigint auto_increment
        primary key,
    created_at   datetime(6)  not null,
    updated_at   datetime(6)  not null,
    email        varchar(255) not null,
    grade        varchar(8)   not null,
    hiworks_id   varchar(255) not null,
    name         varchar(100) not null,
    phone_number char(11)     null,
    role         char(6)      not null,
    constraint UK_bts7dbxk2tw05el19wvguxrv4
        unique (hiworks_id),
    constraint UK_mbmcqelty0fbrvxp1q58dn57t
        unique (email),
    constraint UK_n2qryhkfoqeel6njfhrcq6k7u
        unique (phone_number)
)
    collate = utf8mb4_unicode_ci;

create table if not exists orders
(
    id          bigint auto_increment
        primary key,
    created_at  datetime(6) not null,
    updated_at  datetime(6) not null,
    status      char(9)     not null,
    total_price bigint      not null,
    member_id   bigint      not null,
    constraint FKpktxwhj3x9m4gth5ff6bkqgeb
        foreign key (member_id) references member (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists order_item
(
    id          bigint auto_increment
        primary key,
    created_at  datetime(6) not null,
    updated_at  datetime(6) not null,
    order_count int         not null,
    price       bigint      not null,
    item_id     bigint      not null,
    option_id   bigint      not null,
    order_id    bigint      not null,
    constraint FKija6hjjiit8dprnmvtvgdp6ru
        foreign key (item_id) references item (id),
    constraint FKiord4lw7b58l795tbj9tcwqhs
        foreign key (option_id) references item_option (id),
    constraint FKt4dc2r9nbvbujrljv3e23iibt
        foreign key (order_id) references orders (id)
)
    collate = utf8mb4_unicode_ci;

create table if not exists revinfo
(
    rev      int auto_increment
        primary key,
    revtstmp bigint null
)
    collate = utf8mb4_unicode_ci;

create table if not exists category_aud
(
    id      bigint       not null,
    rev     int          not null,
    revtype tinyint      null,
    name    varchar(255) null,
    primary key (rev, id),
    constraint FKc9m640crhsib2ws80um6xuk1w
        foreign key (rev) references revinfo (rev)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_aud
(
    id          bigint       not null,
    rev         int          not null,
    revtype     tinyint      null,
    base_price  int          null,
    deleted     bit          null,
    description text         null,
    item_status char(8)      null,
    name        varchar(255) null,
    open_at     datetime(6)  null,
    thumbnail   varchar(255) null,
    year        smallint     null,
    category_id bigint       null,
    primary key (rev, id),
    constraint FK7xdof9iyt6pjrlx6nqb7wlbir
        foreign key (rev) references revinfo (rev)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_image_aud
(
    id      bigint       not null,
    rev     int          not null,
    revtype tinyint      null,
    deleted bit          null,
    url     varchar(255) null,
    item_id bigint       null,
    primary key (rev, id),
    constraint FKawb00bilj69ec9lum56qlrc1a
        foreign key (rev) references revinfo (rev)
)
    collate = utf8mb4_unicode_ci;

create table if not exists item_option_aud
(
    id             bigint       not null,
    rev            int          not null,
    revtype        tinyint      null,
    deleted        bit          null,
    description    varchar(255) null,
    option_price   int          null,
    stock_quantity int          null,
    item_id        bigint       null,
    primary key (rev, id),
    constraint FKknp5drqmf4qb3xo48hnemae3q
        foreign key (rev) references revinfo (rev)
)
    collate = utf8mb4_unicode_ci;

create table if not exists shedlock
(
    name       varchar(64)  not null
        primary key,
    lock_until timestamp(3) null,
    locked_at  timestamp(3) null,
    locked_by  varchar(255) null
)
    collate = utf8mb4_unicode_ci;

기본 이미지 사람 : https://gabiaoffice.hiworks.com/gabia.com/common/profile/me