Sisense日本公式ブログ

新しいテーブルやデータウェアハウスを作成する際には、多くの決定事項があります。その時には取るに足らないと思われることでも、データベースが存在する限り、何が起こるかわかりません。

Sisenseデータチームは何千人もの人々とそのデータベースに関わってきて、数え切れないほどの時間をかけてクエリを読んだり書いたりした結果、ほとんどすべてのことを目にしてきました。ここでは、後々苦労しないスキーマを作成するためのトップ10のルールをご紹介します。

1. 小文字のアルファベット、数字、アンダースコアのみを使用する

データベース名、スキーマ名、テーブル名、カラム名には、ドット、スペース、ダッシュを使用しないでください。ドットはオブジェクトを識別するためのもので、通常は database.schema.table.column のパターンで使用します。

オブジェクトの名前にドットを使用すると、混乱の原因となります。同様に、オブジェクト名にスペースを使用すると、クエリに不要な引用符の束を追加しなければなりません。

select "user name" from events

-- vs

select user_name from events

テーブル名やカラム名に大文字を使うと、クエリが書きにくくなります。すべてが小文字であれば、usersテーブルがUsersなのかusersなのかを覚える必要はありません。

また、データベースを変更したり、テーブルをウェアハウスに複製したりする際にも、どのデータベースが大文字と小文字を区別するかを覚えておく必要はありません（一部のデータベースのみ）。

2. シンプルで説明的なカラム名の使用

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などの予約済みキーワードをカラム名として使用することは避けてください。クエリの中で余分な引用符を使わなければならず、それを忘れると非常に分かりにくいエラーメッセージが表示されます。カラム名として使われるべき場所にキーワードが表示されると、データベースはクエリを大きく誤解してしまいます。

3. シンプルで説明的なテーブル名の使用

テーブル名が複数の単語で構成されている場合は、アンダースコアで単語を区切ります。packagedeliveriesよりもpackage_deliveriesの方が読みやすいですね。

また、可能な限り、2つの単語ではなく1つの単語を使用してください：deliveriesはさらに読みやすくなります。

select * from packagedeliveries

-- vs

select * from deliveries

テーブルの前にスキーマを意味するような接頭辞をつけないでください。テーブルをグループ化してスコープにする必要がある場合は、それらのテーブルをスキーマに入れてください。一般的に、store_items, store_transactions, store_coupons のように名前のテーブルを接頭辞付きのカラム名のようにする入力は意味がありません。

テーブルには複数形の名前を使用し（packagesなど）、結合テーブルの名前では両方の単語を複数形にすることをお勧めします（packages_usersなど）。単数形のテーブル名は、予約済みのキーワードと誤って衝突する可能性が高く、一般的にクエリでの可読性が低くなります。

4. 整数の主キーを持つ

UUIDを使用していたり、それが意味をなさない場合（結合テーブルの場合など）でも、自動インクリメントの整数列を持つ標準的なidカラムを追加します。このようなキーがあると、グループの最初の行だけを選択するなど、特定の分析が非常に簡単になります。

また、インポートジョブでデータが重複してしまった場合、特定の行を削除することができるので、このキーは非常に役立ちます：

delete from my_table

where id in (select ...) as duplicated_ids

また、複数列の主キーは避けましょう。主キーを論理的に導くことが難しく、また変更するのも非常に困難になり非効率です。代わりに、整数の主キー、複数列のユニーク制約、いくつかの単一列のインデックスを使用してください。

5. 外部キーの一貫性を保つ

主キーと外部キーの名前には様々なスタイルがあります。私たちが推奨する最もポピュラーな方法は、すべてのテーブル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としてください。

6. DatetimesをDatetimesとして保存

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)

7. UTC, Always UTC

UTC以外のタイムゾーンを使用すると、無数の問題が発生します。Sisense for Cloud Data Teamsをはじめとする優れたツールには、データをUTCから現在のタイムゾーンに変換するために必要な機能がすべて備わっています。Sisenseでは、:pstを追加するだけで、UTCから太平洋時間に変換することができます。

select [created_at:pst], email_address

from users

データベースのタイムゾーンはUTCであり、すべてのdatetimeカラムはタイムゾーンを除去したタイプでなければなりません（例：タイムゾーンなしのtimestamp）。

データベースのタイムゾーンがUTCでない場合や、データベース内にUTCと非UTCのデータタイムが混在している場合、時系列分析は非常に困難になります。

8. 事実を示すソースは１つだけ

1つのデータに対して、事実を示すソースは1つだけでなければなりません。ビューやロールアップには、そのようなラベルを付けるべきです。そうすることで、そのデータの消費者は、自分が使っているデータと現場で認識している事実との間に違いがあることを知ることができます。

select *

from daily_usage_rollup

user_id, user_id_old, user_id_v2のようなレガシーなカラムを残しておくと、大きな混乱の元になります。定期的なメンテナンスの際には、使われていないテーブルや使われていないカラムを削除するようにしてください。

9. JSONカラムのないトールテーブルを好む

超幅広のテーブルは作らない方がいいでしょう。数十個以上のカラムがあり、そのうちのいくつかが連番で名付けられている場合（例：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 カラムを積極的に図式化して、よりシンプルなデータ型にすることで、分析をより簡単かつ迅速に行うことができます。

10. 過剰な標準化は禁物

日付、郵便番号、国は、外部キーで検索する独自のテーブルを必要としません。そうすると、すべてのクエリが同じ結合をいくつも持つことになります。重複した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」を翻訳・加筆修正したものです。