😊

中間テーブルとは(*ハンズオン付き)

2022/12/03に公開約3,900字

概要

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

テーブル間の関係

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

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

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

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高
...

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

中間テーブルの使い方

中間テーブルでは、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()

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

参考文献

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

Discussion

ログインするとコメントできます