Zenn
📊

【AppSheet】データモデリングの学習

2023/06/21に公開
DB
AppSheet
データモデリング
tech

はじめに

AppSheetは、データベース初心者であっても、エクセルやスプレッドシートのような表計算ソフトで表を作り、その表をソースとして取り込むとアプリが自動で生成され、あとは項目の設定や表示方法を変更しながらアプリを完成させることができる便利なノーコードツールではあるが、複雑なモデルになればなるほど、思い通りに動いてくれなかったり、思わぬバグで頭を悩ますことになる。


表計算ソフトからAppSheetでアプリを作成するイメージ

そこで必要とされる知識がデータモデリングというプロセスで、データベースを作る前に、入出力されるデータの流れをER図（Entity Relationship Diagram）という設計図で図式化し、データベースに求められる要件を満たす構造や機能を定義していくことが重要となる。一説には、「データモデリング8割、実装2割」とも言われているくらいである。


ER図のイメージ

データベースにおける用語

データモデリングを学習する前に、データベースにおける基本的な用語をまとめておく。

Table：テーブル

表計算ソフトでいうシートのこと。
データベースではデータモデリングによって複数のテーブルを持つことが多い。エクセルでいうブックのように、いくつかのテーブルを1つのファイルにまとめて用意することもあるが、拡張性やメンテナンス性を考慮して、1つのファイルに1つのテーブルで用意することを推奨する。

Column：カラム

表計算ソフトでいう列のこと。
カラムは、先頭行の列につけた「名前」や「住所」などといった項目のことで、「テキスト型」や「日付型」などといったデータ型（属性）を持つ。このデータ型はデータを取り込んだ際に、AppSheet側で自動的に割り当ててくれるが、意図した機能を持たせるよう修正が必要である。

Record：レコード

表計算ソフトでいう行のこと。
アプリでデータを入力すると、1行ずつレコードとして追加される。
ちなみに、現在処理をしようとしているレコードのことを[_THISROW]（直訳すると「この行」）というふうに記述する。

Field：フィールド

表計算ソフトでいうセルのこと。
レコードを構成する1つ1つの要素のことで、入力フォームのデータを入力するスペースもフィールドという。
レコードで説明したように、現在処理をしようとしているフィールドを指定する場合には、[_THISROW].[カラム名]（=この行のカラム）と記述する。


用語の説明

KEY：キー

KEYは特定のレコードを選択するために必要な一意の値。
AppSheetでは、KEYに指定するカラムの名前に「ID」をつけることで、自動的に8桁の16進数からなるコードを付与する関数UNIQUEID()が初期値(INITIAL VALUE)の欄に設定される。


KEYの説明

LABEL：ラベル

LABELはレコードを代表するユーザー側の値。
AppSheet側はKEYでレコードを特定できるが、ユーザー側ではKEYを見ただけでは何のレコードか分からない。一般的には名前や日付など、ユーザーが識別しやすいものをLABELとして設定するが、同名のレコードが複数あり特定が困難な場合は、バーチャルカラムを利用して「VC名前_日付」など識別しやすい値を作ってLABELに設定する。


KEYとLABELの関係

テーブルの設計

テーブルを設計する際に、留意すべきポイントをいくつか上げてみる。
特にテーブルの正規化については、データの信頼性を高め、更新を効率化するために必要不可欠な作業である。

KEYの設定

KEYを設定するのに、入力日時や頭文字に通し番号（例.A23001）など、人間が認識しやすい値をつけたりする場合があるが、KEY自体に意味を持たせてしまうと重複する可能性が否めないため、ランダムな値をつけることが推奨されている。

カラムの設定

カラムの名前はUIの使い勝手に影響してくるので、アプリを使用するユーザーにヒアリングを行い、日常業務で使用している名前をつけるようにするとよい。
原則として、1つのフィールドに1つの値を入れる。AppSheetの機能の一つでEnumListという複数の値を選択して入力ができるデータ型があるが、原則から外れるので使用しないようにしている。
また、表計算ソフトであらかじめデータを用意する場合、セルを結合したり、セル内に計算式や関数を入れたりはしない。

テーブルの正規化

テーブルの正規化とは、データモデルを行う際に、テーブル内のデータの重複をなくし、適宜データを分割・整理することである。
正規化のメリットは、データの作成・読み出し・更新・削除の処理を行うときに発生する不整合や喪失のリスクを減らすことができ、メンテナンスの効率を高めることができる。


正規化のメリット

非正規形

