データベース設計についてアドバイスください

Question

前提・実現したいこと

IPプロトコルに対応したネットワークカメラやネットワークスピーカーに一括で設定反映する.Netアプリを開発しているのですが、データベース設計に悩んでいます。

実際の機器は、学校のような広い場所にネットワークカメラ5台、ネットワークスピーカ5台といった形で設置されます。

悩んでいる内容

機種は計15品番程度あり、それぞれで設定できる項目が違います。
一方、共通で設定する項目もあります。

ＩＰアドレスや端末名称は全機種設定できる、
ネットワークカメラは音量は設定できないが明るさは設定できる、
ネットワークスピーカーは明るさは設定できないが音量は設定できる、等

イメージとしては以下の通りす。

・各機種ごとに設定できる項目一覧

機種	端末名称	IPアドレス	明るさ	音量	WEB設定ログインID
機種Ａ	〇	〇	〇	〇	〇
機種Ｂ	〇	〇	―	〇
機種Ｃ	〇	〇	〇	―

これらのデータをRDBにどのように保管するか悩んでいます。
端末IDで各端末はユニークになります。

案１：共通項目だけまとめ、個別項目は別テーブルに書き出す

端末テーブル(端末ID(PK)、機種ID、端末名称、IPアドレス等)
機種Ａテーブル（端末ID(PK)、明るさ、音量、Web設定ログインID）
機種Ｂテーブル（端末ID(PK)、音量）
機種Ｃテーブル（端末ID(PK)、明るさ）

イメージ
端末テーブル

端末ID	機種ID	端末名称	IPアドレス
1	機種Ａ	a1	x.x.x.1
2	機種Ａ	a2	x.x.x.2
3	機種Ｂ	b1	x.x.x.3
4	機種Ｂ	b2	x.x.x.4
5	機種Ｃ	c1	x.x.x.5

機種Ａテーブル

端末ID	明るさ	音量	Web設定ログインID
1	3	5	ADMIN
2	3	5	ADMIN

機種Ｂテーブル

端末ID	音量
3	5
4	5

機種Ｃテーブル

端末ID	明るさ
5	5

案２：１つのテーブルでまとめて管理する

端末テーブル（端末ID(PK)、端末種別、端末名称、IPアドレス、明るさ、音量）

・イメージ
端末テーブル

端末ID	機種ID	端末名称	IPアドレス	明るさ	音量	WEB設定ログインID
1	機種Ａ	a1	x.x.x.1	3	5	ADMIN
2	機種Ａ	a2	x.x.x.2	3	5	ADMIN
3	機種Ｂ	b1	x.x.x.3	NULL	5	NULL
4	機種Ｂ	b2	x.x.x.4	NULL	5	NULL
5	機種Ｃ	c1	x.x.x.5	4	NULL	NULL

---

私の感覚でいえば以下のように、案２は矛盾したデータの登録が可能であるため、案１がいいと思っています。

例：
※機種Ｂに設定できてはいけない「明るさ」が設定されているが、ＤＢ制約で防げない（更新時異状）

端末ID	機種ID	端末名称	IPアドレス	明るさ	音量
3	機種Ｂ	b1	x.x.x.3	3	5

.Netアプリのインターフェースはエクセルのような見た目のグリッド型で、複数の機種を同じ画面で設定できるようにする必要があります。
以下のようなイメージです。

・画面出力イメージ

機種	端末名称	IPアドレス	明るさ	音量
機種Ａ	a1	x.x.x.1	3	5
機種Ａ	a2	x.x.x.2	3	5
機種Ｂ	b1	x.x.x.3	設定不可	5
機種Ｂ	b2	x.x.x.4	設定不可	5
機種Ｃ	c1	x.x.x.5	6	設定不可

案１にした場合、Select時に機種分Unionせねばならず、また更新時も煩雑なイメージがあります。

具体的には、上記 ・画面出力イメージ の結果を得ようとした場合、以下のような長いＳＱＬを書く必要があります。

SELECT
    端末テーブル.機種ID,
    端末テーブル.端末名称,
    端末テーブル.IPアドレス,
    機種Ａテーブル.明るさ,
    機種Ａテーブル.音量
