sql oracle Advent Calendar 2018の**10日目です。

枠が余っていたので、書いてみました。とりとめもない話です。19年のが始まるまでには埋めたい。。

sql oracle Advent Calendar 2018

はじめに

SQLを用いた実装において、開発者がよくやるアンチパターンをまとめた著書「SQLアンチパターン」。
普段、開発の中でSQLを使用しているのですが、自分もやっていたかもしれないアンチパターンに関してアウトプットしていきます。

第1章　ジェイウォーク(信号無視)

「多対多」の関連を表現する交差テーブルを避けるために、カンマ区切りのリストを使うこと。
このアンチパターンを著書ではジェイウォーク(信号無視)と名付けています。

1.1 目的

今回サンプルとして使われた事例はProductテーブルというテーブルに、製品名とその連絡窓口の連絡先を複数管理するというものです。

以下がテーブルのDDlです。

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEEY,
  Product_name VARCHAR(1000),
  account_id BIGINT UNSIGNED
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Products (
  product_id, product_name, account_id
) VALUES (
  DEFAULT, "Visual TurboBuilder", 12
);

つまりProductsテーブルの1つの行が、複数の連絡先をもてるようにしなければならない。

1.2 アンチパターン

account_id列をVARCHAR型の列として定義して、複数アカウントのIDをカンマ区切りで連結して格納できるようにしました。

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEEY,
  Product_name VARCHAR(1000),
  account_id VARCHAR(100)
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Products (
  product_id, product_name, account_id
) VALUES (
  DEFAULT, "Visual TurboBuilder", "12, 34"
);

1.2.1 特定アカウントに関連する製品の検索

全ての外部キーが文字列連結されて1つのフィールドに格納されていると、クエリを作ることが難かしくなります。
例えばaccount_idが12のアカウントが指定された全ての製品を取得するためには以下のように記述するらしい。

SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]'

上記のように何らかの文字列パターンに対するパターンマッチが必要になり、式の書き方によっては意図しない一致結果が返される可能性があり、インデックスを使うメリットも得られなくなる。

以下、パターンマッチに関して。
https://dev.mysql.com/doc/refman/5.6/ja/pattern-matching.html

1.2.2 特定の製品に関連するアカウントの検索

同様に、カンマ区切りのリストを使って参照先のテーブルと結合(JOIN)するのには手間がかかる。

SELECT * FROM Products AS p INNER JOIN Accounts AS a
  ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE account_id = 123;

このような式で2つのテーブルを結合すると、インデックスを使う機会が失われる。

1.2.3 集約クエリの作成

集約クエリではCOUNT, SUM AVGなどの関数を利用。
だが、これらの関数はカンマ区切りリストではなく、複数の行に対して使われるように設計されている。

SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id,',','')) + 1
  AS contacts_per_product
FROM Products;

上記のようなクエリを書く必要があり、開発に時間がかかり、デバッグも難かしくなる。

1.2.4 特定の製品に関連するアカウントの更新

1.2.5 アカウントIDの妥当性検証

ユーザーは様々な値を入力できるので、IDではない他のデータをインサートすることができてしまう。

INSERT INTO Products (
  product_id, product_name, account_id
) VALUES (
  DEFAULT, "Visual TurboBuilder", "12, 34, aaaaa"
);

1.2.6 区切り文字の選択

文字列のリストを格納する場合、リストの個々の入力値にも区切り文字(カンマ)が含まれる。
そのため、カンマを区切り文字に指定している場合には、入力された文字列の一部のカンマとして使われているのか、各要素を区切るために使われているのかが不明確になる。

1.2.7 リストの長さの制限

データ型VARCHAR(30)の大きさによって、格納できるIDの数に限界がある。

1.3 解決策: 交差テーブルを作成する

account_idをProductテーブルに格納するのではなく、新たに作成したテーブルの各行にaccount_idを１つずつ格納します。
新たに作成したこのContactsテーブルによって、ProductsとAccountsの間には「多対多」の関係が生じる。

