【データベース】素数を使った中間テーブルの削減について

こんにちは最近いろいろあって落ち込んでいるんですが、久々に更新しようと思って記事つくります。

今回はデータベースを使うときに多対多の関係性があるときに、普通は中間テーブルを作るとも思います。しかし、一方の数が少ないときにわざわざ中間テーブルつくるのめんどいなーって思っていたことがありました。

そこで素数を使ってその組み合わせで管理すれば中間テーブル省けるじゃんって思ったのがきっかけです。

まあ実用性はあまりないかもしれませんが、メモがてか記事にしたいと思います。

多対多の中間テーブル
素数を使った中間テーブルの削減
実装
まとめ

多対多の中間テーブル

例えば、ユーザー情報（ユーザー名や年齢、メールアドレスなど）がまとめられているユーザーテーブルがあったとします。そこに趣味の列を増やしたいとします。趣味はいろいろあると思います（音楽、ゲーム、スポーツ）。さらに1ユーザーに対して趣味はいくつあるかわかりません。

こういった場合は以下の図のようにユーザーテーブルとは別に趣味テーブルを作成して、その間に中間テーブルとして、ユーザー-趣味テーブルを作成することで、ユーザーと趣味の関係を表します。

このようにすれば、ユーザーが増えても、趣味が増えてもユーザー-趣味テーブルをみることで、どのユーザーがどの趣味を持っているかがわかります。

素数を使った中間テーブルの削減

普通は上記のようにすると思いますが、なんか中間テーブル作るのめんどくさいなーって思って考えたのが次の方法です。

先に以下に図を示します。

趣味テーブルに項目ごとに素数を割りあてます。そして、ユーザーテーブルのhobby列に当てはまる素数の掛け算の計算結果を保存するといったものです。

素数は1かその数でしか割りきれないので、ユーザーテーブルから趣味テーブルを参照するときには、各素数で割ってみて、割り切れた素数に該当する趣味がそのユーザーの趣味ということになります。例えば以下のようになります。

ただ、注意点としては大量の素数の掛け算を行うと結果が以下のように莫大になってしまいます。

素数	選択肢数	掛け算の最大値の計算結果
2	1	2
3	2	6
5	3	30
7	4	210
11	5	2310
13	6	30030
17	7	510510
19	8	9699690
23	9	223092870
29	10	6469693230
31	11	200560490130
37	12	7420738134810
41	13	304250263527210
43	14	13082761331670030
47	15	614889782588491410
53	16	3.258915847719E+19

MySQLだとINTの符号なしの最大値が4294967295なので、選択肢の上限は9、BIGINTの符号なしの場合でも、最大値が18446744073709551615なので、選択肢の上限は15個がMAXです。

なので正直あんまり選択肢が増える場合はそもそも使えないですね。

実装

ローカルの環境でXAMPPを使って、MySQLとPHPとHTMLで簡易なページを作成したので、紹介したいと思います。

ユーザーテーブルは以下の感じで作りました。ユーザーIDと素数の掛け算結果を保存するprime列だけです。趣味テーブルの方はめんどくさかったので今回はユーザーテーブルだけ作って、趣味は配列としてPHPで管理することにしました。

ページは以下のような感じです。

趣味を選択して右側の送信ボタンをクリックすると、ユーザーテーブルに素数の計算結果が保存されます。簡単のためユーザーIDは自動でAutoIncrementで割り当てられるようにしています。

上記のように温泉とボードゲームを選択すると、それぞれの素数は5と7なので、かけた結果の35がPOST送信されて、prime列に保存されます。以下のような感じです。以下の例ではユーザーID9に35と保存されています。

ユーザーテーブルから趣味を抽出するにはユーザーIDのInput要素からユーザーIDを入力して送信ボタンをクリックします。これにはGET送信を使っています。そしてユーザーテーブルから該当するユーザーIDがあれば、そのprime列の値が抽出されます。そしてその値を各素数で割り算することで、該当する趣味を検索します。

以下の例は、さきほど保存したユーザーID9を入力し送信したものです。左上に温泉とボードゲームが表示されていることがわかります。

以下に全コードをのせておきますね。需要ないか。。。

<?php
// MySQL データベースの接続文字列
$dsn = 'mysql:dbname=prime_number;host=localhost';
$user = '○○○○'; // ユーザーID
$password = '○○○○'; // パスワード

// 素数と趣味の組み合わせの配列(趣味テーブルの代わり)
$primeNumbers = ["音楽"=>2,"料理"=>3,"温泉"=>5,"ボードゲーム"=>7,"テレビゲーム"=>11,"スポーツ"=>13,"映画鑑賞"=>17,"漫画"=>19];

