Ch07. 동적 설계 EAV - EAV 패턴 실습

기본 테이블 구조 설계

DROP TABLE IF EXISTS product_attribute;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
-- 카테고리 테이블
CREATE TABLE category (
	category_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	parent_id BIGINT,
	FOREIGN KEY (parent_id) REFERENCES category(category_id)
);

-- 상품 테이블 (Entity)
CREATE TABLE product (
	product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	category_id BIGINT NOT NULL,
	name VARCHAR(200) NOT NULL,
	price INT NOT NULL,
	status VARCHAR(20) DEFAULT 'ACTIVE',
	created_at DATETIME NOT NULL,
	FOREIGN KEY (category_id) REFERENCES category(category_id)
);

-- 상품 속성 테이블 (Attribute-Value)
CREATE TABLE product_attribute (
	attribute_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	product_id BIGINT NOT NULL,
	attr_name VARCHAR(100) NOT NULL,
	attr_value VARCHAR(500),
	created_at DATETIME NOT NULL,
	FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- 인덱스 추가
CREATE INDEX idx_product_attribute_product ON product_attribute(product_id);
CREATE INDEX idx_product_attribute_name ON product_attribute(attr_name);

• 이 구조에서 product 테이블은 모든 상품이 공통으로 가지는 핵심 속성만 포함한다.
• 카테고리별로 다른 속성들은 product_attribute 테이블에 저장한다.

테스트 데이터 입력

-- 카테고리 입력
INSERT INTO category (category_id, name, parent_id) VALUES
(1,'전자제품', NULL),
(2,'스마트폰', 1),
(3,'노트북', 1),
(4,'의류', NULL),
(5,'상의', 4),
(6,'식품', NULL);

-- 상품 입력
INSERT INTO product (product_id, category_id, name, price, created_at) VALUES
(1, 2, 'Galaxy S24 Ultra', 1650000, NOW()),
(2, 2, 'iPhone 15 Pro', 1550000, NOW()),
(3, 3, 'MacBook Pro 14', 2690000, NOW()),
(4, 5,'캐시미어 니트', 89000, NOW());

-- 스마트폰 속성 (Galaxy S24 Ultra)
INSERT INTO product_attribute (product_id, attr_name, attr_value, created_at)
VALUES
(1, 'screen_size', '6.8인치', NOW()),
(1, 'battery_capacity', '5000mAh', NOW()),
(1, 'ram', '12GB', NOW()),
(1, 'storage', '256GB', NOW()),
(1, 'main_camera', '200MP', NOW()),
(1, 'processor', 'Snapdragon 8 Gen 3', NOW()),
(1, 's_pen', 'Y', NOW()),
(1, 'color','티타늄 바이올렛', NOW());


-- 스마트폰 속성 (iPhone 15 Pro)
INSERT INTO product_attribute (product_id, attr_name, attr_value, created_at)
VALUES
(2, 'screen_size', '6.1인치', NOW()),
(2, 'battery_capacity', '3274mAh', NOW()),
(2, 'ram', '8GB', NOW()),
(2, 'storage', '256GB', NOW()),
(2, 'main_camera', '48MP', NOW()),
(2, 'processor', 'A17 Pro', NOW()),
(2, 'action_button', 'Y', NOW()),
(2, 'color','블루 티타늄', NOW());

-- 노트북 속성 (MacBook Pro 14)
INSERT INTO product_attribute (product_id, attr_name, attr_value, created_at) VALUES
(3, 'screen_size', '14.2인치', NOW()),
(3, 'processor', 'M3 Pro', NOW()),
(3, 'ram', '18GB', NOW()),
(3, 'storage', '512GB SSD', NOW()),
(3, 'battery_life', '17시간', NOW()),
(3, 'weight', '1.61kg', NOW()),
(3, 'ports', 'HDMI, SD카드, MagSafe, Thunderbolt x3', NOW());

-- 의류 속성 (캐시미어 니트)
INSERT INTO product_attribute (product_id, attr_name, attr_value, created_at) VALUES
(4, 'material','캐시미어 100%', NOW()),
(4, 'size', 'S, M, L, XL', NOW()),
(4, 'color', '아이보리, 네이비, 그레이', NOW()),
(4, 'origin', '이탈리아', NOW()),
(4, 'washing','드라이클리닝', NOW()),
(4, 'season', 'F/W', NOW());

 

상품 속성 조회하기

 

특정 상품의 모든 속성 조회

SELECT
	p.product_id,
	p.name AS product_name,
	p.price,
	pa.attr_name,
	pa.attr_value
FROM product p
JOIN product_attribute pa ON p.product_id = pa.product_id
WHERE p.product_id = 1
ORDER BY pa.attr_name;

쿼리 결과

• product와 product_attribute를 조인해서 상품과 속성을 함께 구할 수 있다.

특정 속성으로 상품 검색

SELECT
	p.product_id,
	p.name,
	p.price,
	pa.attr_value AS ram
FROM product p
JOIN product_attribute pa ON p.product_id = pa.product_id
WHERE pa.attr_name = 'ram'
AND pa.attr_value = '12GB';

쿼리 결과

• "attr_name", "attr_value" 두 조건을 함께 사용해야 한다.
• 만약 "attr_value"만 사용하면 "ram"이 아닌 다른 속성에 "12GB" 값도 결과에 포함될 수 있다.

 

여러 속성 조건으로 검색

화면 크기가 6인치 이상이면서 S펜을 지원하는 상품을 찾아보자. EAV 구조에서 여러 조건을 AND로 검색하려면 조금 복잡한 쿼리가 필요하다.

SELECT p.product_id, p.name, p.price
FROM product p
WHERE p.product_id IN (
	SELECT product_id
	FROM product_attribute
	WHERE attr_name = 'screen_size'
	AND attr_value LIKE '6%'
)
AND p.product_id IN (
	SELECT product_id
	FROM product_attribute
	WHERE attr_name = 's_pen'
	AND attr_value = 'Y'
);

쿼리 결과

 

행을 열로 변환하기 (Pivot)

EAV 구조의 데이터를 전통적인 테이블 형태로 보고 싶을 때가 있다. 이럴 때는 피벗(Pivot) 기법을 사용한다.

SELECT
	p.product_id,
	p.name,
	p.price,
	MAX(CASE WHEN pa.attr_name = 'screen_size' THEN pa.attr_value END) AS screen_size,
	MAX(CASE WHEN pa.attr_name = 'battery_capacity' THEN pa.attr_value END) AS battery,
	MAX(CASE WHEN pa.attr_name = 'ram' THEN pa.attr_value END) AS ram,
	MAX(CASE WHEN pa.attr_name = 'storage' THEN pa.attr_value END) AS storage
FROM product p
LEFT JOIN product_attribute pa ON p.product_id = pa.product_id
WHERE p.category_id = 2 -- 스마트폰 카테고리
GROUP BY p.product_id, p.name, p.price;

쿼리 결과

• 이 쿼리는 행으로 저장된 속성들을 열로 변환하여 보여준다.
• "CASE"와 "MAX()"를 조합하여 Pivot을 구현한다.

 

속성 동적 추가 실습

EAV의 가장 큰 장점인 동적 속성 추가를 실습해보자.

-- 새로운 속성 추가
INSERT INTO product_attribute (product_id, attr_name, attr_value, created_at) VALUES
(1, 'water_resistance', 'IP68', NOW()),
(1, 'wireless_charging', 'Y', NOW()),
(1, 'reverse_charging', 'Y', NOW()),
(1, '5g_support', 'Y', NOW());

-- 확인
SELECT attr_name, attr_value
FROM product_attribute
WHERE product_id = 1
ORDER BY created_at DESC
LIMIT 5;

쿼리 결과

• "ALTER TABLE" 없이 새로운 속성이 추가되었다. 이것이 EAV의 핵심 장점이다.