[가계부 시스템] 2. 시스템 설계 - 아키텍쳐, DB

 

2021.07.15 - [Project] - [가계부 시스템] 1. 요구사항 정리

[가계부 시스템] 1. 요구사항 정리

이전글에 작성한 요구사항을 보면, "여러 기기에서 데이터가 동기화되며 동시에 사용할 수 있을 것" 이라는 요구가 있다. 이는 서버를 두고, 서버에 데이터를 저장해야함을 의미한다. 각 클라이언트에 데이터를 저장하고 실시간으로 데이터를 주고받는것은 구현의 어려움도 있고, 각 기기가 항시 인터넷에 연결되어있지 않으면 데이터가 마구잡이로 섞일 수 있기 때문이다. 어쨌든, 서버는 이기회에 Spring을 이용해보기로 했다.

 

결국, 전체 시스템구조는 간단히 DB - 서버 - 클라이언트 가 되었다. 이제 DB 설계를 할 차례다. 

DB를 어떻게 설계해야하는지에 대한 힌트가 있는 요구사항들을 다시 보자.

1. 여러 자산(각 은행별 계좌, 현금 등)을 등록해 관리할 수 있을것.
2. 전체 자산의 총합과 수입과 지출을 월별로 관리할 것.
3. 가계부에 적을 정보는 날짜및 시간, 자산, 거래의 분류, 금액, 내용 이다.
4. 거래의 분류는 수입, 지출마다 따로 정해져있다.
5. 각 자산간 이동(현금을 계좌에 입금하는 등)을 따로 관리할 수 있다. 이는 수입과 지출에 포함되지 않는다.

우선, 직관적으로, 각 월별 가계부를 표현할 "가계부" 테이블이 필요함을 알 수 있다. "자산" 테이블도 필요하다. 또한, "가계부"에는 각각의 거래가 기록되어있으므로 "거래" 테이블이 필요하다. "거래" 테이블에는 날짜, 자산, 분류, 금액, 내용이 필요하다. 그리고 "가계부"와 "거래"는 1:n 관계이므로 "거래" 테이블에는 "가계부"의 주키를 왜래키로 참조해야 한다. 그리고, 각 거래의 분류를 위해 "분류" 테이블이 필요하고, 이 테이블에는 수입인지 지출인지 표기할 항목도 필요하다. 또한, 나의 가계부를 아무나 볼 수 있어선 안되니 인증을 위한 테이블도 필요할 것이다. 그 외 여러 알 수 있는 정보들로 DB를 구축했다.

CREATE TABLE `account` (
  `aid` bigint(20) NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL DEFAULT current_timestamp(),
  `accountType` varchar(50) NOT NULL,
  `value` bigint(20) NOT NULL,
  `assetName` varchar(50) NOT NULL,
  `category` varchar(50) NOT NULL,
  `memo` varchar(50) NOT NULL DEFAULT '""',
  `yearMonth` varchar(7) NOT NULL,
  PRIMARY KEY (`aid`),
  KEY `FK_account_category` (`category`),
  KEY `FK_account_asset` (`assetName`),
  KEY `FK_account_book` (`yearMonth`),
  CONSTRAINT `FK_account_asset` FOREIGN KEY (`assetName`) REFERENCES `asset` (`assetName`),
  CONSTRAINT `FK_account_book` FOREIGN KEY (`yearMonth`) REFERENCES `book` (`yearMonth`),
  CONSTRAINT `FK_account_category` FOREIGN KEY (`category`) REFERENCES `category` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4

CREATE TABLE `asset` (
  `assetName` varchar(50) NOT NULL,
  `value` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`assetName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `auth` (
  `token` varchar(256) NOT NULL,
  PRIMARY KEY (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `book` (
  `yearMonth` varchar(7) NOT NULL,
  `totalIncome` bigint(20) NOT NULL DEFAULT 0,
  `totalExpenditure` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`yearMonth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `category` (
  `name` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `conversionaccount` (
  `aid` bigint(20) NOT NULL AUTO_INCREMENT,
  `yearMonth` varchar(7) NOT NULL,
  `value` bigint(20) NOT NULL,
  `time` datetime NOT NULL DEFAULT current_timestamp(),
  `from` varchar(50) NOT NULL,
  `to` varchar(50) NOT NULL,
  `memo` varchar(50) NOT NULL,
  PRIMARY KEY (`aid`),
  KEY `FK_conversionaccount_asset` (`from`),
  KEY `FK_conversionaccount_asset_2` (`to`),
  KEY `FK_conversionaccount_book` (`yearMonth`),
  CONSTRAINT `FK_conversionaccount_asset` FOREIGN KEY (`from`) REFERENCES `asset` (`assetName`),
  CONSTRAINT `FK_conversionaccount_asset_2` FOREIGN KEY (`to`) REFERENCES `asset` (`assetName`),
  CONSTRAINT `FK_conversionaccount_book` FOREIGN KEY (`yearMonth`) REFERENCES `book` (`yearMonth`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4