231014_과제물 보충

자기 전에 만들었던 설계가 잘못 되었다는걸 알아서 처음부터 다시 만들어봤다.

어제 만든 작업 순서는 상품명->예약->입금->문의고객->고객 순이었는데 이번에는 고객->상품 명->예약->입금->문의고객 순으로 바뀌었다. 고객을 마지막에 만들었더니 마지막에 1:N 관계망 때문에 각 테이블마다 Foreign key를 만들어줘야 했었다. 그걸 없애보고자 다시 한번 설계함. 

1. 고객은 참조를 할 곳이 없으므로(1:N 에서 1) 가장 처음 만듦

2. 상품 명이 고객을 참조(1:N에서 N)하므로 상품 명을 생성. 왜 상품 명이 고객을 참조 하느냐면 1:N 관계망에서는 N에 Foreign key 를 생성해야 하기 때문이다. 고객은 1:N 관계망을 가지지만 1 이므로 가장 먼저 생성됨.

3. 예약은 상품 명과 1:1 관계망을 가지는데, 상품 명은 고객을 참조(1:N)하고 있다.  이를 통해 예약에서 상품명과 고객명 데이터를 불러온다. 1:1 관계망에서는 Foreign key 를 어디에 만들든 상관 없지만, 상품 명에 이미 Foreign key 를 생성했으니 예약에 만들어뒀다. 

4. 입금은 상품예약:입금(1:N) 관계망 과 상품명:입금(1:N) 관계망을 가진다. 상품예약 테이블은 상품명과 1:1 관계망을 가지고, 상품명은 고객을 참조 하고 있다. 입금명이 1:N에서 N이므로 상품예약, 상품명, 고객 에 관한  Foreign key를 생성했다. 이게 맞는지는 잘 모르겠지만 일단 생성함.

5. 문의고객은 상품명, 예약, 입금 테이블과 N:M 관계망을 가진다. 이에 따라 각 테이블을 참조하여 새로운 테이블을 생성했고, 새로운 테이블에 각자 Foreign key 를 생성했다.

결과적으로 불필요하게 Foreign key를 따로 추가 할 필요 없이 생성할 수 있었다. 

CREATE DATABASE Customer_list_2;
USE Customer_list_2;

CREATE TABLE Customer_Information (
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR (20),
    Address VARCHAR (50),
    tell VARCHAR (20),
    email VARCHAR (20),
    membership DATE,
    PRIMARY KEY(seq)
);

CREATE TABLE product_name (
    seq INT NOT NULL AUTO_INCREMENT,
    product_number VARCHAR(30),
    category VARCHAR(30),
    material VARCHAR(30),
    weight VARCHAR(30),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq)
);

CREATE TABLE product_reservation (
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    Product_number INT,
    confirmation_of_deposit DATE,
    Address VARCHAR(50),
    tell VARCHAR(20),
    email VARCHAR(20),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq),
    FOREIGN KEY (Product_number) REFERENCES product_name(seq)
);

CREATE TABLE confirmation_of_deposit (
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    email VARCHAR(20),
    deposit_type VARCHAR(40),
    card_number VARCHAR(50),
    price INT,
    product_reservation_id INT,
    product_name_id INT,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq),
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq),
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq)
);

CREATE TABLE Questions (
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    Inquiry_code VARCHAR(20),
    email VARCHAR(20),
    date_of_inquiry DATE,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq)
);

CREATE TABLE Questions_ProductName (
    question_id INT,
    product_name_id INT,
    PRIMARY KEY (question_id, product_name_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq)
);

CREATE TABLE Questions_ProductReservation (
    question_id INT,
    product_reservation_id INT,
    PRIMARY KEY (question_id, product_reservation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq)
);

CREATE TABLE Questions_ConfirmationOfDeposit (
    question_id INT,
    confirmation_id INT,
    PRIMARY KEY (question_id, confirmation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (confirmation_id) REFERENCES confirmation_of_deposit(seq)
);

 

그리고 테이블 중 칼럼을 일부 바꾼곳이 있다.

--전
create table product_name ( --상품명
	seq INT NOT NULL AUTO_INCREMENT,
	top_number VARCHAR (30), --상의 넘버
	bottom_number VARCHAR (30), --하의 넘버
	Accessory_number VARCHAR (30), --액세서리 넘버
	shoe_number VARCHAR (30), --신발 넘버
	PRIMARY KEY(seq)
);


--후
CREATE TABLE product_name ( --상품명
    seq INT NOT NULL AUTO_INCREMENT,
    product_number VARCHAR(30), --상품명
    category VARCHAR(30), --분류
    material VARCHAR(30), --재질
    weight VARCHAR(30), --무게
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq)
);

데이터의 열에는 하나의 종류(상품명) 에 대한 정보가 들어가고 행에는 해당 열에 대한 정보가 들어가야 하는데, 기존의 칼럼은 상품명에 대한 상의, 하의, 액세서리, 신발 넘버가 모두 들어가는 구조였다 (이것도 데이터를 넣으려고 보니까 알게 된 부분...). 데이터의 구조가 맞지 않다고 생각되어 수정했다. 대략 표로 그린다면 아래와 같다. 

왼쪽이 변경 전, 오른쪽이 변경 후

이제 각 테이블마다 10개의 데이터만 넣으면 됨. 

나도 처음에 설계도? 를 줬을 때 이상하게 준 것 같은데 괜찮을지 모르겠다... 갑자기 걱정됨.