부모 - 자식 관계 테이블 설계하기(MySQL)

MySQL을 사용하여 부모와 자식간의 관계를 효과적으로 저장하는 테이블을 설계하는 방법을 설명하겠다.

 

VMware의 host와 vm간의 관계를 예를 들어 테이블 설계

• VMware 환경에서는 host(부모)와 VM(자식)이 계층적인 관계를 가진다

 

1. 부모 - 자식 관계 테이블 설계

 

1) 부모테이블 (hosts)

• 각 Host(부모)는 여러개의 VM을 가질 수 있음
• Primary Key(id)를 기준으로 자식 테이블(VM) 과 연결된

CREATE TABLE hosts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hostname VARCHAR(255) NOT NULL UNIQUE,
    ip VARCHAR(45) NOT NULL UNIQUE,
    cpu_cores INT NOT NULL CHECK (cpu_cores > 0)
);

 

2) 자식테이블 (vms)

• 각 VM(자식)은 하나의 Host(부모)에 속함
• host_id(부모의 id)를 참조하는 Foreign Key가 존재

CREATE TABLE vms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vmname VARCHAR(255) NOT NULL,
    ip VARCHAR(45) NOT NULL UNIQUE,
    memory INT NOT NULL CHECK (memory > 0),
    host_id INT NOT NULL,
    FOREIGN KEY (host_id) REFERENCES hosts(id) ON DELETE CASCADE
);

 

★ 설계 원칙

• hosts 테이블이 부모이며, 각 Host는 여러 VM을 가질 수 있음(1:N 관계)
• vms 테이블이 자식이며, 각 VM은 하나의 Host에만 속함
• ON DELETE CASCADE로 부모가 삭제되면 자식도 자동 삭제됨

---

2. 데이터 삽입 (부모 → 자식 순서)

1) 부모(Host) 데이터 삽입

INSERT INTO hosts (hostname, ip, cpu_cores) VALUES 
('host1', '192.168.1.10', 8),
('host2', '192.168.1.20', 16);

 

2) 자식(VM) 데이터 삽입

INSERT INTO vms (vmname, ip, memory, host_id) VALUES 
('vm1', '192.168.1.101', 4096, 1),
('vm2', '192.168.1.102', 8192, 1),
('vm3', '192.168.1.201', 16384, 2);

---

3. 부모 - 자식 관계 조회

부모(Host)와 자식(VM) 간의 관계를 쉽게 조회하려면 JOIN 쿼리를 사용한다

SELECT h.id AS host_id, h.hostname, h.ip AS host_ip, h.cpu_cores, 
       v.id AS vm_id, v.vmname, v.ip AS vm_ip, v.memory 
FROM hosts h
LEFT JOIN vms v ON h.id = v.host_id
ORDER BY h.id, v.id;

 

결과 예시

host_id	hostname	host_ip	cpu_cores	vm_id	vmname	vm_ip	memory
1	host1	192.168.1.10	8	1	vm1	192.168.1.101	4096
1	host1	192.168.1.10	8	2	vm1	192.168.1.101	8192
2	host2	192.168.1.20	16	3	vm2	192.168.1.201	16384

---

4. 부모(Host) 삭제 시 자식(VM) 자동 삭제

부모(Host)가 삭제될 경우, 자식(VM)도 함께 삭제된다

DELETE FROM hosts WHERE id = 1;
-- host1이 삭제되면서 vm1, vm2도 삭제됨

삭제 후 다시 조회하면 다음과 같이 변경 된다

host_id	hostname	host_ip	cpu_cores	vm_id	vmname	vm_ip	memory
2	host2	192.168.1.20	16	3	vm2	192.168.1.201	16384

---

마무리

부모 자식간의 테이블 설계하는 방법에는 여러 방법이 존재한다. One 테이블로도 설계가 가능하고, 테이블을 나누어서도 관리가 가능하다.

이렇게 설계하는게 정답은 아니지만, 기본적으로 많이 하는 방식이기 때문에 정리 해 보았다.