新しいテーブルやデータウェアハウスを作成する際には、多くの決定事項があります。その時には取るに足らないと思われることでも、データベースが存在する限り、何が起こるかわかりません。
Sisenseデータチームは何千人もの人々とそのデータベースに関わってきて、数え切れないほどの時間をかけてクエリを読んだり書いたりした結果、ほとんどすべてのことを目にしてきました。ここでは、後々苦労しないスキーマを作成するためのトップ10のルールをご紹介します。
データベース名、スキーマ名、テーブル名、カラム名には、ドット、スペース、ダッシュを使用しないでください。ドットはオブジェクトを識別するためのもので、通常は database.schema.table.column のパターンで使用します。
オブジェクトの名前にドットを使用すると、混乱の原因となります。同様に、オブジェクト名にスペースを使用すると、クエリに不要な引用符の束を追加しなければなりません。
select "user name" from events
-- vs
select user_name from events
テーブル名やカラム名に大文字を使うと、クエリが書きにくくなります。すべてが小文字であれば、usersテーブルがUsersなのかusersなのかを覚える必要はありません。
また、データベースを変更したり、テーブルをウェアハウスに複製したりする際にも、どのデータベースが大文字と小文字を区別するかを覚えておく必要はありません（一部のデータベースのみ）。
usersテーブルがpackagesテーブルへの外部キーを必要とする場合、そのキーにpackage_idという名前をつけます。pkg_fkのような短くて暗号のような名前は避けてください、他の人には意味がわかりません。記述的な名前は、他の人がスキーマを理解するのを容易にし、チームが拡大しても効率を維持するのに不可欠です。
多義的なデータに曖昧な名前を使わない。item_typeやitem_valueのパターンでカラムを作成している場合は、photo_count、view_count、transaction_priceなどの具体的な名前のカラムを多く使用した方が良いでしょう。
こうすることで、カラムの内容は常にスキーマから知ることができ、行の他の値に依存することはありません。
select sum(item_value) as photo_count
from items
where item_type = 'Photo Count'
-- vs
select sum(photo_count) from items
カラム名の前に、含まれるテーブルの名前を付けないでください。一般的に、usersテーブルにuser_birthday、user_created_at、user_nameのようなカラムを含めることは有益ではありません。
column、tag、userなどの予約済みキーワードをカラム名として使用することは避けてください。クエリの中で余分な引用符を使わなければならず、それを忘れると非常に分かりにくいエラーメッセージが表示されます。カラム名として使われるべき場所にキーワードが表示されると、データベースはクエリを大きく誤解してしまいます。
テーブル名が複数の単語で構成されている場合は、アンダースコアで単語を区切ります。packagedeliveriesよりもpackage_deliveriesの方が読みやすいですね。
また、可能な限り、2つの単語ではなく1つの単語を使用してください：deliveriesはさらに読みやすくなります。
select * from packagedeliveries
-- vs
select * from deliveries
テーブルの前にスキーマを意味するような接頭辞をつけないでください。テーブルをグループ化してスコープにする必要がある場合は、それらのテーブルをスキーマに入れてください。一般的に、store_items, store_transactions, store_coupons のように名前のテーブルを接頭辞付きのカラム名のようにする入力は意味がありません。
テーブルには複数形の名前を使用し（packagesなど）、結合テーブルの名前では両方の単語を複数形にすることをお勧めします（packages_usersなど）。単数形のテーブル名は、予約済みのキーワードと誤って衝突する可能性が高く、一般的にクエリでの可読性が低くなります。
UUIDを使用していたり、それが意味をなさない場合（結合テーブルの場合など）でも、自動インクリメントの整数列を持つ標準的なidカラムを追加します。このようなキーがあると、グループの最初の行だけを選択するなど、特定の分析が非常に簡単になります。
また、インポートジョブでデータが重複してしまった場合、特定の行を削除することができるので、このキーは非常に役立ちます：
delete from my_table
where id in (select ...) as duplicated_ids
また、複数列の主キーは避けましょう。主キーを論理的に導くことが難しく、また変更するのも非常に困難になり非効率です。代わりに、整数の主キー、複数列のユニーク制約、いくつかの単一列のインデックスを使用してください。
主キーと外部キーの名前には様々なスタイルがあります。私たちが推奨する最もポピュラーな方法は、すべてのテーブルfooにidという主キーを持ち、すべての外部キーにfoo_idという名前をつけることです。
もうひとつのポピュラーなスタイルは、グローバルに一意なキー名を使用するもので、foo テーブルに foo_id という名前の主キーがあり、すべての外部キーも foo_id という名前になります。略語を使うと混乱したり、名前の衝突が起きたりするので（例：usersテーブルにuid）、略さないようにしましょう。
どのようなスタイルを選んでも、それを守りましょう。ある場所ではuidを使い、別の場所ではuser_idやusers_fkを使わないようにしましょう。
select *
from packages
join users on users.user_id = packages.uid
-- vs
select *
from packages
join users on users.id = packages.user_id
-- or
select *
from packages
join users using (user_id)
また、明らかにテーブルと一致しない外部キーには注意が必要です。owner_idという名前のカラムは、usersテーブルへの外部キーかもしれませんし、そうでないかもしれません。カラムの名前はuser_id、必要に応じてowner_user_idとしてください。
Unixのタイムスタンプや文字列を日付として保存しないで、代わりにデータタイムに変換してください。SQLの日付計算関数は決して優れたものではありませんが、タイムスタンプを自分で処理するのはさらに困難です。SQLの日付関数を使用するには、すべての問い合わせでタイムスタンプからデータタイムへの変換が必要です。
select date(from_unixtime(created_at))
from packages
-- vs
select date(created_at)
from packages
年、月、日を別々の列に格納しないでください。これにより、すべての時系列クエリを書くのが非常に難しくなり、ほとんどの初心者SQLユーザーはこのテーブルの日付情報を使用することができなくなります。
select date(created_year || '-'
|| created_month || '-'
|| created_day)
-- vs
select date(created_at)
UTC以外のタイムゾーンを使用すると、無数の問題が発生します。Sisense for Cloud Data Teamsをはじめとする優れたツールには、データをUTCから現在のタイムゾーンに変換するために必要な機能がすべて備わっています。Sisenseでは、:pstを追加するだけで、UTCから太平洋時間に変換することができます。
select [created_at:pst], email_address
from users
データベースのタイムゾーンはUTCであり、すべてのdatetimeカラムはタイムゾーンを除去したタイプでなければなりません（例：タイムゾーンなしのtimestamp）。
データベースのタイムゾーンがUTCでない場合や、データベース内にUTCと非UTCのデータタイムが混在している場合、時系列分析は非常に困難になります。
1つのデータに対して、事実を示すソースは1つだけでなければなりません。ビューやロールアップには、そのようなラベルを付けるべきです。そうすることで、そのデータの消費者は、自分が使っているデータと現場で認識している事実との間に違いがあることを知ることができます。
select *
from daily_usage_rollup
user_id, user_id_old, user_id_v2のようなレガシーなカラムを残しておくと、大きな混乱の元になります。定期的なメンテナンスの際には、使われていないテーブルや使われていないカラムを削除するようにしてください。
超幅広のテーブルは作らない方がいいでしょう。数十個以上のカラムがあり、そのうちのいくつかが連番で名付けられている場合（例：answer1、answer2、answer3）、後で大変な思いをすることになります。
テーブルを、重複するカラムを持たないスキーマにピボットします。このスキーマの形は、クエリが非常に簡単になります。例えば、アンケートの完了した回答数を取得するとします。
select
sum(
(case when answer1 is not null
then 1 else 0 end) +
(case when answer2 is not null
then 1 else 0 end) +
(case when answer3 is not null
then 1 else 0 end)
) as num_answers
from surveys
where id = 123
-- vs
select count(response)
from answers
where survey_id = 123
分析クエリでは、JSON カラムからデータを抽出すると、クエリのパフォーマンスが大幅に低下します。本番環境でJSONカラムを使用する大きな理由はたくさんありますが、分析にはありません。JSON カラムを積極的に図式化して、よりシンプルなデータ型にすることで、分析をより簡単かつ迅速に行うことができます。
日付、郵便番号、国は、外部キーで検索する独自のテーブルを必要としません。そうすると、すべてのクエリが同じ結合をいくつも持つことになります。重複したSQLがたくさんできてしまい、データベースにとっては余計な仕事が増えてしまいます。
select
dates.d,
count(1)
from users
join dates on users.created_date_id = dates.id
group by 1
-- vs
select
date(created_at),
count(1)
from users
group by 1
テーブルは、自分自身のデータをたくさん持っている第一級のオブジェクトのためのものです。それ以外のものは、より重要なオブジェクトの追加の列になります。
これらを守ってより良いスキームを作り上げましょう。
これらのルールを身につけておけば、次のテーブルやウェアハウスは、あなたも新しいチームメンバーも、事業拡大の際に簡単にクエリを実行できるようになります。
※本記事は、「10 Rules for a Better SQL Schema」を翻訳・加筆修正したものです。