Table Definition.

created_at:July 09, 2022

updated_at:July 10, 2022

Table-of-contents

Purposes of this article creation
Table Definition Process
References
Summary/What I learned this time

Today, I am going to explain physical design process.

Purposes of this article creation

To be able to create DB tables in MySQL in Ruby on Rails on my own.
To be able to do physical design.

Table Definition Process

In this case, I will create physical data model for a SNS task management application on Cacoo as a deliverable.

【What is Table Definition?】

Physically optimize the data model to match the DBMS to be used.
Implement a database by setting specific data types, constraints, etc.

※The state in which DB can be implemented by looking at the physical data model.

【Table Definition Process】

Determine Data Types (string, number, half-width alphanumeric characters)
Assigning Constraints

1. Determine Data Types (string, number, half-width alphanumeric characters)

※In this case, “db/schema.rb” in Rails is used as the deliverable.

※The “created_at” and “updated_at” columns, which are automatically created by Rails, are omitted.

※Be aware that using inappropriate data types may consume more disk space than necessary and may affect performance.

※Deliverable is introduced in “Deliverable(db/schema.rb)“.

2. Assigning Constraints

※As in “1. Determining data types (strings, numbers, half-width alphabetic characters),” Rails’ “db/schema.rb” is used as the deliverable here.

※The “created_at” and “updated_at” columns automatically created by Rails are omitted.

【Point】

Add appropriate constraints.

[Types of constraints in Rails when creating tables]

1.NOT NULL Constraint
- Constraint that does NOT allow NULL (empty value) to be entered in the attribute values of a specific table.
2.Uniqueness Constraint
- Constraint that prohibits duplicate data in a table.
- For example, when you do not want users to register with the same email address.
3.Primary Key Constraint
- Constraint that guarantee that the attribute value that is the primary key always exists and is not duplicated.
- It is equivalent to setting both NOT NULL and Uniqueness Constraint on the primary key.
- When creating tables in Rails, primary key constraint are originally implemented (primary keys are automatically created as id columns, and values in id columns are not duplicated).
4.Foreign Key Constraint
- Constraint that the corresponding record of a foreign key must also exist in the record of the parent table.
- Even if a foreign key column has a value, there must be a record in another table that has that value as a primary key.
5.DEFAULT Constraint
- Constraint that have an initial value.

※In case of FK constraint, FK can be specified by Model relationship (belongs_to or has_many).

【Deliverable】
1.[users]

[NOTNULL、More than 4 characters and less than 50 characters(Number of characters that can fit on the screen in an easy-to-read manner.)]    t.string "nickname", limit: 50, null: false
[NOTNULL、Uniqueness]    t.string "email", null: false
[NONE]    t.string "firebase_id"
[NULL OK、Less than 160 characters]    t.string "bio", limit: 160
[NOTNULL、Less than 15 characters、Uniqueness]    t.string "username", limit: 15, null: false
ーーー
t.index ["email"], name: "index_users_on_email", unique: true
t.index ["username"], name: "index_users_on_username", unique: true

2.[tasks]

[NOTNULL、"Untitled" will be automatically added on the backend side.]    t.string "title", null: false
[NULL OK]    t.text "content"
[NOTNULL、FK]    t.bigint "user_id", null: false
[NOTNULL、Automatically add "unsupported (numeral 0)" on front or backend side.]    t.integer "status", null: false
[NOTNULL、Specify date format.、Automatically add "date" on backend side.]    t.string "start_date", null: false
[NOTNULL、Specify date format.、Automatically add "date" on backend side.]    t.string "end_date", null: false
ーーー
t.index ["user_id"], name: "fk_rails_4d2a9e4d7e"

※If neither is set, "today(start_date) & later(end_date)" is used.If only one of the two is set, "the date not set is the previous or later date" is used.

3.[likes]

[NOTNULL、FK]    t.bigint "task_id", null: false
[NOTNULL、FK]    t.bigint "user_id", null: false
ーーー
t.index ["task_id"], name: "fk_rails_5b40312a1e"
t.index ["user_id"], name: "fk_rails_1e09b5dabf"

4.[relationships]

[FK]    t.bigint "following_id"
[FK]    t.bigint "follower_id"
ーーー
t.index ["follower_id"], name: "index_relationships_on_follower_id"
t.index ["following_id"], name: "index_relationships_on_following_id"

5.[notifications]

[FK]    t.bigint "visitor_id"
[FK]    t.bigint "visited_id"
[FK]    t.bigint "task_id"
[NOTNULL、DEFAULT("")]    t.string "action", default: "", null: false
[NOTNULL、DEFAULT(false)]    t.boolean "checked", default: false, null: false
ーーー
t.index ["task_id"], name: "index_notifications_on_task_id"
t.index ["visited_id"], name: "index_notifications_on_visited_id"
t.index ["visitor_id"], name: "index_notifications_on_visitor_id"

Deliverable(db/schema.rb)