非正規形とは、正規化がまったく行われておらず、1行の中に複数の繰り返し項目が存在するようなテーブルのこと。
以下の商品管理テーブルでは、仕入先情報のセルが仕入れた商品の行数分だけ結合され、新たに仕入れが行われるごとに行を挿入していかなければならない構造になっている。


非正規形のテーブル

第一正規形

  • 第一正規形では、同一行内での繰り返しをなくす
  • 第一正規形では、導出項目は持たない

リレーショナルデータベースでは原則としてレコード単位で個々のデータを扱うので、このままではデータをデータベースに正しく格納することができない。まずは、結合しているセルを分割し、1つのレコードとして整理する。
また、「在庫数」は「入荷数」の合計から導出することができるので、このように他のカラムから導出可能な項目は、この段階で削除しておく。


第一正規形のテーブル

第二正規形

  • 第二正規形では、部分関数従属性をなくす

データベースを設計する際、目的の行（レコード）を識別するための項目である主キーを見つける。一つのテーブルに主キーが複数ある場合、そのうちの一つの主キーだけで決定できる項目がある場合は部分関数従属となる。
商品管理テーブルでは、入荷に関する情報（「入荷数」）は「仕入先番号」、「商品番号」、「入荷日」の組み合わせで決定されるが、仕入先に関する情報（「仕入先」、「担当者名」、「連絡先」）は「仕入先番号」のみで特定することができる。この場合、仕入先に関する情報は部分関数従属となり、主キーは「仕入先番号」になる。同様に、商品に関する情報（「商品名」、「単価」、「産地」）も部分関数従属となり、主キーは「商品番号」である。


主キーと部分関数従属

そこで商品管理テーブルを、①仕入先に関する情報、②商品に関する情報、③入荷に関する情報の3つのテーブルに分離する。


第二正規形のテーブル

いずれのテーブルにおいても非キー属性は主キーに従属する形になった。この状態を完全関数従属と呼び、これらを満たしている形式が第二正規形となる。

第三正規形

  • 第三正規形では、どのテーブルにも推移関数従属性が存在しない状態にする

推移関数従属性とは、テーブル内の一部の項目が主キーでない項目によって決定されることである。
仕入先テーブルにおいて、担当者の「連絡先」は主キーではないが「担当者名」で決定される。
たとえば仕入先の麦米製粉では、製粉事業や加工事業、倉庫事業など幅広く事業展開をしており、それぞれの事業部ごとに担当者がいたとする。それが異動により、加工事業と倉庫事業の担当者が抜け、製粉事業の担当者が他の2つの事業部の担当も兼任することになったとする。その場合、仕入先テーブルは、担当者名と連絡先のそれぞれを変更しなければならない。それに伴い、これまで担当していた担当者名と連絡先も消えてしまう。


推移件数従属性の例

そこで、「担当者名」をキーとして、仕入先テーブルから「連絡先」を分離して、新たに担当者テーブルを作成する。また、担当者テーブルには先頭列に「担当者番号」を追加し、仕入先テーブルとの紐づけに利用する。


第三正規形のテーブル

このように、非キー属性がすべて主キーに完全関数従属し、なおかつ推移的関数従属性が排除された形式を第三正規形と呼ぶ。

データモデリング

次に正規化されたテーブルをもとに、データモデリングを行っていく。
その前にテーブルに少し手を加える。先頭列にそのテーブルのKEYとなるカラムを配置し、関数従属関係がある場合は次の列に参照するテーブルのKEYカラムを配置する。
ここで、KEYカラムにつける名前を「ID（テーブル名）」としておくと、テーブル間のリレーションを確認する際や、SELECT()関数でKEYカラムを検索値として、別テーブルから値を持ってくる式を書くときに分かりやすくて便利である。


テーブルの前準備

ER図

テーブルを簡略化した図形を使って、データの入出力を図式化する。
テーブルの設計が先か、モデリングが先かという問題もあるが、今回のようにあらかじめソースとなる表がある場合はテーブルを設計した後にER図を作成し、何もないところから新規にデータベースを検討する場合には、ER図で検討した後にテーブルを設計した方が進めやすい。


テーブルを設計した後にER図を作成する


ER図を作成した後にテーブルを設計する

テーブルの構成

AppSheetにおいて、テーブル同士にリレーションを持たせる場合、カラムのデータ型をRef型に設定し、参照するテーブルを選択した時点でリレーションシップのタイプが自動的に選択されるようになっている。最初のうちはタイプを意識することが少ないが、リレーションシップを理解していないと、思わぬ不具合を招くことがある。特に上位階層と下位階層の関係を掴んでいないと、例えば参照（Reference：REF_ROWS(dataset, ref-column)）するのか、逆参照（Dereference：[ref-column].[value-colum]）するのかが正しく適用できていないことがある。

