【学習記録】DB設計など

今日はアプリのDB設計を進めました。実務ではDB設計をゼロから担当したことはないのですが、こういうタスクも全部自分でできるのが個人開発の良いところですね。
自分で機会作っていって幅広くスキル伸ばしていきたいと思います。

作ろうとしているアプリケーションはインスタグラムのような画像共有アプリ。
ちょっとだけ差別化を狙い、景色の画像に特化したアプリにしようと計画中。

まだシンプルなところしかDB設計できていないですが、だいたい以下の感じで作ろうと考えています。
ER図はまだ作れておらず、Docker環境立ち上げ時に呼び出すSQL文のみ用意できています。
後々ER図を作る練習もしていきます。

usersテーブル

CREATE TABLE `users` (
  user_id INT NOT NULL AUTO_INCREMENT,
  user_name varchar(50) NOT NULL,
  user_password text,
  email_address varchar(200),
  note text,
  icon_image varchar(200),
  PRIMARY KEY(user_id)
);

ユーザー情報を持つテーブル。 ユーザー新規登録時にuser_name、user_password、email_address、note(自己紹介みたいなやつ)、icon_image(顔写真)を入力してユーザー登録する想定。
user_idはAUTO_INCREMENTなので入力しなくても連番で登録される。
どの項目を必須にするかは考え中。icon_imageは必須項目にしないでおいて、ユーザーにicon_imageが設定されていない場合はデフォルトで使うシルエットみたいなやつが表示される、というパターンがよく見かけるので似たような作りにしていきたいと思ってます。

桁数は特に明確な根拠はなく、ある程度余裕を持たせて設定しています。

picturesテーブル

CREATE TABLE `pictures` (
  picture_id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  image_url varchar(200),
  image_note varchar(250),
  prefecture_cd varchar(2),
  view_category_cd varchar(2),
  like_count int,
  published_at timestamp,
  PRIMARY KEY (picture_id)
);

投稿された画像の情報を持つテーブル。

画像投稿画面では画像を選択、image_note(説明文)を入力、prefecture_cd(どこの県の画像か)はプルダウンリストから選択、view_category_cd(空、海、森など)はプルダウンリストから選択してAPIに送信する想定。

APIからS3に画像を格納し、S3の画像URLをDBのimage_urlに登録する。(このあたりまだベストプラクティスはわからないですが、とりあえず動いているのでこの設計で進める )

like_countはいいねされた数、published_atは投稿された日付を登録し、画面で人気順、新しい順で画像一覧を表示する時にソートするKeyとして使う想定。

以下はカテゴリー分け用のテーブル。

CREATE TABLE `view_categories` (
  view_category_cd varchar(3),
  view_name varchar(20),
  sort_no int,
  PRIMARY KEY (view_category_cd)
);

view_category_cdは一意の値を登録し、view_nameに空とか山とか海を登録する想定。

CREATE TABLE `prefecture_categories` (
  prefecture_category_cd varchar(2),
  prefecture_name varchar(4),
  sort_no int,
  PRIMARY KEY (prefecture_category_cd)
);

prefecture_category_cdは一意の値を登録し、prefecture_nameに北海道とか東京とか沖縄とかを登録する想定。