Zenn
😊

中間テーブルとは #1

2022/12/03に公開
Flask
Database
table
中間テーブル
foreignkey
tech

概要

中間テーブルとは、テーブル同士の関係が多対多の時に、２つのテーブルの中間で使われるテーブルのことである。この説明だけだと、意味不明なので以下に実例も交えながら説明する。

テーブル間の関係

テーブル間の関係の種類は３つ存在する。
まず１対１の関係である。例えば、人と所有するパスポートの関係など。下記の通り、DBで表現できる。「人」を扱うテーブルと「Kenのパスポート」を扱うテーブルがあれば、ケンは一つのパスポートしか保有できない（＊二重国籍は便宜上無視する。）ので、１対１の関係となる。

id person
1 Ken
2 Emily
...
id passport
1 Japan
...

次に、１対多の関係である。例えば、人と所有する車の数など。上記の「人」を扱うテーブルとは別に、一人の人は複数の車を所有できるので、１対多の関係となる。

id Cars
1 Nissan
2 Toyota
3 Mazda
...

最後に、多対多の関係である。例えば、人と学歴の関係が挙げられる。小学校、中学校、高校、大学など、人は複数の学歴を持つことが可能である。一方、学歴も同様に、Aさん、Bさん、Cさんと言ったように、複数の人を持つことが可能である。これが多対多の関係となり、中間テーブルを使う必要があるのだが、まず「なぜ使う必要があるのか、使わないとどう言った問題が発生するか」という背景から説明する。

中間テーブルの必要性について

人と学歴の関係をテーブルで示す場合、下記のようなテーブル構成が考えられる。

id user 小学校 中学校 高校 大学
1 A A小 A小 A中 A高
2 B B小 B小 B中 B高
...

ただし、中卒、高卒、はたまた院卒や、社会人になってから再度大学へ行った人など、卒業履歴をどのように確保すべきかと、テーブル設計段階で考慮することは難しい。また、学歴１、２、３、４..と言ったように大量の項目を用意すると、空のセルが多くなってしまい、実際のデータ処理をする際にエラーの原因となる。こういった問題点を避けるため、使用するのが中間テーブルである。

中間テーブルの使い方

中間テーブルでは、userテーブル、学歴テーブルをつなげるように設計する。

id person
1 Ken
2 Emily
...
id 学歴
1 A小
2 A中
...

そして、下記の中間テーブルを作成する。こうすることで、不要な空欄を避けることができる。また、学歴に大学院や海外留学といった新たな項目が追加されても、テーブルの後ろに継ぎ足すだけで対応できる。

is userID 学歴ID
1 1(Ken) 1(A小)
2 1(Ken) 2(A中)
...

念の為、補足として説明するが、userテーブルのidは中間テーブルのuserIDと、学歴テーブルのidは中間テーブルの学歴IDと紐づいている。そのため、中間テーブルの（userID、学歴ID）に（１、１）と入力すると、KenがA小に通っていた、というデータが保存される。

リレーションごとのテーブル構造

テーブル構造が、1:1、1:n、n:nの場合を解説する。

1:1の場合

下記が、User：Profileが1:1の関係であることを示している。

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    profile = db.relationship('Profile', backref='user', uselist=False)

class Profile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Profileテーブルのuser_idとUser
テーブルのidを設定していることが確認できる。もう一つのポイントは、uselist=Falseを設定していることである。これにより、user.profileは単一のProfileオブジェクトで返すことを保証する。

1:nの場合

下記がOrder：Productが1:nの関係であることを示している。

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.String(), nullable=False)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
    order = db.relationship('Order', backref=db.backref('products', lazy=True))

こちらでは、Productにorder_idという外部キーを追加して、そこに対してOrderのidをリンクさせている。また、Productモデルにorderというリレーションプロパティを追加している。下記コードによって、OrderとProductが1:nの関係であることを示している。

order = db.relationship('Order', backref=db.backref('products', lazy=True))

より具体的に説明すると、Productテーブルのorder = db.relationship('Order', ... )で「この商品は、どの注文に属しているか」知るための関係構築している。また、backref=db.backref('products', ...)で「この注文にはどんな商品が含まれるか」を知るための関係を構築している。つまり上記コードで、ある商品がどの注文に関連しているのか、もしくはある注文がどんな商品に含まれているのか、と言ったことを調べられる。

＊ちなみにこのproductsは、任意の名前なので、abc123とかでもOK。たとえば、product = Product.query.first()とあるProductオブジェクトを取得した場合、related_orders = product.abc123として、そのproductのOrderオブジェクトを取得できる。（＊特定の商品がどれだけの注文に含まれるか、確認できる。三つの異なる注文があれば、それらの注文を参照する。）

n:nの関係

下記がOrder：Productのn：nの関係である。1:nの関係に修正を加えることで、下記コードができる。元々、order_idがあったが、既にOrderとProductの関係を中間テーブルで管理しているので、その部分は削除した。また、双方で中間テーブルの設定が必要なので、secondary=order_productsという部分を追加した。

order_products = db.Table('order_products',
    db.Column('order_id', db.Integer, db.ForeignKey('order.id'), primary_key=True),
    db.Column('product_id', db.Integer, db.ForeignKey('product.id'), primary_key=True)
)

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.String(), nullable=False)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)
    order = db.relationship('Order', secondary=order_products, backref=db.backref('products', lazy=True))

ハンズオン

では実際に、flaskを使ってハンズオン形式で中間テーブルを確認する。任意の場所にapp.pyを作成して、下記コードをコピペする。

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 下記コードは、PC環境に応じて書き換えること。exampleというdbの作成とユーザ名の変更が必要である。
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://postgres:abc@localhost:5432/example'
db = SQLAlchemy(app)

order_items = db.Table('order_items',
    db.Column('order_id', db.Integer, db.ForeignKey('order.id'), primary_key=True),
    db.Column('product_id', db.Integer, db.ForeignKey('product.id'), primary_key=True)
)

class Order(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  status = db.Column(db.String(), nullable=False)
  products = db.relationship('Product', secondary=order_items,
      backref=db.backref('orders', lazy=True))

class Product(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(), nullable=False)

こちらは、 Orderという注文テーブル、Productという商品テーブル、そしてorder_itemsという中間テーブルを作成している。レストランでの注文で考えると、注文には複数の商品が含まれており、商品もまた複数の注文に含まれている。例えばマクドナルドでも、一つの注文でシェイク、バーガー、ポテトといった複数商品が含まれるし、ポテトは複数の注文でオーダーされる。

order_itemsという中間テーブルでは、Orderの注文idとProductの商品idを参照する必要があるので、それぞれのテーブルに対して外部キーを設定している。また、Orderが親テーブルでProductが子テーブルのため、Order側ではproductsの中で子テーブルとの関係を示している。

最後に、下記の通りコマンドを実行して、中間テーブルで二つのテーブルが紐づけられていることを確認する。

# pythonへログインする
python3

# ファイルの中身をインポートする
>>> from app import db, Order, product

# テーブルを作成する
>>> db.create_all()

# Order / Productテーブルにデータを挿入する
>>> product = Product(name='Great widget')
>>> order = Order(status='ready')

# 上記で挿入したデータを紐付ける
order.products = [product]

# データをコミットする
>>> db.session.add(order)
>>> db.session.commit()

上記操作実行後、データベースに接続すると中間テーブルでデータ紐付けがされていることを確認できる。

参考文献

https://qiita.com/morikuma709/items/9fde633db9171b36a068
https://qiita.com/ramuneru/items/db43589551dd0c00fef9

Discussion