1対1のリレーションシップ

1対1のリレーションシップでは、テーブル内の1つのレコードが別のテーブルの1つのレコードのみと関連付けられる。
商品管理テーブルの例でいうと、「担当者」と「仕入先」が必ず1対1の状態でつながっているテーブルの関係になる。


1対1のリレーションシップ

1対多のリレーションシップ

1対多のリレーションシップでは、テーブル内の1つのレコードが別のテーブルの1つ以上のレコードのみと関連付けられる。
「担当者」と「仕入先」の例でいうと、麦米製粉の担当者が3つの事業部を兼任する場合は1対多のテーブルの関係になる。


1対多のリレーションシップ

多対多のリレーションシップ

あるテーブルの複数のレコードが別のテーブルの複数のレコードと関連付けられている場合は多対多のリレーションシップが発生する。
「仕入先」と「商品」の関係を見てみる。「仕入先」には直販店もあれば、卸売業者もあるので、2つのテーブルを関連付けると多対多の関係になる。


多対多のリレーションシップ

仮に、「仕入先」テーブルと「商品」テーブルをつなげてみると、みかんといちごのレコードの「ID仕入先」カラムには{JA農協のID,河内青果のID}の2つの値が存在することになり、仕入先が特定できない状態になる。
こうした問題を回避するために中間テーブルという第三のテーブルを使用して、多対多のリレーションシップを二つの1対多のリレーションシップに分割して解決する。今回のケースだと、「入荷」テーブルが中間テーブルの役目を担っている。


中間テーブルを用いたリレーションシップ

中間テーブルを使用することで、10/21に入荷されたみかんの仕入先はJA農協、10/27に入荷されたみかんの仕入先は河内青果と識別することができるようになった。

自己連結のリレーションシップ

ここから先のリレーションシップについては、少し特殊なケースとなる。
たとえば、社内通貨[1]（企業独自のポイント制度）のモデリングについて考えてみる。
社内通貨について簡単に説明すると、営業の田中さんが、日頃お世話になっている経理の鈴木さんにお礼としてを社内で使えるポイントを送るといった制度である。


社内通貨のイメージ

田中さんも鈴木さんも同じ会社の社員なので、ER図で表現すると下図のようになる。


自己連結を用いたリレーションシップ

上図では、社員テーブルを複製して社員（あげる人）テーブルと社員（もらう人）テーブルの2つのテーブルに分けて表現したが、1つの社員テーブルのままで、ポイントテーブルの社員ID（あげる人）と社員ID（もらう人）のそれぞれに外部キーとして繋げることもできる。この場合、社員テーブルとポイントテーブル間で循環した関係になるので、取扱いに注意が必要となる。


自己連結を用いたリレーションシップ（循環したモデル）

自己参照のリレーションシップ

自己連結の例としてあげた社内通貨制度の場合は、あげる人ともらう人の関係が多対多の関係にあったが、たとえば会社の上司と部下の関係について見てみる。一般的には、一人の社員に対し、直属の上司の人数は1人（もしくはその社員が所属長の場合は0人）となり、1対多の関係になる。


会社の上司と部下の関係

上司も部下も同じ会社の社員なので、社員テーブルを使ってER図を書くと以下のように表現できる。


自己参照を用いたリレーションシップ

このように、自分自身のテーブルを使って親子構造を表したモデルを、自己参照のリレーションシップという。

テーブルの参照

データベースを使用するときに、第一正規形に正規化する際に削除した導出項目（例では在庫数）を求めたいときや、その日の仕入れの合計金額を求めたいときはどのようにするのかなど、参照の機能（または関数）を使って具体的にみていく。

REF_ROWS()：テーブルから関連するレコードを収集する

上位テーブルにおいて、Ref型で参照されている下位テーブルのレコードのうち、Ref型で指定したカラムと同じ値を持つレコードをリストとして返す。

REF_ROWS(dataset, ref-column)
  • dataset：Ref型で参照されている下位テーブル
  • ref-column：Ref型で指定したカラム

入荷履歴を参照する

AppSheetでは、Ref型でテーブル同士を繋いだ時、自動で上位テーブルにREF_ROWS()で関連するレコードを収集するバーチャルカラム（Related テーブル名s）が作られる。


入荷履歴を参照する

「入荷」テーブルから、「仕入先」テーブルの「ID仕入先」と同じ値を持つレコードを検索し、リストとして「Related 入荷s」に格納する。
AppSheetでは、詳細ビュー「仕入先_Detail」にtableタイプとして表形式で出力される。