FROM
    端末テーブル
    LEFT JOIN
    機種Ａテーブル
    ON
    端末テーブル.端末ID = 機種Ａテーブル.端末ID

UNION ALL

SELECT
    端末テーブル.機種ID,
    端末テーブル.端末名称,
    端末テーブル.IPアドレス,
    NULL,
    機種Ｂテーブル.音量
FROM
    端末テーブル
    LEFT JOIN
    機種Ｂテーブル
    ON
    端末テーブル.端末ID = 機種Ｂテーブル.端末ID

UNION ALL

SELECT
    端末テーブル.機種ID,
    端末テーブル.端末名称,
    端末テーブル.IPアドレス,
    機種Ｃテーブル.明るさ,
    NULL
FROM
    端末テーブル
    LEFT JOIN
    機種Ｃテーブル
    ON
    端末テーブル.端末ID = 機種Ｃテーブル.端末ID

一方案２にすると、SELECT文、UPDATE文はシンプルになるものの、機種Ｂのデータを保存するテーブルに、意味のない「明るさ」というフィールドが出来てしまいます。
またUpdate文も、最大4テーブルたいして行う必要が出てきます。

どちらも一長一短なのですが、皆様だったらどのような設計をされますか？
屈託のない意見を頂きたく宜しくお願いします。

追記：
なお、設定項目は全部で800項目ちかくあり、共通設定項目はそのうち40～80個程度です。

Comments

[dodox86]

「機種」と書いてあるからには、同じ機種で違う端末（≒IPアドレス）、同じ機種で違う端末名称のものが存在するのですか？　ユニークに成り得る項目は何なのでしょうか。機種に依存する設定項目は明るさや音量、端末に依存する項目はIPアドレスのようなイメージを抱きますが、質問者さんのケースではそういうものとは違うのでしょうか。テーブルのカラムに相当するデータ項目がどんなカテゴリに属するかで設計が変わるような気もします。

[pokorin]

機種とは、具体的にはネットワークカメラやネットワークスピーカーのことを指しています。

> 「機種」と書いてあるからには、同じ機種で違う端末（≒IPアドレス）、同じ機種で違う端末名称のものが存在するのですか？　

はい。すべてＩＰプロトコルに対応しており、どの機種もIPアドレス、および端末名称を設定できます。
一方、明るさや音量といった項目は、機種によって設定できる・設定できないがあります。
（ネットワークカメラは音量は設定できないが明るさは設定できる、
　ネットワークスピーカーは明るさは設定できないが音量は設定できる、等）

実際の機器は、学校のような広い場所にネットワークカメラ5台、ネットワークスピーカ5台といった形で設置されます。
機器には「校庭1」「校庭2」等といった、利用するときにわかりやすい名称を付けます。

>ユニークに成り得る項目は何なのでしょうか。

端末IDで端末はユニークになります。

[dodox86]

質問の前提となることだと思いますので、お手数だとは思いますが質問への追記・修正をお願いします。

[nskydiving]

設定項目は必ず数値型でしょうか？
それとも文字列型や他の型もあり得るのでしょうか？

[pokorin]

設定項目は文字型または数値型のみとなります。

Answer 1

json型や配列の得意なDBMSであれば

案２：１つのテーブルでまとめて管理する

で、案１の個別部分を、json型や配列で管理する。

Comments

[pokorin]

ありがとうございます。
その発想はありませんでした。
参考になります。

[sazi]

アンチパターンではありますが、そもそもjson型などはそういった用途ですし。

[dodox86]

こちらの方法ですと、行（Row）単位でみたときに共通のデータ（カラム）部分を基底クラス、拡張部分を基底クラスから継承したクラスとしてコードに表現し易いメリットがあると思います。

Answer 2

すみませんが、Select時に機種分Unionせねばならず、また更新時も煩雑なイメージがとか、更新時異状が発生と言われている意味が理解できないのですが、Masterテーブルに意味のない項目が出来るのを嫌うのであれば、RDBであれば次の様な方法が考えれます。select時は機種IDでJOINすればよく、更新時も機種IDをキーにすればいいだけなので、煩雑にはなりません。

15品目程度であればどの様に設計しても問題なることはあまり無いと思いますが、一応の例として参考までに示します。

機種テーブル

