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

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

ただいまの
回答率

89.20%

online card gameにおいてserver側が各clientの所持cardをSQLiteのdatabaseで管理したい時のdatabaseの設計

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 264

gottadiveintopy

score 474

online card gameを作ろうとしているのですが、題の通りdatabaseの設計で悩んでいます。

課題

例えば太郎,花子という二人のclientと.,という三種類のcardがあったとして、二人が各cardを何枚もっているか以下のように管理したとします。

import sqlite3
from textwrap import dedent as dd
from contextlib import closing


with closing(sqlite3.connect(':memory:')) as conn:
    cur = conn.cursor()
    cur.executescript(dd('''
        CREATE TABLE card (
            id TEXT,
            name TEXT,
            PRIMARY KEY (id)
        );
        CREATE TABLE user (
            name TEXT,
            cards_owned TEXT,
            PRIMARY KEY (name)
        );
        INSERT INTO card VALUES ('tiger', '虎');
        INSERT INTO card VALUES ('wolf', '狼');
        INSERT INTO card VALUES ('sheep', '羊');
        INSERT INTO user VALUES ('太郎', 'tiger 0, wolf 2, sheep 1');
        INSERT INTO user VALUES ('花子', 'tiger 3, wolf 1, sheep 1');
        '''))
    for name, cards_owned in cur.execute('SELECT * FROM user'):
        print(f"{name}の所持カード")
        for v in cards_owned.split(', '):
            card_id, n = v.split()
            for card_name, in conn.cursor().execute(f'SELECT name FROM card WHERE id = "{card_id}"'):
                print(f"{card_name}: {n}枚")
# 標準出力
太郎の所持カード
虎: 0枚
狼: 2枚
羊: 1枚
花子の所持カード
虎: 3枚
狼: 1枚
羊: 1

この設計は良いとは思いません。理由はtiger 0, wolf 2, sheep 1を自力でparseしているからです。せっかくdatabaseを使っているのにこのような事を自分でするのは無駄に思えます。後このような文字列にFOREIGN KEYを持たせる方法があるのかどうかも分かりません。

なので代わりに思いついたのが以下のようにclientの数だけ所持card用のtableを作る方法なのですが

import sqlite3
from textwrap import dedent as dd
from contextlib import closing


with closing(sqlite3.connect(':memory:')) as conn:
    cur = conn.cursor()
    cur.executescript(dd('''
        CREATE TABLE card (
            id TEXT,
            name TEXT,
            PRIMARY KEY (id)
        );
        CREATE TABLE user (
            name TEXT,
            PRIMARY KEY (name)
        );
        INSERT INTO card VALUES ('tiger', '虎');
        INSERT INTO card VALUES ('wolf', '狼');
        INSERT INTO card VALUES ('sheep', '羊');
        INSERT INTO user VALUES ('太郎');
        INSERT INTO user VALUES ('花子');

        CREATE TABLE 太郎の所持card (
            card_id TEXT,
            n INT,
            FOREIGN KEY (card_id) REFERENCES card(id)
        );
        INSERT INTO 太郎の所持card VALUES ('tiger', 3);
        INSERT INTO 太郎の所持card VALUES ('wolf', 2);
        INSERT INTO 太郎の所持card VALUES ('sheep', 1);
        CREATE TABLE 花子の所持card (
            card_id TEXT,
            n INT,
            FOREIGN KEY (card_id) REFERENCES card(id)
        );
        INSERT INTO 花子の所持card VALUES ('tiger', 0);
        INSERT INTO 花子の所持card VALUES ('wolf', 3);
        INSERT INTO 花子の所持card VALUES ('sheep', 2);
        '''))
    for name, in cur.execute('SELECT * FROM user'):
        print(f"{name}の所持カード")
        for card_id, n in conn.cursor().execute(f'SELECT * FROM {name}の所持card'):
            for card_name, in conn.cursor().execute(f'SELECT name FROM card WHERE id = "{card_id}"'):
                print(f"{card_name}: {n}枚")

