🖍️ MySQL 데이터베이스 설계 기록하기

• 체계적이고 효율적인 데이터 관리를 위해 필수
  
  

• 좋은 데이터베이스 설계

  • 데이터의 무결성을 보장
  • 중복을 최소화
  • 시스템의 성능을 최적화

• 설계단계
  
  

  • 요구사항 분석
    • App 요구사항 파악
    • DB 가 해결해야할 문제 이해
      
      
  • 개념적 설계
    • DB 의 고수준 구조 정의
    • ER 다이어그램을 사용하여 데이터와 그 사이 관계 모델링
      
      
  • 논리적 설계
    • 개념적 모델을 DBMS에서 사용할 수 있는 논리적 구조로 변환
    • 테이블, 열, 기본키, 외래키 등 정의
      
      
  • 물리적 설계
    • 실제 시스템에서 DB가 어떻게 저장될지 결정
    • 인덱싱, 파티셔닝, 성능 최적화 방법 고려
      
      
  • 구현
    • 물리적 설계를 바탕으로 DB 생성
    • SQL 을 사용하여 테이블을 만들기
    • 데이터 삽입
      
      
  • 유지보수 및 최적화
    • App 의 요구사항이 변경
    • 성능 문제가 발생할 경우 DB 수정하고 최적화

• EX (온라인 쇼핑몰)
  
  

  • 사용자 / 상품 / 주문
    
    
  • 사용자 (User) 테이블
    • UserID (기본키)
    • Username
    • Email
    • Password
      
      
  • 상품 (Product) 테이블
    • ProductID (기본키)
    • ProductName
    • Price
    • Stock
      
      
  • 주문 (Order) 테이블
    • OrderID (기본키)
    • UserID (외래키, Order 테이블 참조)
    • ProductID (외래키, Product 테이블 참조)
    • Quantity
    • Price

• DDL (데이터 정의 SQL)

CREATE TABLE User (
  UserID INT AUTO_INCREMENT PRIMARY KEY,
  Username VARCHAR(50),
  Email VARCHAR(50),
  Password VARCHAR(50)
);

CREATE TABLE Product (
  ProductID INT AUTO_INCREMENT PRIMARY KEY,
  ProductName VARCHAR(100),
  Price DECIMAL(10, 2),
  Stock INT
);

CREATE TABLE Order (
  OrderID INT AUTO_INCREMENT PRIMARY KEY,
  UserID INT,
  OrderDate DATE,
  TotalPrice DECIMAL(10, 2),
  FOREIGN KEY (UserID) REFERENCES User(UserID)
);

CREATE TABLE OrderDetail (
  OrderDetailID INT AUTO_INCREMENT PRIMARY KEY,
  OrderID INT,
  ProductID INT,
  Quantity INT,
  Price DECIMAL(10, 2),
  FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

• DML (데이터 조작 SQL)

-- @@@ Insert (삽입)

-- Inserting Users
INSERT INTO Users (Username, Email, Password) VALUES
('JohnDoe', 'johndoe@example.com', 'password123'),
('JaneDoe', 'janedoe@example.com', 'password456');

-- Inserting Products
INSERT INTO Products (ProductName, Price, Stock) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15);

-- Inserting Orders
INSERT INTO Orders (UserID, OrderDate, TotalPrice) VALUES
(1, '2023-03-15', 2000.00);

-- Inserting OrderDetails
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES
(1, 1, 1, 1200.00),
(1, 2, 1, 800.00);



-- @@@ Retrieve (검색)

-- Retrieving all orders made by a specific user
SELECT o.OrderID, o.OrderDate, o.TotalPrice, u.Username
FROM Orders o
JOIN Users u ON o.UserID = u.UserID
WHERE u.Username = 'JohnDoe';

-- Retrieving all products purchased in a specific order
SELECT p.ProductName, od.Quantity, od.Price
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
WHERE od.OrderID = 1;



-- @@@ UPDATE (수정)

UPDATE Users
SET Email = 'newemail@example.com'
WHERE UserID = 1;



-- @@@ DELETE (삭제)

DELETE FROM Users
WHERE UserID = 1;



-- @@@ MERGE (병합)

MERGE INTO OrderDetails AS target
USING (SELECT ? AS OrderID, ? AS ProductID, ? AS Quantity, ? AS Price) AS source
ON (target.OrderID = source.OrderID AND target.ProductID = source.ProductID)
WHEN MATCHED THEN 
    UPDATE SET 
        target.Quantity = source.Quantity,
        target.Price = source.Price
WHEN NOT MATCHED THEN 
    INSERT (OrderID, ProductID, Quantity, Price)
    VALUES (source.OrderID, source.ProductID, source.Quantity, source.Price);



-- @@@ MERGE 와 유사하게 INSERT 사용 (병합)

INSERT INTO Products (ProductName, Price, Stock)
VALUES ('New Smartphone', 999.99, 10)
ON DUPLICATE KEY UPDATE 
    Price = VALUES(Price),
    Stock = VALUES(Stock);