Java 2주 프로젝트 - 쇼핑몰(1)

 

https://github.com/swknjj/Project_ShoppingMall

GitHub - swknjj/Project_ShoppingMall: 쇼핑몰 프로젝트

1. 주제를 왜 쇼핑몰로 정했는가 ?

요즘 생활에는 쇼핑몰을 이용하는게 일상이 되었기 때문에 한번 구성을 해두면은 참고가 많이 될 것 같았다.

직접 사용도 많이 해보니 다른 쇼핑몰들을 보면 그 구상이 생각나지 않을까 생각하여 정하게 되었다.

 

 

2. ERD ( Entity Relationship Diagram )

데이터베이서는 MySQL을 사용하였다.

 

처음 구상으로 ERD를 먼저 제작하게 되었다.

테이블을 설계를 해둬야 중간중간에 뭘해야 하는지 알 수 있을것 같았다.

하지만 처음에 설계를 해두었음에도 중간중간 테이블을 수정해야 했다.

탄탄한 설계가 프로젝트를 더욱 완성감있게 만드는것임을 다시 깨닫게 되었다.

 

 

3. Table 내부 컬럼

user_table ( 회원들 테이블 )

create table user_table (
	user_id bigint auto_increment primary key,
    email varchar(50),
    email_full varchar(50) unique,
    password varchar(200),
    birth varchar(13) not null,
    nickname varchar(15) unique,
    agree_TOS varchar(50),
    agree_PICU varchar(50),
    agree_promotion varchar(50),
    role varchar(10) default '일반',
    status varchar(10) default '정상',
    created_at datetime default now(),
    updated_at datetime default now()
    );

 

address_table ( 회원들의 주소 테이블 )

create table address_table (
	address_id bigint auto_increment primary key,
    user_id bigint,
    address_name varchar(50),
    receiver varchar(30),
    phone_number varchar(13),
    zipcode varchar(6),
    address1 varchar(50),
    address2 varchar(50),
    reference varchar(100),
    is_default boolean,
    constraint fk_address_table foreign key (user_id) 
    	references user_table(user_id) on delete cascade
    );

 

product_category_table ( 물품들의 카테고리 테이블 )

create table product_category_table (
	category_id bigint auto_increment primary key,
    name varchar(50)
    );

 

seller_table ( 판매자 테이블 )

create table seller_table (
	seller_id bigint auto_increment primary key,
    user_id bigint not null,
    company_name varchar(30),
    representative varchar(10),
    address varchar(200),
    customer_center varchar(15),
    email varchar(50),
    registration_num bigint,
    constraint fk_seller_table foreign key (user_id) 
    	references user_table(user_id) on delete cascade
    );

 

product_table ( 상품 테이블 )

create table product_table (
	product_id bigint auto_increment primary key,
    seller_id bigint not null,
    category_id bigint not null,
    brand varchar(50),
    title varchar(100),
    image varchar(100),
    storedFileName varchar(100),
    price bigint not null,
    discount_rate bigint default 0,
    special_price bigint,
    delivery_fee bigint,
    created_at datetime default now(),
    updated_at datetime default now(),
    rating_sum bigint,
    rating_cnt bigint,
    product_detail varchar(300),
    constraint fk_product_table foreign key (category_id) 
    	references product_category_table(category_id) on delete cascade,
    constraint fk_product_table2 foreign key (seller_id) 
    	references seller_table(seller_id) on delete cascade
    );

 

product_option1_table ( 상품의 옵션 1 테이블 )

create table product_option1_table (
	option_id bigint auto_increment primary key,
    product_id bigint not null,
    content varchar(50),
    price bigint not null,
    stock bigint not null,
    constraint fk_product_option1_table foreign key (product_id) 
    	references product_table(product_id) on delete cascade
    );

 

product_option2_table ( 상품의 옵션 2 테이블 )

create table product_option2_table (
	option_content_id bigint auto_increment primary key,
    option_id bigint not null,
    content varchar(50),
    price bigint not null,
    stock bigint not null,
    constraint fk_product_option2_table foreign key (option_id) 
    	references product_option1_table(option_id) on delete cascade
    );

 

product_image_table ( 상품의 이미지 테이블 )

create table product_image_table (
	image_id bigint auto_increment primary key,
    product_id bigint not null,
    seller_id bigint not null,
    file_name varchar(255),
    storedFileName varchar(100),
    created_at datetime default now(),
    updated_at datetime default now(),
    constraint fk_product_image_table2 foreign key (product_id) 
    	references product_table(product_id) on delete cascade,
    constraint fk_product_image_table4 foreign key (seller_id) 
    	references product_table(seller_id) on delete cascade
    );

 

review_table ( 회원의 리뷰 테이블 )

create table review_table (
	review_id bigint auto_increment primary key,
    user_id bigint not null,
    product_id bigint not null,
    seller_id bigint not null,
    rating bigint,
    review_image varchar(255),
    storedFileName varchar(100),
    content varchar(255),
    liked bigint default 0,
    status varchar(100),
    created_at datetime default now(),
    constraint review_table foreign key (user_id) 
    	references user_table(user_id) on delete cascade,
    constraint review_table2 foreign key (product_id) 
    	references product_table(product_id) on delete cascade,
    constraint seller_id foreign key (seller_id) 
    	references product_table(seller_id) on delete cascade
    );

 

inquiry_table ( 회원의 문의 테이블 )

create table inquiry_table (
	inquiry_id bigint auto_increment primary key,
    user_id bigint not null,
    product_id bigint not null,
    seller_id bigint not null,
    is_buy varchar(10) default 'false',
    is_private varchar(10) default 'false',
    is_answered varchar(10) default 'false',
    created_at datetime default now(),
    category varchar(15),
    content varchar(255),
    constraint inquiry_table foreign key (user_id) 
    	references user_table(user_id),
    constraint inquiry_table2 foreign key (product_id) 
    	references product_table(product_id) on delete cascade,
    constraint inquiry_table3 foreign key (seller_id) 
    	references product_table(seller_id) on delete cascade
    );

 

order_table ( 회원의 주문내역 테이블 )

create table order_table (
	order_id bigint auto_increment primary key,
    product_id bigint not null,
    user_id bigint not null,
    seller_id bigint not null,
    address_id bigint not null,
    quantity bigint not null,
    totalPrice bigint not null,
    status varchar(10) default '정상',
    updated_at datetime default now(),
    created_at datetime default now(),
    memo varchar(50),
    constraint order_table foreign key (product_id) 
    	references product_table(product_id),
    constraint order_table2 foreign key (user_id) 
    	references user_table(user_id) on delete cascade,
    constraint order_table3 foreign key (seller_id) 
    	references product_table(seller_id),
    constraint order_table4 foreign key (address_id) 
    	references address_table(address_id)
    );

아쉬운 점은 설계를 했음에도 product_option2_table을 사용하지 못했다.