質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.96%

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

受付中

回答 3

投稿

  • 評価
  • クリップ 1
  • VIEW 3,548
社内の資産管理台帳を作りたいと考えています。

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

以下の3点が前提条件としてあります。
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が入ります

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+1

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

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

ライセンスタイプBの「ユーザー1人につき2台まで」という条件が少しややこしいので、
これを整理して、
  1. 「AタイプのライセンスはPCごとにライセンスされる」
  2. 「Bタイプのライセンスはユーザーごとにライセンスされる」
  3. 「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側でチェックするのであれば、トリガーでチェックするような形になるかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/11/16 10:37 編集

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

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

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

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

    キャンセル

  • 2015/11/16 13:27

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/11/16 11:45

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

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

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

    キャンセル

0

ER
ソフトウェア識別番号 | ライセンス番号  | ライセンス連番 | ライセンスタイプ  | 
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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/11/16 11:39

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.96%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる