인프런 - RDBMS Modeling 실습 5일차 (챕터 10,11,12,13,14,15,16,17,18)

10. 회사, 사용자 조회 및 삭제 구현

1. 트랜잭션은 프로그램에서 처리하는게 맞다. 이유로 스토어 프로시저는 디버깅이 어렵기 때문이다.

2. 트랜잭션은 참조 무결성에 의해 참조하는 테이블의 관련된 데이터가 전부 삭제되는게 보장되어야 하고 만약 문제가 발생되었을 때 롤백이 되어야 한다. 

   - 이러한 조건으로 문제 발생시의 디버깅이 어렵기 때문 스토어 프로시저로 만들지 않고 어플리케이션에서 제어함

3. 트랜잭션 락을 사용하자.

11. 삭제 트랜잭션 구현하기

1. try ~ catch를 사용해 commit과 rollback의 위치를 잡아주고 db lock을 사용해 동작별 안전한 처리를 보장해준다.

2. 현재 하고자 하는 동작

   - 사용자를 먼저 지운다. (트랜잭션 사용)

   - 회사를 지운다. (트랜잭션 사용)

3. cascade를 사용하면 위의 단계를 줄이고 개발 코드도 줄일 수 있다.

12. 고객 테이블

1. 테이블의 성격에 따라 명사,간전목적어, 직접목적어, 부사, 동사의 특징을 가지게 된다.

   - 테이블의 관계 : ~하다가 성립하면 동사, 아니면 명사로 보면 된다. 

   - 명사는 주어나 목적어가 된다.

2. 마스터 테이블과 릴레이션 테이블을 잘 사용하는게 고급스킬이다.

   - 대부분 초급 설계에서는 릴레이션 테이블을 잘 사용하지 못한다.

   - 예) TB_User가 릴레이션, TB_Company, TB_UserLevelCode, TB_UserAction이 메인

   - 이러한 테이블의 구조는 정규화를 기반으로 중복이 없이 분리를 하게되면 정리가 된다.

   - 컬럼의 이름은 절대 약어 등의 구분하기 힘든 이름을 사용하지 않는다.

13. 거래 관계테이블 및 상품 테이블 만들기

1. TB_Product, PB_ProductType 테이블을 생성한다.

   - PB_ProductType (기준 테이블)

2. 쇼핑몰 모델링을 할 경우 카테고리를 여성의류, 남성의류, 모자, 신발 이런식으로 나열할 수 있다. 하지만, 모든 제품이 성별로 나뉘어질 수 있기 때문에 차후 개발에 차질이 생길 수 있다.

3. TB_LCategory, TB_SCategory, TB_MCategory 를 만든다. 

   - TB_LCategory : 여성의류, 남성의류, 아동의류, 여성슈즈, 남성슈즈, 여성모자,남성모자,아동모자...

   - TB_SCategory(관계테이블) : 자체키 + TB_LCategory + TB_MCategory 를 복합키로 가짐, 블라우스, 티셔츠 

   - TB_MCategory(관계테이블) : 상의, 하의, 코트 

4. TB_ProductColor(관계테이블), TB_Color를 만든다.

   - color는 앞으로 계속 추가가 될 수 있는 변동 테이블이기에 기준 테이블은 될 수 없다.

5. TB_Size, TB_ProductSize(관계테이블)를 만든다.

6. TB_Sales (관계테이블)를 만든다.

14. 인덱스의 이해

1. 진정한 퍼포먼스 튜닝이란?

   - 주로 사용하게 되는 컬럼 내 데이터를 찾을때, 스캔으로 검색을 하는지 서치로 검색을 하는지가 핵심

      - 인덱스를 잘 설정해줘야 한다

   - 전체 스캔을 하는 내장 함수를 사용한다 (count all), 특히 관계 테이블에 사용 금지

      - 만약 해야 한다면, 인덱스가 되어 있는 곳을 사용 특히 int로 되어 있는 키를 대상으로 하면 실재 전체 데이터가 얼마 되지 않기 때문에 빠르게 결과를 가져올 수 있다.

   - 여러 아이템을 대상으로 검색을 시도할 때, 하나라도 인덱스가 되어 있지 않다면, 해당 과정은 스캔 작업이 일어난다.

2. 하나의 테이블에는 4~5개 인덱스가 적절하다. 인덱스를 너무 많이 설정하면, index 작업에 과부하가 생길 수 있다.

3. 인덱스가 필요한 테이블은 데이터가 시간 축을 기준으로 증가하는 경우에 필요하다. 적은 데이터의 테이블에는 필요가 없다.

4. TB_ProductPrice 테이블을 생성한다.