REF_ROWS("入荷", "ID仕入先")

Dereference expressions：参照したレコードからカラムの値を取得する

逆参照式はテーブル間の参照を利用し、下位テーブルのレコードより、Ref型のカラムの値を使用して上位テーブルのレコードを検索し、カラムの値を取得する。

[ref-column].[value-column]
  • ref-column：Ref型で指定したカラム
  • value-column：取得したい値が入っているカラム

商品の単価を参照する

商品の単価を参照し、入荷数と掛けて金額を求める。


商品の単価を参照する

「商品」テーブルから「入荷」テーブルの「ID商品」に対応する「単価」を取得して、バーチャルカラム「VC単価」に格納する。

[ID商品].[単価]

また、「VC金額」は「VC単価」に「入荷数」を掛け合わせて求める。

[VC単価]*[入荷数]

SELECT()：テーブルから特定のレコードを選択する

指定したテーブルから、真偽式に合致するレコードにあるカラムをリストとして返す。

SELECT(from-dataset-column, select-row?, [distinct-only?])
  • from-dataset-column：参照するテーブルとカラム名をテーブル名[カラム]の表記で設定する。
  • select-row?：レコードごとに評価される真偽式。TRUE または FALSE を返し、そのレコードのカラムの値をリストとして含める (TRUE) か除外する (FALSE) かを設定する。
  • distinct-only?：TRUE または FALSE を指定する。
    select-row?に該当するすべての値をリストに含める場合は FALSE に設定し、重複する値を省略する場合は TRUE に設定します。指定しない場合は、FALSE が設定される。

商品の在庫数を求める

第一正規形で削除した商品の在庫数を、SELECT()関数を使って求める。


商品の在庫数を求める

「商品」テーブルに商品別の在庫数を持たせます。まず、SELECT()関数で「入荷」テーブルのレコード中から、「商品」テーブルの「ID商品」と同じ値を持つレコードを抽出し、「入荷数」のリストを作る。SELECT()関数で作成した「入荷数」のリストを、SUM()関数で合計値を求め、バーチャルカラム「VC在庫数」に値を格納する。

SUM(
  SELECT( 入荷[入荷数], [ID商品] = [_THISROW].[ID商品] )
)

仕入先からの仕入金額を求める

同様に、仕入先ごとの仕入金額の合計値を、SELECT()関数とSUM()関数を組み合わせて求める。


仕入金額の合計を求める

「仕入先」テーブルに仕入金額の合計を持たせます。在庫数の時と同様に、SELECT()関数で「入荷」テーブルのレコード中から、「仕入先」テーブルの「ID仕入先」と同じ値を持つレコードを抽出し、「VC金額」のリストを作る。SELECT()関数で作成した「VC金額」のリストを、SUM()関数で合計値を求め、バーチャルカラム「VC仕入合計」に値を格納する。

SUM(
  SELECT( 入荷[VC金額], [ID仕入先] = [_THISROW].[ID仕入先] )
)

おわりに

今回は、データモデリングについて、主にネットで調べたことについてまとめてみた。
参考にした資料は専門用語を使った解説が多かったので、なるべく分かりやすい言葉で置き換えて説明ができるよう試みてみたが、なかには解釈が違っているものがあるかもしれない。
この記事を読んでいただいた方には、ご意見やご指摘がありましたらお願いします。

参考文献

https://www.youtube.com/@yomikakiPC

AppSheet Help　公式ヘルプ
https://support.google.com/appsheet?sjid=9923089967423897886-AP#topic=11981018

GMO CLOUD アカデミー
データベースの用語を理解しよう 「テーブル」「レコード」「カラム」「フィールド」とは？
https://academy.gmocloud.com/know/20160425/2259

ITエンジニアの備忘録的技術ブログ【仮】
【データベース入門】テーブル設計初心者がやってはいけない9個の事例の紹介と解説
https://www.depthbomb.net/?p=6334#toc1

株式会社システムインテグレータ
データベースの正規化とは？
https://products.sint.co.jp/topsic/blog/database-normalization

FileMaker Pro18 Advanced
リレーションシップについて
https://fmhelp.filemaker.com/help/18/fmp/ja/index.html#page/FMP_Help%2Frelationships.html%23

脚注

  1. 「社内通貨」とは、企業が自社の従業員向けに発行する社内利用限定の通貨のこと。 企業独自のポイント制度ともいえるもので、企業が従業員に社内通貨を付与することでモチベーション向上を図ったり、従業員同士が社内通貨を贈り合うことで社内コミュニケーションの円滑化を促したりというように使われる。 ↩︎

Discussion