中間テーブルとは（＊ハンズオン付き）
概要
中間テーブルとは、テーブル同士の関係が多対多の時に、２つのテーブルの中間で使われるテーブルのことである。この説明だけだと、意味不明なので以下に実例も交えながら説明する。
テーブル間の関係
テーブル間の関係の種類は３つ存在する。
まず１対１の関係である。例えば、人と所有するパスポートの関係など。下記の通り、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 | user | 学歴 |
|---|---|---|
| 1 | Ken | A小 |
| 2 | Ken | A中 |
| ... |
ハンズオン
では実際に、flaskを使ってハンズオン形式で中間テーブルを確認する。flaskでは下記の通り、中間テーブルを作成する。
association_table = Table('association', Base.metadata,
Column('table1_id', Integer, ForeignKey('table1.id')),
Column('table2_id', Integer, ForeignKey('table2.id')),
)
class Parent(Base):
__tablename__ = 'table1'
id = Column(Integer, primary_key=True)
children = relationship('Child', secondary=association_table)
class Child(Base):
__tablename__ = 'table2'
id = Column(Integer, primary_key=True)
まず概要から解説すると、association_tableが中間テーブルである。そして、その中間テーブルでつなげているのが、ParentテーブルとChildテーブルである。Parentとして設定したテーブル側では、childrenを準備する必要がある。
任意の場所に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()
上記操作実行後、データベースに接続すると中間テーブルでデータ紐付けがされていることを確認できる。
参考文献
Discussion