5. 지금까지 진행한 모든 테이블의 요구사항

   - 스토어드 프로시저가 만들어져 있어야 한다. (CURD)

   - 어플리케이션에 저장소 참조 모델이 만들어져 있어야 한다.

6. 전체 테이블의 관계도

15. 뷰 만들기

1. 모든 기준테이블 및 관계테이블의 키가 모이는 핵심 테이블은 어플리케이션에서 많이 사용된다. 하지만 최종 원하는 데이터를 가져오기 까지는 관련한 키를 역으로 추적하기 위해 다른 테이블을 조인으로 참조해야 된다.

2. ms-sql tool 에서 "select a.* from tb_user as a" 라고 입력하면 자동으로 해당 테이블의 모든 레코드 네임이 자동으로 select 뒤에 명시되어 쿼리가 전환된다.

   - 해당 레코드의 속성 중 null 사용 불가로 되어 있다면, join으로 누락될 데이터가 하나도 없다라는 뜻이 된다. 이 경우 inner join을 사용하면 된다. null을 허용하게 되면, left out join을 사용한다.

   - 각각의 fk를 사용해 뷰를 만들 때에는해당 테이블의 id, 이름 및 가장 많이 사용되는 데이터를 명시한다. 

3. user 테이블 뷰 만들기

   - 사용 방법

      - select * from vw_user order by companyid, username

      - 결과 :

5. productType 테이블의 뷰 만들기

   - 결과 :

6. sales 테이블 뷰 만들기

   - 결과 :

16. Product 테이블 구조 변경

1. product 테이블 뷰 만들기

   - 사용 : select * from vw_product

   - 결과 : 

2. sales 테이블 업데이트

   - 결과 : 

3. 직접 view 테이블을 작성할때 길어진 쿼리로 에러를 찾기 힘들때에는 쿼리 실행문으로 가서 에러를 확인하면 해당 위치들이 빨간색 언더라인이 잡혀 쉽게 확인할 수 있다.

4. 스토어 프로시저를 만들때 select는 무조건 view를 가지고 작성을 해야한다.

17. 사용자, 고객, 상품, 판매 테이블 설계 완료

1. product 테이블에 color, price, size 등의 정보가 추가가 되어애 된다는 것을 확인하게 되었다. 물론 추가적으로 User 테이블도 불필요한 데이터가 있다고 판단되어 수정이 될 수 있다.

* 제일 핵심이 되는 중앙 테이블까지 기본테이블에서 다양한 관계 테이블을 거치게 된다. 그리고 이 사이에 존재하는 id 들은 복합키로 유일성을 유지하며 중앙 테이블까지 접근을 하게 된다.

* 즉 중앙 테이블과 가장 가까운 관계 테이블일 수록 가장 많은 수의 복합키를 가지게 된다. 여기에 상위에게 전달되지 않고 해당 테이블에서 사용성이 정리되는 키는 복합키가 될 필요가 없다.

2. 최종 모델링 결과

3. 전체 모델링

18. 창고(재고/입고/출고) 관리

1. TB_Warehouse(창고), TB_WarehouseIn(입고), TB_WarehouseOut(출고), TB_BasicStock(기초재고), TB_Stock(재고) 테이블을 만든다.

   - 추가로 TB_WarehouseInOut 테이블을 만듬, (TB_warehouseIn과 Out의 아이템이 섞인다. 비정규화가 생긴다)

2. 꼭 필요한 경우에는 성능 등의 이슈로 비정규화 테이블을 만들 수 있지만 기본적으로는 정규화를 최대한 지키는게 좋다.

3. 주요 트랜잭션 정리 - 실패하면 롤백

   - TB_BasicStock은 이 프로그램이 처음 시작될때 초기 값들이다. 해당 테이블에 데이터가 저장되면 자동으로 TB_Stock에 자료가 넘어가야 하는 트랜잭션이 존재한다는 것을 프로그래머들이 알고 있어야 한다.

   - TB_WarehouseIn과 TB_WarehouseOut이 업데이트가 되면 자동으로 그 데이터가 TB_Stock에 반영 되어야 한다.

4. 해당 모델링을 고려할때 TB_BasicStock의 입력을 스토어 프로시저로 자동화하여 TB_Stock에 저장되게 만들 수도 있을 것이다. 하지만, 개발자들 시각을 생각할 필요가 있다. 어플리케이션 단의 트랜잭션에서 해당 기능이 있다고 예상을 할텐데 없는 경우 중복하여 만들 수가 있다.

   - 스토어 프로시저는 CURD 까지만 제공하는 것이 제일 좋다.

   - 추가적으로 개발에서 접근하는 테이블의 아이템을 ENUM으로 제공해주는 것까지가 제일 좋다. 

5. 최종 완성