위와 같은 테이블 설계를 시작으로 연관된 테이블의 정보를 출력을 진행하였다.
CASE, END 조건을 통해 맞는 테이블을 설정을 진행하는 쿼리를 작성한 결과.....
전체 조회 query.
SELECT user_review.*, user.email,
CASE
WHEN user_review.table_name = 'diner' THEN diner.name
WHEN user_review.table_name = 'cafe' THEN cafe.name
WHEN user_review.table_name = 'hall' THEN hall.name
WHEN user_review.table_name = 'gym' THEN gym.name
WHEN user_review.table_name = 'library' THEN library.name
END AS name
FROM user_review
INNER JOIN user ON user_review.user_id = user.id
LEFT JOIN diner ON user_review.table_id = diner.id AND user_review.table_name = 'diner'
LEFT JOIN cafe ON user_review.table_id = cafe.id AND user_review.table_name = 'cafe'
LEFT JOIN hall ON user_review.table_id = hall.id AND user_review.table_name = 'hall'
LEFT JOIN gym ON user_review.table_id = gym.id AND user_review.table_name = 'gym'
LEFT JOIN library ON user_review.table_id = library.id AND user_review.table_name = 'library'
ORDER BY reg_date DESC테이블 구성의 가시성을 위해 table_id를 여러 테이블 ID를 하나로 관리 후 table_name을 통해 분류를 하려고 하였지만 전체 조회하나만으로도 작성되어야 하는 테이블이 많고 이후 table이 추가할 시 내용의 추가가 많이 이뤄진다.
좋지 않은 이유
table_id를 하나의 컬럼으로 관리하고 table_name을 통해 어떤 테이블인지 구분하는 방식은 일반적으로는 좋은 설계 방법이 아닙니다.
-
데이터 무결성 규칙 위배
만약 특정 테이블의 id가 삭제된다면, 이 테이블을 참조하는 모든 행을 업데이트 해야 합니다. 또한 table_name이 잘못된 값을 가지고 있다면, 참조되는 테이블의 정보를 찾을 수 없어 데이터 무결성 규칙이 위배. -
성능 문제 발생 가능성
쿼리를 실행할 때, table_name을 사용하여 특정 테이블에 대한 정보를 가져와야 하므로, 인덱스를 사용할 수 없음! 이렇게 되면 성능 문제가 발생할 가능성. -
유연성 제한
테이블이 추가될 때마다, 새로운 테이블에 대한 정보를 추가해야 합니다. 이렇게 되면, 유연성이 제한되며, 유지 보수 비용이 증가할 가능성이 큼.
-> USER_REVIEW 테이블에서 각각의 상품을 참조하는 별도의 컬럼을 가지는 것이 좋습니다. 이렇게 되면, 데이터 무결성이 보장되며, 인덱스를 사용하여 쿼리의 성능을 개선할 수 있습니다
개선작업.
전체 조회 query.
SELECT ur.id, ur.user_id, ur.subject, ur.review, ur.stars, ur.reg_date, ur.mod_date,
COALESCE(c.NAME, d.NAME, l.NAME, h.NAME, g.NAME) as NAME
FROM USER_REVIEW ur
LEFT JOIN CAFE c ON ur.cafe_id = c.id
LEFT JOIN DINER d ON ur.diner_id = d.id
LEFT JOIN LIBRARY l ON ur.library_id = l.id
LEFT JOIN HALL h ON ur.hall_id = h.id
LEFT JOIN GYM g ON ur.gym_id = g.id;게시판 별 조회 query.
/** pk 외래키 사용 이전 */
String sql = "SELECT * FROM user_review WHERE table_id = ? AND table_name = ? " +
" ORDER BY reg_date DESC LIMIT 0, 5";
* */
/** pk 외래키 사용 후 */
String sql = "";
switch (tableName){
case "diner":
sql = "SELECT * FROM user_review WHERE diner_id = ? ORDER BY reg_date DESC LIMIT 0, 5";
break;
case "hall":
sql = "SELECT * FROM user_review WHERE hall_id = ? ORDER BY reg_date DESC LIMIT 0, 5";
break;
case "gym":
sql = "SELECT * FROM user_review WHERE gym_id = ? ORDER BY reg_date DESC LIMIT 0, 5";
break;
case "library":
sql = "SELECT * FROM user_review WHERE library_id = ? ORDER BY reg_date DESC LIMIT 0, 5";
break;
case "cafe":
sql = "SELECT * FROM user_review WHERE cafe_id = ? ORDER BY reg_date DESC LIMIT 0, 5";
break;
}db 변경후 sql 조회 및 입력 수정을 진행하며 조건에 따른 sql 문을 분기 처리.. sql로직이 길어졌지만!!
🎇 데이터 베이스 정규화 및 성능을 고려한다면 pk를 도입하는 것이 맞다.
