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

Question

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がデッキを組んでから対戦します。私が使っている所持カードという言葉はデッキを組む際の元となる物であって、対戦中に変動することはありません。ゲーム内の店でカードを買うなどした時に初めて変動する類の物です。

Answer 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 の多用が必要です。

Comments

[gottadiveintopy]

回答ありがとうございます。すいません、どっちのコードのどこの繰り返しを除けば良いのか教えてもらえないでしょうか？例えば一つ目のコードの`tiger 0, wolf 2, sheep 1`という文字列ですが、tiger,wolf,sheepの３つの識別子はcardのPRIMARY KEYであり、どのcardであるかを示す唯一の手段なので取り除けないと思うのですが、間違ってますでしょうか？それとも全然別の箇所でしょうか？

[gottadiveintopy]

今思ったのですが、cardのPRIMARY KEYを'tiger'のような名前ではなく0から始まる序数にしてしまえば
`tiger 0, wolf 2, sheep 1`は'021'に置き換えられますね。

[Orlofsky]

IDは一意であれば何でも構わないけど、数字項目にするのが一般的です。
動物の数が10桁になったり、動物の種類が増えた時の変更方法は考えていますか？
繰り返しの排除で提示したURLを理解できるかと。
眠いから続きがあれば明日以降。

[gottadiveintopy]

動物の数は必ず一桁になる予定ですのでこの方法で大丈夫だと思っています。

動物の種類が増えた時なんですが、少し困っています。全userのcards_ownedの末尾に'0'を加えれば良いと思うのですが、このような自己代入をどうSQLで行うのかが分かっていません。ヒントをもらえませんか？

[gottadiveintopy]

ありがとうございます。なるほどそのようにdatabaseを設計するのが良いんですね。出題されたQuizなんですが、解くのに時間がかかりそうなのでしばらく待ってもらえると助かります。

[gottadiveintopy]

できました。
https://gist.github.com/gottadiveintopython/34a012581acb9b83f96549ef78a8ce0b

[Orlofsky]

良かったですね。
ユーザーや動物が増えてデータ量が多くなってSQLiteでは負荷の限界が来そうなら最初からMariaDB(MySQLのフォーク)を使っておいた良いです。

[gottadiveintopy]

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

[Orlofsky]

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

[gottadiveintopy]

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