안녕하세요.

오늘은 데이터베이스 모델링에 대해 조언을 구하고자 글을 쓰게 되었습니다.

아직 내공이 부족해 도움은 못드리고 도움만 받고 있는 상황인지라 많이 부끄럽습니다.

저도 내공이 많이 쌓여 많은 지식을 함께 나눴으면 좋겠습니다.

 

1. 목표

관리자 페이지에서 관리자는 회원 그룹 (발송대상) 별로 메시지를 전달하고, 사용자들은 전달받은 메시지와 관련된 정보를 집계하여

확인하는 기능을 구현

 

2. 배경

회원그룹(발송대상)은 4가지로서, 1-전체회원, 2-지점장회원, 3,-지점장을 제외한 일반사원회원, 4-선택회원

네가지의 경우의 수로 메시지를 전달하며, 회원들은 인트라넷 접속시 본인앞으로 전달된 메시지의 수를 카운트하여 

대시보드 및 메시지함에서 열람이 가능함.

 

3. 구현

A - 메시지 테이블 (테이블명 detail)

id : PK

title : 메시지 제목

contents : 메시지 내용

----------------------------------------------------------------------------------------------------
|   id   |                       title                |                contents                    |
---------------------------------------------------------------------------------------------------|
|   1    |    이번주도 힘내서 정진합시다.             |       안녕하세요 이번주는 5월의셋..        |
|--------------------------------------------------------------------------------------------------|
|   2    |    지점장님들께 공지사항                   |       5월 가정의 달을 맞이하여 우..        |
|--------------------------------------------------------------------------------------------------|
|   3    |    회원님들 가정에 항상 건강               |       회원님들의 가정에 항상 건강이        |
|--------------------------------------------------------------------------------------------------|
|   4    |   5월 이벤트 당첨이 되셨습니다.            |       고객센터로 문의주시면 상품수...      |
|--------------------------------------------------------------------------------------------------|

 

B - 메시지 대상자 테이블 (테이블명 message)

id : PK

sender_id : 발신자 아이디

receiver_id : 수신자 아이디

detail_id : 메시지 제목과 내용 테이블의 PK

type : 메시지 종류 (0 - 전체회원, 1-지점장회원, 2-지점장제외한 일반회원, 3->선택회원)

----------------------------------------------------------------------------------------------------
|   id   |        sender_id        |      receiver_id       |    detail_id     |       type        |
---------------------------------------------------------------------------------------------------|
|   1    |          admin          |         null           |        1         |         0         |
|--------------------------------------------------------------------------------------------------|
|   2    |          admin          |          kim           |        2         |         1         |
|--------------------------------------------------------------------------------------------------|
|   3    |          admin          |          lee           |        2         |         1         | 
|--------------------------------------------------------------------------------------------------|
|   4    |          admin          |          kang          |        2         |         1         |
|--------------------------------------------------------------------------------------------------|  
|   5    |          admin          |         hwang          |        2         |         1         |
|--------------------------------------------------------------------------------------------------| 
|   6    |          admin          |          kim           |        3         |         2         | 
|--------------------------------------------------------------------------------------------------| 
|   7    |          admin          |          lee           |        3         |         2         | 
|--------------------------------------------------------------------------------------------------| 
|   8    |          admin          |          kang          |        3         |         2         | 
|--------------------------------------------------------------------------------------------------| 
|   9    |          admin          |          hwang         |        3         |         2         | 
|--------------------------------------------------------------------------------------------------| 
|   10   |          admin          |          lee           |        4         |         3         |
|--------------------------------------------------------------------------------------------------|
|   11   |          admin          |          kang          |        4         |         3         |
|--------------------------------------------------------------------------------------------------|
|   12   |          admin          |          hwang         |        4         |         3         |
|--------------------------------------------------------------------------------------------------|

C - 메시지 확인 유무 테이블 (테이블명 read_user)

id: PK

message_id : message 테이블의 PK

user_id : 유저 ID

---------------------------------------------------------
|  id   |        message_id        |      user_id       |
--------------------------------------------------------|
|   1   |            1             |         kim        |
|-------------------------------------------------------|
|   2   |            1             |         lee        |
|-------------------------------------------------------|
|   3   |            1             |        hwang       |
|-------------------------------------------------------|
|   4   |            3             |         jang       |
|-------------------------------------------------------|  

 

모델 설계시 고려한 부분은 다음과 같습니다.

1. 지점장의 경우 일반회원의 수보다 많을 수 없다

 

2. 회원수의 경우 시간이 지날수록 기하급수적으로 늘어날것으로 간주하고

