2019/11/18 - 深酒避ける為の技術ブログ

三日坊主にならずに帰ってくることができた。

では、今日も作業を始めていく。

先週の木曜日はベッロベロになりながら、適当なテーブルスキーマを書いた。今日はその適当なスキーマに肉付けと説明を書き残す作業から始めることとする。

まずユーザ情報を管理するための Users テーブルから始める。

CREATE TABLE IF NOT EXISTS `users` (
  `id` CHAR(36) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(255) NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `salt` VARCHAR(45) NOT NULL,
  `is_admin` TINYINT(1) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB

id フィールドにはUUIDまたはULIDを用いる。 UUIDとその互換のULIDはピッタリ36文字と決められているため、型は CHAR(36) とした。 (参考：primary key - UUID max character length - Stack Overflow )

email フィールドの長さに関しては色々な議論があるだろうが、まあ個人で適当に作るサービスなのでそこまで厳密には考えず適当に VARCHAR(255) とした。ちなみに VARCHAR の長さを256ではなく255としているのをよく見かけるが、これは文字数カウントのための領域が255だと1バイト、256だと2バイト必要なため、どっちでも良い場合は255を使うのがお作法ということらしい。

次にルーティンワークを管理するための Routines テーブルを示す。

CREATE TABLE IF NOT EXISTS `routines` (
  `id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `status` VARCHAR(16) NOT NULL,
  `start_time` TIME NULL,
  `finish_time` TIME NULL,
  `color` VARCHAR(7) NOT NULL,
  `days` TINYINT NOT NULL,
  `is_allday` TINYINT(1) NOT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

id フィールドはユーザ情報と同じくUUIDかULIDを用いる。また、user_id は外部キーで users テーブルを参照し、複数のユーザで同じルーティンワークを共有することはできないこととする。すなわちユーザとルーティンワークは一対多の関係となる。

description にはそのルーティンワークの詳細な説明を入れる。この説明文は長くなる可能性が高いので型は TEXT 型とした。

status には、そのルーティンワークがまだActiveかどうかを示す文字列を格納する。 TINYINT(1) にしなかったのは、将来的にActive/Inactive以外にも状態を持たせる可能性があると判断したからである。ただしこのフィールドは後述する routine_history テーブルの存在により必要なくなるかもしれない。

start_time と finish_time は、一日の中でどの時間帯にそのルーティンワークを行うかを示す。終日行ったり、start_time だけ決めて finish_time は決めない、といった状況にも対応するためにNOT NULL制約はかけていない。

color はルーティンワークに色で簡単にタグ付けできるようにするための機能である。もちろん文字列によるタグ付けも実装は簡単だが、少しそれはやり過ぎな気がしたので、このようなシンプルなデザインにした。型は VARCHAR(7) としたが、これは #FFFFFF のような文字列を直接格納することを想定したからである。しかしよく考えるとアンチパターンな気もするのでこれは変えたほうが良いかもしれない。

days は何曜日にそのルーティンワークを実行するかを管理する1バイトの整数値である。整数値である理由は何となく予想がつくと思うが、以下のようにビットで管理したいからである。ただこの方針はアプリケーションの実装にテーブルが密に依存してしまうのでバッドプラクティスなのかもしれない…。

(High) 0	1	1	1	1	1	1	1 (Low)
X	Sat	Fri	Thu	Wed	Tue	Mon	Sun

is_allday はそのルーティンが終日行われるルーティンか否かを示すフラグである。

時間が時間なので、今日はここまでとする。今日も色々調べ物がメインだったため全然作業が進まなかった。ただSQL アンチパターンをまとめた記事をざっと目を通せたのはとても良かった。

では、おやすみなさいませ。