MySQLにおける多対多の集計方法について

Question

社内の資産管理台帳を作りたいと考えています。

管理対象は、社員が利用しているPCと、各PCにインストールしているソフトです。

以下の３点が前提条件としてあります。
1.一人の社員が複数のPCを使用する場合がある
2.ソフトはライセンスの種別により利用可能対象が異なる
・タイプA：PC1台につき1ライセンス
・タイプB：ユーザー1人につき2台まで
3.各ソフトウェアごと利用状況（使用中/在庫）を集計したい

多対多の関係になるため、連結用のテーブルを使うことを考えましたが、
どのようなSQLを書けば3の集計ができるのかわかりませんでした。

そもそも設計として正しいのか、SQLはどのようになるのかをご教示いただければと思います。

[ユーザー]
│
│
[連結用]────[パソコン]
│
│
[ソフトウェア]

---

[ユーザー]
ID 社員
1 田中 一郎
2 山田 太郎
3 太田 光博
4 佐川 和夫
5 太田 明日香
6 篠原 沙希
7 朝霞 寛子

[パソコン]
ID PC識別番号
1 H001
2 H002
3 H003
4 H004
5 H005
6 H006

[ソフトウェア]
ID ソフト名称 タイプ 購入数
1 Office2010 Professional A 5
2 Office2010 Personal B 5
3 Office2013 Professional B 5
4 Office365 B 20
5 Illustrator CS4 A 4
6 Photoshop CS4 A 4
7 Dreamweaver CS4 A 4
8 InDesign CS4 B 3
9 Illustrator CS5 B 6
10 Photoshop CS5 B 6
11 Dreamweaver CS5 B 6
12 InDesign CS5 B 2
13 CreativeCloud B 2
14 Acrobat A 30

[連結用]
社員 PC ソフト
田中 一郎 H001 Office2010 Professional
田中 一郎 H001 Acrobat
山田 太郎 H003 Acrobat
山田 太郎 H003 CreativeCloud
佐川 和夫 H004 Acrobat
佐川 和夫 H004 Illustrator CS5
佐川 和夫 H004 Photoshop CS5
佐川 和夫 H004 Dreamweaver CS5
篠原 沙希 H002 Office2013 Professional
篠原 沙希 H002 Acrobat
田中 一郎 H006 Office2013 Professional
田中 一郎 H006 Acrobat
朝霞 寛子 H003 Illustrator CS4
朝霞 寛子 H003 Photoshop CS4
朝霞 寛子 H003 Dreamweaver CS4

※実際には各IDが入ります

Answer 1

多対多の関連を表すのに関連テーブルを使うのは定石ではありますが、関連テーブルの設計に少し問題があるように思います。

以下の仕様が実際の業務にマッチするかはわかりませんが、提案させていただきます。

ライセンスタイプBの「ユーザー1人につき2台まで」という条件が少しややこしいので、
これを整理して、
0.「AタイプのライセンスはPCごとにライセンスされる」
0.「Bタイプのライセンスはユーザーごとにライセンスされる」
0.「Bタイプのライセンスは、1ユーザーにつき、PC2台までインストールできる」
という3つの条件として区別して考えるというのはいかがでしょうか。

そして、ライセンス数の在庫に影響するのは1と2であり、3の条件が守られているかどうかを別の問題として考えるとすると、在庫数に関しては以下のように管理できるかと思います。

【テーブル構成】

/* ユーザー */
CREATE TABLE USER(
	ID INT NOT NULL PRIMARY KEY,
	NAME VARCHAR(24)
);

/* パソコン */
CREATE TABLE PC(
	ID INT NOT NULL PRIMARY KEY,
	SERIAL VARCHAR(4) NOT NULL
);

/* ソフトウェア */
CREATE TABLE SOFTWARE(
	ID INT NOT NULL PRIMARY KEY,
	NAME VARCHAR(64),
	TYPE VARCHAR(1),
	CNT INT
);

/* ライセンスタイプ：A */
/* どのPCにどのソフトウェアがインストールされているのか */
CREATE TABLE R_PC_SOFT(
	PID INT NOT NULL,		/* PC.ID */
	SID INT NOT NULL,		/* SID.ID */
	PRIMARY KEY(PID, SID)
);

/* ライセンスタイプ：B */
/* どのユーザーがどのPCにどのソフトをインストールして使用しているのか */
CREATE TABLE R_USER_SOFT(
	UID INT NOT NULL,		/* USER.ID */
	PID INT NOT NULL,		/* PC.ID */
	SID INT NOT NULL,		/* SOFTWARE.ID */
	PRIMARY KEY(UID, PID, SID)
);

（FKは省略しましたが、実際には付けた方が良いと思います）

【テストデータ】

INSERT INTO USER VALUES(1, '田中 一郎');
INSERT INTO USER VALUES(2, '山田 太郎');
INSERT INTO USER VALUES(3, '太田 光博');
INSERT INTO USER VALUES(4, '佐川 和夫');
INSERT INTO USER VALUES(5, '太田 明日香');
INSERT INTO USER VALUES(6, '篠原 沙希');
INSERT INTO USER VALUES(7, '朝霞 寛子');