발송하는 메시지의 경우 각 그룹에 전달하는 메시지의 성격을 띄므로 최대한 데이터베이스에 중복 인서트를 줄이는 방향으로 고려한다.

 

3. 따라서 고려한 부분은 메시지 제목과 내용의 경우 별도 테이블에 저장 (detail 테이블) 하고 message 테이블에 수신대상자를 정의하여

실제로 읽은 인원과 읽지 않은 인원을 구해야하는 경우가 발생하므로 별도의 read_user 테이블에 읽은인원에 대해 message의 문서 id값과 유저 id 값을 

동시 저장해 구현한다.

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

다음과 같은 논리로 구현하였습니다.

 

전체 메시지 발송의 경우

발송 당시의 TIMESTAMP 값을 기준으로 유저가 로그인시 해당 유저의 회원가입일을 대조해 TIMESTAMP 이전 인원의 경우 메시지를 보이도록 구현하고

해당 메시지를 읽은 대상자의 경우 read_user 테이블에 값을 남겨 읽은 회원으로 남김

 

지점장 회원 발송과 일반회원 발송의 경우

메시지 발송 당시의 지점장 회원 아이디를 receiver_id 에 기록하고 유저가 로그인시 해당 유저의 회원가입일을 TIMESTAMP과 대조해 이전 인원중에서

지점장 회원만 보이도록 또는 반대로 지점장 회원을 제외한 회원만 메시지를 보이도록 구현하고 

해당 메시지를 읽은 대상자의 경우 read_user 테이블에 값을 남겨 읽은 회원으로 남김

 

선택회원 메시지 발송의 경우

발송 당시의 선택한 회원 아이디를 receiver_id 에 기록하고 유저가 로그인시 메시지를 보이도록 구현하고 해당 메시지를 읽은 대상자의 경우 read_user

테이블에 값을 남겨 읽은 회원으로 남김

 

4. 문제점

전체회원과 선택회원의 경우 지점장과 일반회원 간 등급 구분이 없어 문제가 없을것으로 생각되지만

지점장과 일반회원의 경우 메시지를 발송하고 나서 대상이 되는 일반회원또는 지점장 회원이 메시지를 확인하기

이전 조직도의 변동이 있을경우 문제가 있을것으로 예상이 되어 조언을 구하게 되었습니다. 

글을 쓰다가 생각해보니 메시지 작성이후 회원의 회원 등급이 바뀜으로서 현재 등급과 맞지 않게 보이는 메시지는

게시판이 아닌 메시지라는 원래의 기능에 합당하게 보이기 때문에 문제가 없을것 같더라구요..

결국 조언을 구하고자 내용을 쓰다가 우려하던 문제점이 해결이 된 것 같습니다 ㅡㅡ;

 

5. 결론

혹시 이와 같은 모델을 구성할때 더 나은 방법으로 구성하는 방법이 있을까요?

한편으로는 모델링 책으로 공부를 하고 실무에 적용하려고 하니 각 테이블은 테이블당 하나의 기능을 담당해야 한다고 하는

글을 따라 구성하게 되었고 프로젝트를 진행하면서 드는 생각이 왠지 테이블이 엄청나게 늘어나는 느낌입니다.

 

(따라서 쿼리도 더욱 복잡해집니다. 저걸 단순히 하나의 테이블에 넣으면 엄청나게 쉬워지는 쿼리가 다음과 같은 값을 구하려면

"""

예)일반유저가 로그인했을당시,

해당 일반회원 기준 전체회원대상 메시지 총 10건중 8건을 읽지 않았고,

일반유저 대상으로 발송된 메시지가 5건중 3건을 읽지 않았으며,

선택회원으로 발송된 메시지는 5건중 5건다 읽었음

총 수신한 메시지는 20건이며 읽은메시지는 9건, 읽지 않은 메시지는 11건임

"""

위와 같은 결과를 단일 쿼리로만 구해보려면 조인은 기본이고 서브쿼리도 여러개 구성이됩니다.)

 

제가 본 책에서는 저런식의 구성의 경우 조인이나 서브쿼리를 실행하므로서 소요되는 비용이 적지는 않지만,

중장기적으로 보면 관리측면에서나 효율적인면에서나 크게 다르지 않거나 적다고 한다고 합니다.

물론 책으로만 보고 실제 겪어보진 않았기 때문에 정말 그런것인지 이런 부분이 궁금하긴 합니다.

지금과 같이 모델링을 할때 보다 나은 방법으로 모델링을 할 수 있는 방법이 있을까요?

 

저보다 많은 경험을 겪어보신 분들의 고견을 바랍니다.