[문과 코린이의 IT 기록장] RDBMS Modeling 실습 - 고객 및 관련 테이블 생성, Product 관련 테이블 생성, 인덱스의 이해, View 만들기, Product 관련 테이블 수정, 창고(재고/입고/출고) 관리
RDBMS Modeling 실습 - 인프런 | 강의
데이터베이스 설계 기초편에 이은 두 번째 강의입니다. 실전 프로젝트를 대상으로 처음부터 끝까지 만들어보는 실전 위주의 수업이며, 강의 내용을 모두 이해하고 나면 자유자재로 데이터베이
www.inflearn.com
[문과 코린이의 IT 기록장] RDBMS Modeling 실습 - User 테이블 설계, 시도 시군구 뷰 만들기, 시도 시군
[문과 코린이의 IT 기록장] RDBMS Modeling 실습 - User 테이블 설계, 시도 시군구 뷰 만들기, 시도 시군구 저장 프로시저 만들기, Company 뷰, 저장 프로시저 만들기, User 관련 사..
vansoft1215.tistory.com
1. 고객 및 관련 테이블 생성
- Customer과 Company는 마스터 테이블 : 다른 것에 의지하지 않고 그냥 그 자체로 존재하는 것들이기 때문
- 이 두 테이블이 서로 관계를 가지기 위해서는, Sales라는 관계 테이블이 필요하다.
* 관계 테이블은 시간의 개념이 반드시 들어가야 한다. ex. IssueDate : 언제 물건을 팔았는지
* Sales 테이블은 또 다른 마스터 테이블인 Product 테이블을 참조하게 된다. (ps. Prodcut 테이블은 쿠팡과 같이 상품이 많이 움직이는 경우와, 대리점과 같이 많이 움직이지 않는 경우가 존재한다. 상황에 맞게 테이블을 구성할 필요가 있다.)
- TB_Worker 테이블 : 주문시 특정 고객에 대한 담당자 테이블
- TB_Sales 테이블 : 상품 판매 테이블
* Sales 테이블에서의 PK - CustomerId, CompanyId + Seq
* Sales테이블에서 UserId나 WorkerId까지 PK로 두면, 제약사항이 많아진다.
ex 1. 만약 UserId 1번의 사람이 사표를 내고, 2번에게 인수인계를 해주게 되면, Sales 테이블에서 UserId가 변화하게 된다. 그런데 PK가 변화하는 것은 굉장히 큰 문제를 야기할 수 있다.
ex 2. 또한 이 둘을 PK로 두게 되면, 하나의 담당자는 한 고객에게 한번만 상품을 판매할 수 있게 되는 구성을 하게 될 것이다.
=> 따라서, UserId, WorkerId, IssueDate는 PK가 아닌 속성 값으로추가함으로써, 융통성을 보장하는 것이 필요하다.
2. Product 관련 테이블 생성
[ ProdcutType 테이블 ]
- 해당 상품 종류가 무엇인지 (가장 큰 대분류) ex. 의류, 신발, 모자
- 확장성을 거의 가지지 않는 테이블. 따라서 프로그램에서 Enum으로 사용할 확률이 굉장히 높다. (자동증분 X)
public enum ProductTypeEnum
{
의류 = 1,
신발 = 2,
모자 = 3,
우산 = 4,
백팩 = 5,
기타 = 99
}[ TB_LCategory / TB_MCateogry / TB_SCategory ]
- 카테고리는 상황에 따라 수시로 변경될 수 있기 때문에, enum으로 사용하기는 어렵다.
- 그렇지만 기준 테이블로서 사용된다.
- LCateogry는 자동 증분으로 설정하며, MCateogry와 SCateogry는 Stored Procedure를 사용해서 증분을 사용할 수 있도록 설정해야 한다.
[ TB_Color / TB_ProductColor ]
- Color 관련 정보를 Product 테이블 내에 넣게 되면, 하나의 상품은 하나의 Color만 가질 수 밖에 없게 된다.
- 따라서 Color 테이블(TB_Color)를 따로 생성해준다. // Color는 수도 없이 바뀔 수 있기 때문에, 자동증분 O (Enum 사용X)
- 그런데 이 TB_Color는 직접 TB_Product와 연결할 수 없다. 그러므로 둘을 연결하는 관계 테이블인 TB_ProductColor 테이블을 새로 생성해 준다.
cf ) TB_ProductColor 테이블에서 컬럼들을 참조할 때, ProductId 컬럼은 TB_Product의 PK이기 때문에 해당 테이블과 연결하고, Category 관련 내용들은 SCateogry에 PK로 한번에 설정되어 있기 때문에 해당 테이블과 연결하며, ProdcutTypeId는 TB_ProductType과, ColorId는 TB_Color과 연결한다.
[ TB_Size / TB_ProductSize ]
- Size 관련 정보 또한, 하나의 품목이 여러 Size를 가질 수 있다.
- 따라서 별도의 TB_Size테이블을 만들어주며, TB_ProductSize를 통해 두 테이블을 연결시켜준다.
[ TB_Sales와 TB_Product 사이의 관계 ]
- TB_Sales에서 Product는 변화할 수 있다.
- 즉, 판매 테이블에서 특정 Company와 Customer가 거래했다는 것은 변화하면 안되지만, 어떤 상품을 판매했는지는 변화할 수 있을 것이다. 따라서 PK가 아닌 일반 속성으로 ProductId가 들어가게 된다.
[ TB_ProducPrice ]
- 하나의 Product더라도 Price는 변화할 수 있다.
- 따라서 별도의 TB_ProductPrice 테이블을 만들어서, ProductId, ProductTypeId, L/M/SCategoryId, ProductSizeId에 따라, Price 값을 가질 수 있도록 만들어주는 것이 필요하다.
[ 전체 테이블 구성 Diagram ]
3. 인덱스의 이해
1) 관계 테이블 Index
- TB_Sales와 같은 관계테이블은, 시간의 흐름에 따라 레코드가 끝도 없이 늘어난다.
- 레코드 수가 너무 많아서, 조회할 양이 끝없이 늘어나게 되면, 어느 한계를 넘는 순간 DB가 뻗어버리게 된다.
ex. 레코드가 총 2억개일 경우, 9사람만 해당 테이블을 조회하더라도, 18억개를 조회하게 된다.
- 관계 테이블에서는 날짜(IssueDate)가 중요한 요소가 되며, 이와 같이 조회될 가능성이 있는 조건 컬럼들을 index로 설정해두는 것이 중요하다.
ex. TB_Sales 테이블에서는, 자주 조건으로 지정될만한 컬럼인, IssueDate / UserId / ProductId를 Index로 지정하면 좋다.
- Index는 한 테이블에서 4-5개 정도로 지정하는 것이 적당하다. Index가 많아질 경우 빨리 찾을 수 있다는 장점을 가지지만, 너무 많아지면 해당 테이블에 Insert할 경우 부하가 걸릴 수 있다.
- 그렇지만, 꼭 Index를 지정해야 하는 것은 아니다. 관계 테이블이 아닌 마스터 테이블인 경우, 즉 예를 들어 TB_Worker인 경우, 중소기업이라면 아무리 많아도 레코드 수(직원수)가 1000명을 넘기가 쉽지 않다. 따라서 이러한 경우는 그냥 조회를 진행해도 딱히 큰 문제가 발생하지 않는다.
4. View 만들기
- 가장 중심이 되는 테이블들만 남기고 나머지들을 제거하면, 이 4개의 테이블이 나온다.
- 이 테이블들을 중심으로 관련 View를 만들고자 한다.
[ VW_User ]
SELECT * FROM TB_User
-- CompanyId, UserLevelId, RegisterId, Modifierid이 무엇인지 알기가 쉽지 않음. (숫자로만 쓰여져 있기 때문에, 이를 한글로 알 수 있도록 명시해줘야 함)
-- 이것들이 다 다른 테이블에 존재하는데, 이를 알기 위해 view를 생성한다.
-- 뷰 : 테이블을 join해서 가상의 테이블로 보자는 것- TB_User의 컬럼들 중, not null인 컬럼은 다른 컬럼과 join할 때 inner join을 사용할 수 있다.
- 왜냐하면, inner join을 사용함으로써 null값으로 인해 뷰 상에서 누락될 위험성이 없기 때문이다.
cf 1 ) DB 상에서 view를 설계한 후, 프로그래머에게 줄 때, 주요 컬럼은 누락시키지 않고 모두 반환시켜줘야 한다. 해당 컬럼을 사용할지 안할지는 프로그래머가 결정하는 것이기 때문이다.
cf 2 ) 프로그램상에서는 아마 CompanyId, UserId, UserLevelId, IsDeleted, RegisterId, ModifierId는 제외되고 화면상에 표출될 것으로 예상된다.
- 뷰 정의
SELECT a.CompanyId, b.CompanyName, a.UserId, a.UserName, a.LoginId, a.Password, a.UserLevelId, c.UserLevelName, a.IsDeleted, a.RegisterId, a.RegisterDate, a.ModifierId, a.LastModifiedDate, a.Exam, d.UserName AS RegisterName, f.UserName AS ModifierName
FROM dbo.TB_User AS a
INNER JOIN dbo.TB_Company AS b ON a.CompanyId = b.CompanyId
LEFT OUTER JOIN dbo.TB_UserLevelCode AS c ON a.UserLevelId = c.UserLevelId
INNER JOIN dbo.TB_User AS d ON a.CompanyId = d.CompanyId AND a.RegisterId = d.UserId
LEFT OUTER JOIN dbo.TB_User AS f ON a.CompanyId = f.CompanyId AND a.ModifierId = f.UserId
- 뷰 호출
SELECT * FROM VW_User ORDER BY CompanyId, UserName
[ VW_Product ]
SELECT a.ProductId, a.ProductName, a.ProductTypeId, a.LCategoryId, a.MCategoryId, a.SCategoryId, b.ProductTypeName, c.SCategoryName, d.MCategoryName, e.LCategoryName
FROM dbo.TB_Product AS a
INNER JOIN dbo.TB_ProductType AS b ON a.ProductTypeId = b.ProductTypeId
INNER JOIN dbo.TB_SCategory AS c ON a.SCategoryId = c.SCategoryId AND a.MCategoryId = c.MCategoryId AND a.LCategoryId = c.LCategoryId
INNER JOIN dbo.TB_MCategory AS d ON a.LCategoryId = d.LCategoryId AND a.MCategoryId = d.MCategoryId
INNER JOIN dbo.TB_LCategory AS e ON a.LCategoryId = e.LCategoryId- TB_Product는 모든 컬럼이 Not Null이기 때문에 따로 고려해줄 사항이 없으며, 따라서 모두 inner join을 사용해도된다.
[ VW_Sales ]
SELECT a.CompanyId, a.CustomerId, a.Seq, a.ProductId, a.UserId, a.WorkerId, a.IssueDate, b.CompanyName, c.CustomerName, d.ProductName, e.UserName, f.WorkerName
FROM dbo.TB_Sales AS a
INNER JOIN dbo.TB_Company AS b ON a.CompanyId = b.CompanyId
INNER JOIN dbo.TB_Customer AS c ON a.CustomerId = c.CustomerId
INNER JOIN dbo.TB_Product AS d ON a.ProductId = d.ProductId
LEFT OUTER JOIN dbo.TB_User AS e ON a.CompanyId = e.CompanyId AND a.UserId = e.UserId
LEFT OUTER JOIN dbo.TB_Worker AS f ON a.CustomerId = f.CustomerId AND a.WorkerId = f.WorkerId
-- TB_Product에서 UserId와 WorkerId 컬럼은 Null을 허용하고 있다. 따라서 left outer join으로 연결해줘야 한다.
-만약 ProductType, Category, Color, Size 등 관련 정보들을 추가로 보여달라고 요청이 들어온다면?
(1) 사전작업
: TB_ProductSize에 SizeId컬럼을 추가하고, TB_Size 테이블의 PK-FK 관계를 제거한다.
: 이후 TB_Sales에 TB_ProductSize의 기본키 컬럼들을 추가한 후, 이 둘 간의 PK_FK 관계를 설정한다.
* ProductId / ProductTypeId / LCategoryId / SizeId
(2) VW_Sales 구성
SELECT a.CompanyId, a.CustomerId, a.Seq, a.ProductId, a.UserId, a.WorkerId, a.IssueDate, b.CompanyName, c.CustomerName, d.ProductName, e.UserName, f.WorkerName, j.ProductSizeName, a.ProductTypeId, a.LcategoryId, a.SizeId, g.ProductTypeName
FROM dbo.TB_Sales AS a
INNER JOIN dbo.TB_Company AS b ON a.CompanyId = b.CompanyId
INNER JOIN dbo.TB_Customer AS c ON a.CustomerId = c.CustomerId
INNER JOIN dbo.TB_Product AS d ON a.ProductId = d.ProductId
LEFT OUTER JOIN dbo.TB_User AS e ON a.CompanyId = e.CompanyId AND a.UserId = e.UserId
LEFT OUTER JOIN dbo.TB_Worker AS f ON a.CustomerId = f.CustomerId AND a.WorkerId = f.WorkerId
INNER JOIN dbo.TB_ProductType AS g ON a.ProductTypeId = g.ProductTypeId
INNER JOIN dbo.TB_ProductSize AS j ON a.ProductId = j.ProductId AND a.ProductTypeId = j.ProductTypeId AND a.LcategoryId = j.LCategoryId AND a.SizeId = j.ProductSizeId
5. Product 관련 테이블 수정
[ TB_Product ]
- Product에 필요한 추가 정보(속성) : Color / Size / Price / MakerId 등.. // 특정 Product를 설명하는 수식어
* Price : Product의 Price와, Sales의 Price는 다를 수 있다. (할인가가 적용되는 등 현상이 발생할 수 있기 때문)
- ProductId를 PK로 설정함으로써, ProductTypeId - LCategoryId - MCategoryId - SCategoryId - ColorId - SizeId의 속성을 갖는, ProductName을 구분할 수 있다.
ex. ProductId(1) : 의류 - 여성의류 - 상의 - 블라우스 - 하얀색 - S사이즈 : 꽃무늬 블라우스
- ProductName을 Unique 설정해 줌으로써, 구체적 제품명은 중복되지 않도록 설정한다.
cf) 블라우스도 여러 종류가 있기 때문에, ProductName을 설정하는 것은 반드시 필요하다.
** TB_Product는 마스터 테이블 중 중심 테이블이기 때문에, 대부분 다른 테이블과의 관계에서 자식테이블의 역할을 한다.
(키가 있는 쪽이 부모테이블)
[ TB_ProductColor / TB_ProductSize ]
- ProductId(FK) + ProductColorId / ProductSizeId 를 PK로 설정해준다.
// ProductId는 TB_Product로부터 참조하는 것이다.
// ProductColorId / ProductSizeId는 각각의 테이블이 참조를 받게 되는 형식이다.
- 해당 제품이 무엇인지를 파악하기 위해서는, ProductId로 찾아낼 수 있다.
[ TB_Sales ]
- Sales에 필요한 추가 정보(속성) : UnitPrice / Amout / TotSum / Vat
cf) TB_Sales에서 ProductId 대신 Seq가 PK로 들어가는 이유 : ProductId가 PK로 들어가게 되면 회사-고객 간 하나의 상품은 한번만 판매될 수 있기 때문이다.
** TB_Sales는 관계 테이블 중 중심 테이블이기 때문에, 대부분 다른 테이블과의 관계에서 자식테이블의 역할을 한다.
(키가 있는 쪽이 부모테이블)
[ 수정 후, 전체 테이블 구성 Diagram ]
6. 창고(재고/입고/출고) 관리
- 하나의 제품은 Category별로, Size별로 모두 라벨을 붙여서, 창고를 지정해 나눠 관리하게 된다.
- 따라서 ERP 시스템의 경우 창고 관리 부분도 함께 고려해줘야 한다.
- 창고 관리는 회계쪽과도 연관된다. ex. 잔존가치, 선입선출 등..
| - 창고 : TB_Warehouse - 입고 : TB_WarehouseIn - 출고 : TB_WarehouseOut - 재고 : TB_Stock - 기초재고 : TB_BasicStock // 세무와 관련이 깊은 부분. (기준점의 역할을 함) |
[ TB_Warehouse ]
- WarehouseId : 창고 Id
- WarehouseName : 창고명
[ TB_Stock ]
cf ) ProductId + WarehouseId를 함께 PK로 설정한다는 것 : 하나의 Product가 여러 창고에 존재한다는 것 표현 가능
- ProductId : 어떤 제품이 (PK + FK)
- WarehouseId : 어떤 창고에 있는 (PK + FK)
- Amount : 재고량
- MiniumAmount : 적정재고 // 지정한 재고수량보다 떨어지면, 자동으로 발주를 진행하도록 하는 시스템 등을 구축할 수 있음.
[ TB_BasicStock ]
- ProductId + WarehouseId (PK + FK)
- BasicAmount : 기초재고량
- BasicAveragePrice : 기초평균단가
- RegisterId : 최초등록자
- ReisterDate : 최초등록일
- ModifierId : 최종등록자
- LastModifiedDate : 최종등록일
[ TB_WarehouseIn ]
- ProductId + WarehouseId (PK + FK)
- IssueDate : 발생일
- Amount : 입고량
[ TB_WarehouseOut ]
- ProductId + WarehouseId (PK + FK)
- IssueDate : 발생일
- Amount : 입고량
cf ) TB_WarehouseIn과 TB_WarehouseOut의 컬럼 구성이 비슷하다고, 둘을 합쳐서 테이블을 만드는 것은 좋지 않다.
=> 정규화 위반의 문제가 발생할 수 있다.
a. In과 Out을 다른 테이블로 만든 후, Join시키게 될 경우 아래와 같은 결과가 자연스럽게 나오게 된다.
b. 아래와 같이 TB_WarehouseInOut 테이블을 만들고, IsIn 컬럼을 통해 (True : 입고, Flase : 출고)를 구분하게 되면, Join시킬 경우 조건을 더 많이 걸어야 하기 때문에, 그 과정에서 다양한 문제들이 생길 수 있다.
또한 테이블 내에 입고만 가지는 속성, 출고만 가지는 속성 등이 추가될 경우, 컬럼들이 값을 가지지 않고 null값으로 비어만 있을 가능성이 높다.
따라서 가능하면, 정석대로 테이블을 분할해 구성해야 한다.
- 테이블을 모두 구성한 후에는, Stored Procedure를 만들어서, Add(Create) / Delete / Update / Select(Read)를 구현해주면 된다. 즉 CRUD를 구현해주면 된다는 것이다.
- 트랜잭션 관련 사항은 여기서 고려할 필요는 없다. 프로그래밍을 할 때, 한 번에 고려해주면 된다.
* 이 창고 관리 부분에서 프로그래밍시, 트랜잭션 고려사항
1. Basic Stock에 Insert시 => Stock에 동시에 재고가 반영되도록 트랜잭션 필요 (+)
2. WarehouseIn에 Insert시 => Stock에 동시에 재고가 반영되도록 트랜잭션 필요 (+)
3. WarehouseOut에 Insert시 => Stock에 동시에 재고가 반영되도록 트랜잭션 필요 (-)
[ 전체 테이블 구성 Diagram ]
| * 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :) |