INSERT INTO PC VALUES(1, 'H001');
INSERT INTO PC VALUES(2, 'H002');
INSERT INTO PC VALUES(3, 'H003');
INSERT INTO PC VALUES(4, 'H004');
INSERT INTO PC VALUES(5, 'H005');
INSERT INTO PC VALUES(6, 'H006');

INSERT INTO SOFTWARE VALUES(1, 'Office2010 Professional', 'A', 5); 
INSERT INTO SOFTWARE VALUES(2, 'Office2010 Personal', 'B', 5); 
INSERT INTO SOFTWARE VALUES(3, 'Office2013 Professional', 'B', 5); 
INSERT INTO SOFTWARE VALUES(4, 'Office365', 'B', 20); 
INSERT INTO SOFTWARE VALUES(5, 'Illustrator CS4', 'A', 4); 
INSERT INTO SOFTWARE VALUES(6, 'Photoshop CS4', 'A', 4); 
INSERT INTO SOFTWARE VALUES(7, 'Dreamweaver CS4', 'A', 4); 
INSERT INTO SOFTWARE VALUES(8, 'InDesign CS4', 'B', 3);
INSERT INTO SOFTWARE VALUES(9, 'Illustrator CS5', 'B', 6); 
INSERT INTO SOFTWARE VALUES(10, 'Photoshop CS5', 'B', 6); 
INSERT INTO SOFTWARE VALUES(11, 'Dreamweaver CS5', 'B', 6); 
INSERT INTO SOFTWARE VALUES(12, 'InDesign CS5', 'B', 2); 
INSERT INTO SOFTWARE VALUES(13, 'CreativeCloud', 'B', 2); 
INSERT INTO SOFTWARE VALUES(14, 'Acrobat', 'A', 30);

INSERT INTO R_PC_SOFT VALUES(1, 1);
INSERT INTO R_PC_SOFT VALUES(1, 14);
INSERT INTO R_PC_SOFT VALUES(3, 14);
INSERT INTO R_PC_SOFT VALUES(4, 14);
INSERT INTO R_PC_SOFT VALUES(2, 14);
INSERT INTO R_PC_SOFT VALUES(6, 14);
INSERT INTO R_PC_SOFT VALUES(3, 5);
INSERT INTO R_PC_SOFT VALUES(3, 6);
INSERT INTO R_PC_SOFT VALUES(3, 7);

INSERT INTO R_USER_SOFT VALUES(2, 3, 13);
INSERT INTO R_USER_SOFT VALUES(4, 4, 9);
INSERT INTO R_USER_SOFT VALUES(4, 4, 10);
INSERT INTO R_USER_SOFT VALUES(4, 4, 11);
INSERT INTO R_USER_SOFT VALUES(6, 2, 3);
INSERT INTO R_USER_SOFT VALUES(6, 2, 14);
INSERT INTO R_USER_SOFT VALUES(1, 6, 3);
INSERT INTO R_USER_SOFT VALUES(1, 6, 14);

【ライセンス在庫数を確認するSQLサンプル】

select 
	s.id as "ソフトウェアID", 
	s.name as "名称", 
	s.type as "タイプ", 
	s.cnt as "購入数", 
	ifnull(u.used, 0) as "使用数", 
	ifnull(s.cnt - u.used, 0) as "在庫" 
from software as s
	left outer join (select sid, count(sid) as used from r_pc_soft group by sid) as u 
	on s.id = u.sid 
where s.type = 'A'
union
select 
	s.id as "ソフトウェアID", 
	s.name as "名称", 
	s.type as "タイプ", 
	s.cnt as "購入数", 
	ifnull(u.used, 0) as "使用数", 
	ifnull(s.cnt - u.used, 0) as "在庫" 
from software as s
	left outer join (select sid, count(sid) as used from r_user_soft group by sid) as u
	on s.id = u.sid
where type = 'B'
order by 1;

【問い合わせ結果】

+----------------------+-------------------------+-----------+-----------+-----------+--------+
| ソフトウェアID       | 名称                    | タイプ    | 購入数    | 使用数    | 在庫   |
+----------------------+-------------------------+-----------+-----------+-----------+--------+
|                    1 | Office2010 Professional | A         |         5 |         1 |      4 |
|                    2 | Office2010 Personal     | B         |         5 |         0 |      0 |
|                    3 | Office2013 Professional | B         |         5 |         2 |      3 |
|                    4 | Office365               | B         |        20 |         0 |      0 |
|                    5 | Illustrator CS4         | A         |         4 |         1 |      3 |
|                    6 | Photoshop CS4           | A         |         4 |         1 |      3 |
|                    7 | Dreamweaver CS4         | A         |         4 |         1 |      3 |
|                    8 | InDesign CS4            | B         |         3 |         0 |      0 |
|                    9 | Illustrator CS5         | B         |         6 |         1 |      5 |
|                   10 | Photoshop CS5           | B         |         6 |         1 |      5 |
|                   11 | Dreamweaver CS5         | B         |         6 |         1 |      5 |
|                   12 | InDesign CS5            | B         |         2 |         0 |      0 |
|                   13 | CreativeCloud           | B         |         2 |         1 |      1 |
|                   14 | Acrobat                 | A         |        30 |         5 |     25 |
+----------------------+-------------------------+-----------+-----------+-----------+--------+