CREATE TABLE Contacts (
  product_id BIGINT UNSIGN NOT NULL,
  account_id BIGINT UNSIGN NOT NULL,
  PRIMARY KEY (product_id, account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (product_id) REFERENCES Accounts(account_id),
);

INSERT INTO Contacts (
  product_id, account_id
) VALUES 
  (123, 12),  
  (123, 34), 
  (345, 23), 
  (567, 12), 
  (567, 34)
;

テーブルが2つのテーブルを参照する外部キーを持つ時、そのテーブルは交差テーブルという。
つまり、製品は複数のアカウントに、アカウントは複数の製品に結びつけられる。

株式会社いい生活の長期インターンを退職したので、退職ポエムを書きます。

期間

長期インターンの期間は12月末から3月いっぱいの3ヵ月くらい。1Weekサマーインターンにも参加してた。

参加経緯

Wantedlyでお金がもらえるサマーインターンを探してたら見つけた。

いい金額がもらえそうだったので飛びついたら通っちゃいました。

サマーインターン終了後に連絡したら、快く長期インターンも受け入れてもらえました。もちろん有給です。

本当にありがとうございます。

作業内容

3ヵ月ほどいましたが、1月半くらいづつ2か所の部署で作業してました。
作業内容はこちらの要望に沿った形で用意してくれました。

1. ダッシュボードの構築
   • Redmineからのチケット収集プログラム
   • Redashの環境構築
     を行って部署のダッシュボードをつくって、データを可視化したりしてました。
2. 物件の売値推定
   実際の物件データをもらえたので、それを使って物件がいくらで売れるか機械学習してました。

感想

• おいしいご飯をごちそうになれる
• コードレビューは勉強になった
• インターン内容の自由度が高い
• Slackに社員の顔スタンプがある
• 家が遠かったのでリモートで参加できた
  • 本当に助かりました

三か月ほどでしたが、とてもためになったインターンでした。早川さんがごはんおごってくれるらしいので、時々伺えたらと思っています。

概要

プログラミング初心者が一年くらいudemyで勉強をしました
修了したコースの感想を書いてます

今回紹介するのはSQL初心者向けの教材です
基礎知識ゼロからでもいけます、講師も「Complete beginners to MySQL are welcome」と言ってるので初めてSQLを勉強する！って人にもおすすめです

使った講座

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

MySQLを使った基本的なSQLのコマンドから、最後は用意されているウェブアプリを使ってデータベースの変更がブラウザ上の情報に反映されるところまで勉強できます
実際にブラウザで表示できるようにしてくれてるのは、僕としては何にデータベースの情報が使われてるのか体験できてありがたかったです

受講した頃の自分

応用情報処理技術者試験の勉強をしていたので
SELECTなどの基本的なSQLのコマンドは知ってた

railsで間接的に触ったことはあった

ただSQLを書いて使用した経験はほぼゼロ

良かった点

• めちゃめちゃ丁寧

アバウトですみません笑

初心者向けと言っているだけあってとても丁寧です
インストールの仕方から始まり、各レクチャーの終わりには
そのレクチャーで入力したコードが文字起こしされています

またSQLとMySQLは何が違うの？みたいな素朴な疑問、概念、考え方なども教えてくれています
(↑最初の頃よくわかってなかった)

• 各セクションごとに確認テストがある

各セクションが終わるごとに確認テストが用意されています

内容はスライドに示す結果と同じ結果になるSQLを書いてねって感じで
セクションの内容全部使えばできるようになっています

僕の場合独学だとインプットばっかりになって中々アウトプットしないことが多いので
セクションごとに勉強した内容が身になってるか確認できるのは良かったです

今一つだった点

• テストが簡単だった

初心者向け！、未経験でも大丈夫！！とうたっているのでしょうがないかもしれませんが
確認テストが少し簡単すぎるかなあと思いました

応用情報の問題で出てきたのはもう少し難しかったので
せめてそのくらいの難易度はあっても良かったかなと思います

まとめ

SQLを触ったことがない、勉強したいけどどうやったらいいかわからないって人は
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
やってみてもいいと思います

スネークケースとは

テーブル定義では、スネークケース(_区切り)で定義することが多い。
例えば、ユーザー情報を格納するマスタの例で説明する。
テーブル名：mst_user

論理名	物理名
ユーザーID	user_id
ユーザー名	user_name

アプリケーション開発との関連

アプリケーション開発言語はスネークケースではないことが多い。
例えば、javaの場合、パスカルケースとキャメルケースだったりする。

何が問題か

javaを例にあげると、mst_userに対応するクラス(DTO)はJavaの規約(パスカルケース、キャメルケース)に従うと以下のようになる。

public class MstUser {
    private String userId;
    private String userName;

    // getter,setterは省略
}

テーブルはスネークケースなのにjavaの規約が異なるためマッピングが必要となる。
これに付随する問題がいくつか発生する。

1. 仕様書作成時の負担が大きい
   例）API仕様書作成時に間違える。
   大抵は項目が多いのでテーブル定義から項目をコピーしてスネークケースをキャメルケースに置き換えていくことになるが間違えやすい。
   user_id => userid => 間違えた！
   特に、設計だけして開発を外部に依頼する場合、「userIdでは?」とQ&Aがきたりして時間の無駄。

2. 開発時にマッピングミスすると面倒
   SQLがスネークケースのため、javaのクラスへのマッピングを記載する必要がある。
   ORマッパーで自動生成できれば、結合(join)するような仕様だと地道にマッピングを書くことになるのでミスする可能性がある。値の取得結果がnullになる=>マッピングミスだったということが起こる。

3. スネークケースとキャメルケースの相互変換が面倒
   1,2とも関連するが、そもそもマッピングをする際にスネークケース=>キャメルケース、キャメルケース=>スネークケースの変換をするのがかなりの手間になる。
   ツールを作れば多少はマシになるかもしれないが、やっぱり手間である。

スネークケースをやめて開発言語の規約に合わせる

javaの規約に従ってみる。

テーブル名：MstUser

論理名	物理名
ユーザーID	userId
ユーザー名	userName

DTO

public class MstUser {
    private String userId;
    private String userName;

    // getter,setterは省略
}

これでマッピングの手間がなくなるので、効率よく設計、開発ができそう。
デメリットはあるだろうか？

目次