// POSTで呼び出されたとき
if(!empty($_POST)){
    $data = 1;
    foreach($_POST as $val){
        $data *= intval($val);
    }
    var_dump($data);
    // データベースに保存
    try {
        $dbh = new PDO($dsn, $user, $password);
        $stmt = $dbh->prepare("INSERT INTO test01 (prime) VALUES ($data)");
        $stmt->execute();
    } catch (PDOException $e) {
        //echo "接続失敗: " . $e->getMessage() . "\n";
        exit();
    }
}

// GETで呼び出されたとき
if(!empty($_GET['user_id'])){
    $user_id =$_GET['user_id'];
    // データベースからuser_idで検索
    try {
        $dbh = new PDO($dsn, $user, $password);
        $stmt = $dbh->prepare("SELECT prime FROM test01 WHERE user_id = $user_id");
        $stmt->execute();
        $prime = $stmt->fetch(PDO::FETCH_COLUMN);
    } catch (PDOException $e) {
        //echo "接続失敗: " . $e->getMessage() . "\n";
        exit();
    }
    // レコードがあった場合
    if( $prime ){
        // 各素数でループ
        foreach($primeNumbers as $key => $primeNumber){
            // 各素数で割ったあまりが0だった場合は配列に趣味を格納
            if($prime % $primeNumber == 0){
                $list[] = $key;
            }
        }
        // 結果の表示
        echo implode(", ", $list);
    }
}
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<main class="px-3">
<div class="container">
<div class="mb-3">
<form action="/test01/index.php" method="post">
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox01" value="2">
    <label class="form-check-label"><?php echo array_search(2,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox02" value="3">
    <label class="form-check-label"><?php echo array_search(3,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox03" value="5">
    <label class="form-check-label"><?php echo array_search(5,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox04" value="7">
    <label class="form-check-label"><?php echo array_search(7,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox05" value="11">
    <label class="form-check-label"><?php echo array_search(11,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox06" value="13">
    <label class="form-check-label"><?php echo array_search(13,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox07" value="17">
    <label class="form-check-label"><?php echo array_search(17,$primeNumbers); ?></label>
    </div>
    <div class="form-check form-check-inline">
    <input class="form-check-input" type="checkbox" name="checkbox08" value="19">
    <label class="form-check-label"><?php echo array_search(19,$primeNumbers); ?></label>
    </div>
    <input type="submit" value="送信">
</form>
</div>
<div class="mb-3">
<form action="/test01/index.php" method="get">
    <div class="mb-3">
        <label class="form-label">ユーザーID</label>
        <input type="number" class="form-control" name="user_id">
    </div>
    <input type="submit" value="送信">
</form>
</div>
</div>
</main>
</body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

<?php

// MySQL データベースの接続文字列

$dsn = 'mysql:dbname=prime_number;host=localhost';

$user = '○○○○'; // ユーザーID

$password = '○○○○'; // パスワード

// 素数と趣味の組み合わせの配列(趣味テーブルの代わり)

$primeNumbers = ["音楽"=>2,"料理"=>3,"温泉"=>5,"ボードゲーム"=>7,"テレビゲーム"=>11,"スポーツ"=>13,"映画鑑賞"=>17,"漫画"=>19];

// POSTで呼び出されたとき

if(!empty($_POST)){

$data = 1;

foreach($_POST as $val){

$data *= intval($val);

}

var_dump($data);

// データベースに保存

try {

$dbh = new PDO($dsn, $user, $password);

$stmt = $dbh->prepare("INSERT INTO test01 (prime) VALUES ($data)");

$stmt->execute();

} catch (PDOException $e) {

//echo "接続失敗: " . $e->getMessage() . "\n";

exit();

}

// GETで呼び出されたとき

if(!empty($_GET['user_id'])){

$user_id =$_GET['user_id'];

// データベースからuser_idで検索

try {

$dbh = new PDO($dsn, $user, $password);

$stmt = $dbh->prepare("SELECT prime FROM test01 WHERE user_id = $user_id");

$stmt->execute();

$prime = $stmt->fetch(PDO::FETCH_COLUMN);

} catch (PDOException $e) {

//echo "接続失敗: " . $e->getMessage() . "\n";

exit();

}

// レコードがあった場合

if( $prime ){

// 各素数でループ

foreach($primeNumbers as $key => $primeNumber){

// 各素数で割ったあまりが0だった場合は配列に趣味を格納

if($prime % $primeNumber == 0){

$list[] = $key;

}

// 結果の表示

echo implode(", ", $list);

}

<!DOCTYPE html>

<head>

</head>

<body>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

</form>

</div>

<label class="form-label">ユーザーID</label>

</div>

</form>

</div>

</main>

</body>

</html>

まとめ

使い道があるかわかりませんが、素数で中間テーブルを削減する方法を考えてみました。

もっと効率いい方法があるかもしれませんが、使える選択肢の数も限られてますし、まあどうでもいいですかね。

【データベース】素数を使った中間テーブルの削減について

多対多の中間テーブル

素数を使った中間テーブルの削減

実装

まとめ

参考サイト

コメント