ActiveRecord::Schema.define(version: 2022_07_10_114648) do
  create_table "likes", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.bigint "task_id", null: false
    t.bigint "user_id", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["task_id"], name: "fk_rails_5b40312a1e"
    t.index ["user_id"], name: "fk_rails_1e09b5dabf"
  end

  create_table "notifications", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.bigint "task_id"
    t.string "action", default: "", null: false
    t.boolean "checked", default: false, null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.bigint "visitor_id"
    t.bigint "visited_id"
    t.index ["task_id"], name: "index_notifications_on_task_id"
    t.index ["visited_id"], name: "index_notifications_on_visited_id"
    t.index ["visitor_id"], name: "index_notifications_on_visitor_id"
  end

  create_table "relationships", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.bigint "following_id"
    t.bigint "follower_id"
    t.index ["follower_id"], name: "index_relationships_on_follower_id"
    t.index ["following_id"], name: "index_relationships_on_following_id"
  end

  create_table "tasks", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "title", null: false
    t.text "content"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.bigint "user_id", null: false
    t.integer "status", null: false
    t.string "start_date", null: false
    t.string "end_date", null: false
    t.index ["user_id"], name: "fk_rails_4d2a9e4d7e"
  end

  create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "nickname", limit: 50, null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.string "email", null: false
    t.string "firebase_id"
    t.string "bio", limit: 160
    t.string "username", limit: 15, null: false
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["username"], name: "index_users_on_username", unique: true
  end

  add_foreign_key "likes", "tasks"
  add_foreign_key "likes", "users"
  add_foreign_key "notifications", "tasks"
  add_foreign_key "notifications", "users", column: "visited_id"
  add_foreign_key "notifications", "users", column: "visitor_id"
  add_foreign_key "relationships", "users", column: "follower_id"
  add_foreign_key "relationships", "users", column: "following_id"
  add_foreign_key "tasks", "users"
end

References

[Table Definition]

「SQLでテーブル定義を設定・確認・変更する方法などDBMSごとに徹底解説！」：https://style.potepan.com/articles/25439.html

[Data Types in Rails]

「Rails テーブルのデータ型について」：https://qiita.com/s_tatsuki/items/900d662a905c7e36b3d4
「[Rails]データベースのデータ型種類について勉強してみた！」：https://qiita.com/jackie0922youhei/items/472ae8a35455475b3de8

[Table Column Constraint in Rails]

「[Rails]テーブルにおける制約の種類まとめ」：https://qiita.com/kenji-nishie/items/0e37cd7b2459a7a31cfb
「Rails　テーブルの制約について」：https://qiita.com/oteko7/items/2b03fa13d2f1c91e67e2

[Not Null Constraint]

「RailsでNotNull制約を後からカラムに追加する方法2種類」：https://qiita.com/MOssan-32/items/89afc9e4375215f8b5d2

[Text Limit Constraint]

「RailsのMigrationで文字列型の長さを変更」：http://www.iwazer.com/~iwazawa/diary/2008/03/railsmigration.html

[FK Constraint]

「外部キーの概要と制約を使うことのメリット・デメリット」：https://qiita.com/kamillle/items/5ca2db470f199c1bc3ef
「Railsの外部キー制約とreference型について」：https://qiita.com/ryouzi/items/2682e7e8a86fd2b1ae47
「【Ruby on Rails】「外部キー」の設定のされ方」：http://www.code-magagine.com/?p=12427
「Railsで外部キー制約の参照制約の書き方」：https://qiita.com/na-777/items/cf17f8c79cc76af2898d
「外部キー制約とは」：https://wa3.i-3-i.info/word17575.html
「外部キー制約が一切ないと何に困るのか？」：https://zenn.dev/dowanna6/articles/2667cbb1ab7233
「【SQL入門】外部キーとは？主キーとの関係や作成方法について解説」：https://www.sejuku.net/blog/54072
「[Rails] dependent: :destroy について」：https://qiita.com/Tsh-43879562/items/fbc968453a7063776637
「Ruby on Railsでテーブルのカラムを追加・削除する方法」：https://reasonable-code.com/rails-column/
「Railsでユーザーフォローの機能を作る（テーブル設計〜アソシエーションまで）」：https://zenn.dev/masanarih0ri/articles/dbc544a79a26ee
★「マイグレーションにおいて参照先テーブル名を自動で推定できないカラムを外部キーとして指定する方法」：https://qiita.com/kymmt90/items/03cb9366ff87db69f539
「外部キー制約を追加」：https://railsdoc.com/page/add_foreign_key

[Table Index]

「MySQL初級者を脱するために勉強してること -INDEX編-」：https://qiita.com/kkyouhei/items/e3502ef632c48d94541d
「データベースにindexを張る方法」：https://qiita.com/seiya1121/items/fb074d727c6f40a55f22
「拝啓〜データベースにIndex貼ってますか？〜」：https://qiita.com/ren0826jam/items/89d6602ccaecb7866455

[Others]

「ビット？バイト？データ量の表し方」：https://www.networld.co.jp/sp/learn_first/storage/p22_24.html
「カラムの修正、追加、削除、変更、データ型の変更」：https://qiita.com/iverson3kobe0824/items/6dfce930d57dcc5dd482
「rails generate migrationコマンドまとめ」：https://qiita.com/zaru/items/cde2c46b6126867a1a64

Summary/What I learned this time

Since table definitions are the basis for creating services, they should be created correctly to prevent defects and to make them easy to use for users and developers.
I would like to be able to design tables more appropriately through repetition of “creating new tables,” “reviewing the initial table design after the service start,” “modifying existing tables,” and so on.
I would like to become a higher level developer by accumulating small efforts one by one.