このような設計がそもそも良いのかどうかが分かりません。何かより良い方法を知っている方 教えていただけたら嬉しいですm(_ _)m。

環境

  • Python: 3.7.1
  • OS: LinuxMint 18.2 (Ubuntu 16.04 LTS派生)

追記

cardのPRIMARY KEYを序数にする事で一つ目のコードの'tiger 0, wolf 2, sheep 1'021に置き換える事ができました。こちらの方が一つ目の物より優れていると思うので追記します。

import sqlite3
from textwrap import dedent as dd
from contextlib import closing


with closing(sqlite3.connect(':memory:')) as conn:
    cur = conn.cursor()
    cur.executescript(dd('''
        CREATE TABLE card (
            id INTEGER,
            name TEXT,
            PRIMARY KEY (id)
        );
        CREATE TABLE user (
            name TEXT,
            cards_owned TEXT,
            PRIMARY KEY (name)
        );
        INSERT INTO card (name) VALUES ('虎');
        INSERT INTO card (name) VALUES ('狼');
        INSERT INTO card (name) VALUES ('羊');
        INSERT INTO user VALUES ('太郎', '021');
        INSERT INTO user VALUES ('花子', '311');
        '''))
    for row in cur.execute('SELECT * FROM card'):
        print(row)
    for name, cards_owned in cur.execute('SELECT * FROM user'):
        print(f"{name}の所持カード")
        for id, name in conn.cursor().execute('SELECT * FROM card'):
            print(f"{name}: {cards_owned[id - 1]}枚")
(1, '虎')
(2, '狼')
(3, '羊')
太郎の所持カード
虎: 0枚
狼: 2枚
羊: 1枚
花子の所持カード
虎: 3枚
狼: 1枚
羊: 1

追記2

説明が不十分な気がするので追記します。私が作ろうとしているのは対戦型のcard gameで、あらかじめ各playerがデッキを組んでから対戦します。私が使っている所持カードという言葉はデッキを組む際の元となる物であって、対戦中に変動することはありません。ゲーム内の店でカードを買うなどした時に初めて変動する類の物です。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 1

checkベストアンサー

+1

カードの種類が増えたら大変更が必要です。

SQLをシンプルにパフォーマンス良く実行できるように、[データベースのテーブルはデータベースの正規化 に沿って設計します。通常、第3正規化まで行います。この質問では第1正規化で繰り返しを排除します。

追記

Markdown を使いたいのでこちらに追記します。

オオカミは全部で何匹でしょうか?
現行テーブルと以下に提示したテーブルでSQLを追記してください。
最初に書いたように動物が増えた時のSQLでの対応方法も現行と提案部分で各々追記してください。
SUM ... GROUP BY を使って各動物毎の合計数を取得するSQLを提示してください。

正規化とは維持を容易に、しかもパフォーマンスが良いシステムを作る為に必要不可欠な手法です。
繰り返しの排除を理解できないようなのでCREATE TABLEを提示します。

CREATE TABLE card (
    card_id    INTEGER,
    card_name  TEXT,
    PRIMARY KEY (card_id)
);

CREATE TABLE user (
    user_id    INTEGER,
    user_name  TEXT,
    PRIMARY KEY (user_id)
);

CREATE TABLE card_user (
    card_id    INTEGER,
    user_id    INTEGER,
    toosu      INTEGER,  -- 頭数
    PRIMARY KEY (card_id, user_id)
);


現行ではSUBSTR の多用が必要です。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/09/07 00:24

    もし良かったらなんですが、書いたSQLに改良の余地が無いか教えてもらえませんか?

    キャンセル

  • 2019/09/07 05:50

    primary key は数値が良いって書いたのは AUTOINCREMENT を使うことが多い。 https://www.dbonline.jp/sqlite/table/index9.html
    トリガーの中の SELECT MAX(card_id) FROM card を使わないで AUTOINCREMENT の最大値を取得する方法がSQLiteにあったか思い出せません。
    こんなところで。

    キャンセル

  • 2019/09/07 10:50

    link先の情報によるとあるみたいですね。
    色々と教えてくれてありがとうございました。

    キャンセル

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

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