おそらく、やりたいことはこんな感じなのではと推測したのですが、いかがでしょうか。

ライセンスタイプBの「ユーザー1人につき2台まで」の条件が満たされているかどうかはアプリ側で制御する前提で考えましたが、それをDB側でチェックするのであれば、トリガーでチェックするような形になるかと思います。

Comments

[smiley-_-smiley]

返信が遅くなりまして申し訳ありません。
やりたいこととしてはまさにその通りです。

ライセンスの種別ごとに別々の関連テーブルを設けるということですね。
非常にわかりやすいご説明ありがとうございます。

ただ、どうしても理解ができなかったのが、R_USER_SOFTにライセンスタイプＡである、Acrobatが含まれている点です。

SQLの内容としては、タイプ別の関連テーブルごとの集計結果を合算しているという認識ですが、どのような意図があるのか、よく考えてみます。

[pizza]

ご指摘の通り、AcrobatはライセンスタイプAなので、R_USER_SOFTに登録すべきではありませんね。これはデータの登録ミスです。失礼しました。

Answer 2

ソフトウェア識別番号 | ライセンス番号  | ライセンス連番 | ライセンスタイプ  | 
Microsoft-001-2010-P | 123-456-789-012 |              1 |                A  | 
Microsoft-001-2010-P | 123-456-789-013 |              1 |                A  | 
Microsoft-001-2010-P | 123-456-789-014 |              1 |                A  | 
Microsoft-001-2010-P | 123-456-789-015 |              1 |                A  | 
Microsoft-001-2010-P | 123-456-789-016 |              1 |                A  | 
Microsoft-002-365    | 123-456-789-017 |              1 |                A  | 
Microsoft-002-365    | 123-456-789-017 |              2 |                A  | 
Microsoft-002-365    | 123-456-789-017 |              3 |                A  | 
Microsoft-002-365    | 123-456-789-017 |              4 |                A  | 
Microsoft-002-365    | 123-456-789-017 |              5 |                A  | 

PC識別番号
PC0123456
PC0123457
PC0123458
PC0123459
PC0123460

ソフトウェア利用状況
PC識別番号 | ソフトウェア識別番号 | ライセンス番号  | ライセンス連番 
PC0123456  | Microsoft-001-2010-P | 123-456-789-012 |              1
PC0123456  | Microsoft-002-365    | 123-456-789-017 |              1
PC0123457  | Microsoft-001-2010-P | 123-456-789-012 |              1 
PC0123458  | Microsoft-002-365    | 123-456-789-017 |              2 
PC0123459  | Microsoft-002-365    | 123-456-789-017 |              3 

ユーザーテーブル
社員ID   | 社員名
00000001 | 山田太郎
00000002 | 佐藤二郎
00000003 | 鈴木三郎

パソコン利用状況テーブル
社員ID   |PC識別番号
00000001 |PC0123456
00000002 |PC0123457
00000001 |PC0123458

select ソフトウェア.ソフトウェア識別番号, ソフトウェア利用状況.PC識別番号, 社員ID from ソフトウェア
left outer join ソフトウェア利用状況 on ソフトウェア.ソフトウェア識別番号 = ソフトウェア利用状況.ソフトウェア識別番号
left outer join パソコン利用状況テーブル on ソフトウェア利用状況.PC識別番号 = パソコン利用状況テーブル.PC識別番号
left outer join ユーザーテーブル on パソコン利用状況テーブル.社員ID = ユーザーテーブル.社員ID

すみません、結構適当なので他の方のブラッシュアップを期待します。

Comments

[smiley-_-smiley]

ありがとうございます。
ちょっと理解しかねる点がありますが、今実際にDBを触れる環境にないため、確認をしたうえであらためてご質問させていただくかと思います。

Answer 3

正規化の入門レベルの内容ですが、

1.一人の社員が複数のPCを使用する場合がある

ある社員がどのパソコンを使っているか、複数の社員が同じパソコンを使っている場合のライセンスのカウントを表現する情報(テーブルひとつ追加で済むか？)が必要です。
現状用意できる CREATE TABLE, INSERT文も提示できるとコメントも付きやすいでしょう。
PRIMARY KEYに相当する列がIDですと誤解し易いので、列名ひとつとっても分かり易いネーミングを考えてください。

Comments

[smiley-_-smiley]

初歩的な質問で申し訳ありません。
>ある社員がどのパソコンを使っているか、複数の社員が同じパソコンを使っている
>場合のライセンスのカウントを表現する情報(テーブルひとつ追加で済むか？)が
>必要です。

関連テーブルが必要だと考えたのですが、複数の意味を持つ関連テーブルになってしまうと考えたため、わからなくなってしまいました。

考え方をまとめている状態での質問だったため、具体的なテーブル構造まで作成しておりません。
皆様からいただいたアドバイスからあらためて考えてみます。