MySQL + SQLAlchemy、リレーションについて

Question

リレーショナルデータベースの考え方について質問です。
MySQL+SQLAlchemyを使ってレシピ管理のwebアプリを作っています。登録したいデータは、「メニュー名」、「材料」、「作り方」の３項目です。例えば、カレーであれば以下の様なデータを保存します。
メニュー名：カレー
材料：”じゃがいも”、”にんじん”、”玉ねぎ”、”カレールー”
作り方：”材料を切る”、”材料を煮る”、”カレールーを入れる”
PosgtreSQLであれば、array型が使えるので、
column1="カレー"
column2=[”じゃがいも”、”にんじん”、”玉ねぎ”、”カレールー”]
column3=[”材料を切る”、”材料を煮る”、”カレールーを入れる”]
とするだけで１つのテーブルでスッキリとデータを保存することができますが、MqSQLではarray型は使えないので、お手本のコードなどを参考にしながら以下のコードを書きましたが、

①MySQLでは本当にこんなに面倒な方法で上記のレシピを管理するのが正しいでしょうか。
②arrayが使えず、リレーショナルの考え方を頼りに、こんなに単純な事の為に３つもテーブルを作るメリットが理解できません。。。arrayがサポートされない理由をご存知でしたら教えて下さい。

'''
略
'''
class Recipe(db.Model):
    __tablename__="recipe_table"
    id = db.Column(db.Integer, primary_key=True)  
    name = db.Column(db.String(40))
    zairyou = db.relationship('Zairyou', backref='recipe')
    tejun = db.relationship('Tejun', backref='recipe')

class Zairyou(db.Model):
    __tablename__="zairyou_table"
    id = db.Column(db.Integer, primary_key=True)  
    zairyou_name = db.Column(db.String(20))
    recipe_id = db.Column(db.Integer, db.ForeignKey('recipe_table.id'))

class Tejun(db.Model):
    __tablename__="tejun_table"
    id = db.Column(db.Integer, primary_key=True)  
    tejun_name = db.Column(db.String(20))
    recipe_id = db.Column(db.Integer, db.ForeignKey('recipe_table.id'))

@app.route('/')
def show_recipe():

    menu = Recipe.query.filter_by(name="カレー").first()
    materials = menu.zairyou  #backref=を使ってRecipeからSozaiへの
relationを利用し、カレーの材料をzairyou_tableから取り出す
    steps = menu.tejun     #backref=を使ってRecipeからTejunへのrelationを利用し、カレーの作成手順をtejun_tableから取り出す
    
if __name__ == '__main__':
    app.run(debug = True)

Answer 1

arrayがMYSQLに実装されていない理由ですが、
リレーショナルデータベースにとっては、オプションのようなもので
必ずしも実装されているものではないのかなと思います。

なかなかメリットが見えにくいかもしれません。
リレーショナルデータベースになるので、基本的には３つのテーブルに分けることが多いです。

分けることにより、データの検索が容易になります。
例えば、シチューというメニューを追加して、じゃがいもを使う場合です。

じゃがいもという食材を利用している、メニューを探すときにarrayの中に
配列で格納されていると、SQLで検索がしにくくありませんか？
別テーブルに分かれていると、検索がしやすくなります。

これが、リレーショナルデータベースの特徴なので逆にarray列自体が特殊なデータ型である
という認識の方が正しいです。

逆に検索や分析で利用しないのであれば、arrayでもよいと思います。
array型が無くても文字列型で、カンマ区切りで複数データを登録しても良いと思います。

なかなか、うまく説明できなかったので一度「正規化理論」を調べてみてください。

Comments

[sandalwalk]

とても分かりやすい説明でした。文字列型でカンマ区切りにするのは気づきませんでした。今後の為に記憶に留めておきます。今は学習中の身なので、リレーションを使う事にします。確かに、じゃがいもという食材から作れる料理名を検索する時は便利ですね。とりあえず、リレーショナルの使い方としては、こんな感じ（ソースコード）で正しいでしょうか？　本当にこんなに面倒な事するの？と思いながらネットで調べながら作っているので心配です。。。

[Kaiser]

大きな問題は無いと思います。
コードをシンプルにしたければ、DB側でVIEWを利用しても良いかもしれません。

[sandalwalk]

中間テーブルの考え方に導いて頂いたのでベストアンサーにさせて頂きました。

Answer 2

①MySQLでは本当にこんなに面倒な方法で上記のレシピを管理するのが正しいでしょうか。

mysqlでもpostgresでも、多対多（レシピ：使う材料）みたいなテーブルでは配列として文字列保存するよりも中間テーブルを使うのが一般的かと

②arrayが使えず、

今回の用途なら数値でindexの振れるjson型が楽かもしれないですね
※indexが張れるではありません。キーがソートキー代わりに出来るという意味です（誤解を生まないように念のため補足）

arrayがサポートされない理由

mysqlの設計方針は知らないけど、適切な設計をすれば最悪でもenum、json、setで事足りるという考えかも

Comments

[sandalwalk]

①についてですが、「中間テーブル」の存在を初めて知った初心者ですので、にわか理解で書くのですが、具体的には、zairyou = db.relationship('Zairyou', backref='recipe')　は書かず（使わず）、中間テーブルを介して料理名と材料がお互いを探す、ということでしょうか。

[hentaiman]

そうです。
間接的にレシピのIDと素材のIDを紐づけた中間テーブルに情報を持つ事で素材データが冗長になる事を解消でき、また、じゃがいも料理を検索する時にはじゃがいものIDを持つレシピIDを中間テーブルから検索する事で速度もあがります。
「レシピ : 素材」に限らず、「曲 : ジャンル」、「書籍 : カテゴリー」みたいに多対多になるテーブルはまず中間テーブルが必要です。

「レシピ：料理手順」は一対多なので中間テーブルはいりません。

それと、回答には質問に書いてあるarrayの代替としてenuｍ等をあげたけど、極力使わないで済むテーブル設計が良いです。

[sandalwalk]

（１：多）の場合=> zairyou = db.relationship('Zairyou', backref='recipe')の書き方
（多：多）の場合=>中間テーブルを使う
の理解で正しいでしょうか。
arrayの代替については、学習中の身にはお勧めしないという意見があるので、正統派の方法が知りたいと思い質問させて頂きましたので、今回は使わないつもりです。

[hentaiman]

そうです。
学習中に限らずひとつの列に配列などの複数の値を内包するような設計はお勧めしません。