機種	端末名称	IPアドレス
機種Ａ	a	x.x.x.a
機種Ｂ	b	x.x.x.b
機種Ｃ	c	x.x.x.c

設定テーブル

機種	設定種類	設定値
機種Ａ	明るさ	3
機種Ａ	音量	5
機種Ｂ	音量	5
機種Ｃ	明るさ	6

Comments

[pokorin]

ご回答ありがとうございます。
※質問内容をもう少し詳しく更新しました。

頂いたデータベース構造ですが、参考にさせていただきます。
このような構造(Key-Value型というんでしょうか)の場合、

アプリでは1万台まで端末を登録できるので、Key-Value型の場合800万行近くレコードがINSERTされます。
性能面に不安があるので検証してみようと思います。

わからないと頂いた点、説明不足でしたので、詳細を記載します。

----------------------------------------------------
・UNIONしなければならない点

案１の設計の場合、「**・画面出力イメージ**」の結果を得ようとした場合、
以下のような長いSQLが必要となります。

```SQL
SELECT
端末テーブル.機種ID,
端末テーブル.端末名称,
端末テーブル.IPアドレス,
機種Ａテーブル.明るさ,
機種Ａテーブル.音量
FROM
端末テーブル
LEFT JOIN
機種Ａテーブル
ON
端末テーブル.端末ID = 機種Ａテーブル.端末ID

UNION ALL

SELECT
端末テーブル.機種ID,
端末テーブル.端末名称,
端末テーブル.IPアドレス,
NULL,
機種Ｂテーブル.音量
FROM
端末テーブル
LEFT JOIN
機種Ｂテーブル
ON
端末テーブル.端末ID = 機種Ｂテーブル.端末ID

UNION ALL

SELECT
端末テーブル.機種ID,
端末テーブル.端末名称,
端末テーブル.IPアドレス,
機種Ｃテーブル.明るさ,
NULL
FROM
端末テーブル
LEFT JOIN
機種Ｃテーブル
ON
端末テーブル.端末ID = 機種Ｃテーブル.端末ID

```

・更新時異状が発生

以下のような、機種Ｂに設定できてはいけない「明るさ」が設定されてしまうことを、ＤＢ制約で防げないことを表しています。

|端末ID|機種ID　|端末名称|IPアドレス|明るさ|音量|
|------|------|--------|----------|------|----|
|3|機種Ｂ|　b1 　　| x.x.x.3 | **3**　 |5 |

・更新時も煩雑なイメージ

**・画面出力イメージ**で、入力可能なすべてのデータを変更した場合、
案１ではUpdate文が10必要ですが、案2では5で済みます。
案１では発行するUpdate文が多くなってしまうことを煩雑と記載しました。

----------------------------------------------------

[ahidaka]

追加説明と更新、ありがとうございます。この修正により、当初想定していたのとは条件が変わってしまいました。また、追加で増えた条件の優先順位とその背景が良くわからないので、以下に一般的と思われること、感じたことをコメントします。

- RDBではこのような構造(Key-Value型いうんでしょうか)でテーブルを設計するのが普通です。
- RDBでは通常、テーブルを設計時には性能の事はあまり考えません。それよりも正規化を優先して考えます。なぜなら、テーブルを設計段階ではまだ不確定な要素や重複等の問題がある可能性があり、それを明らかにする必要があるからです。例えば第３/４正規形でテーブル設計したプロトタイプ等で試験運用してみて、性能的や機能に問題が発生する様であれば、そこで初めて性能対策やデータの冗長化などを検討する様な方法が一般的です。

なお案１ではまさか、増減する可能性がある（と私が思い込んでいた）機種別のテーブルを持つことは想定していなかったので、Unionの多用や更新時の煩雑さが理解できませんでした。RDBではメンテナンスコストがかかるテーブルの追加変更を避けるので、この場合の機種の様に、グループ要素である機種別のテーブルを持つ事はあまりしませんので。

案２の場合、特にRDBにする必要は無いように思います。更新時異状とは、データの論理的な整合性が無くなることだったのですね。案２でこれを防ぐためには、確かに専用の仕組みを作る必要があります。

条件の理解が悪く、良い提案が出来なくて申し訳ありません。