Define one SQLAlchemy model to be related to one of multiple other models
<p>I'm designing the database for my own Q&amp;A website, which is somewhat similar to Stack Overflow: </p>

<ol>
<li>A "question" can have several "answers", but an "answer" has only one "question".</li>
<li>Both "questions" and "answers" can have several "comments", but a "comment" has only one "question" or "answer".</li>
</ol>

<p>I have no idea how to design such a database. Here is what I've tried:</p>

<pre><code>class Question(db.Model):
    __tablename__ = 'questions'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.Unicode(64), unique=True)
    body = db.Column(db.UnicodeText)
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    answers = db.relationship('Answer', backref='question', lazy='dynamic')

class Answer(db.Model):
    __tablename__ = 'answers'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.UnicodeText)
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    question_id = db.Column(db.Integer, db.ForeignKey('questions.id'))

class Comment(db.Model):
    __tablename__ = 'comments'
    id = db.Column(db.Integer, primary_key=True)
    # post_id = db.Column(db.Integer, db.ForeignKey('')) ???
</code></pre>

<p>So you've already managed the first point.</p>

<p>What you're looking for is a generic relationship. You can find it in <a href="https://sqlalchemy-utils.readthedocs.io/en/latest/index.html" rel="nofollow"><code>sqlalchemy_utils</code></a> package.</p>

<pre><code>from sqlalchemy_utils import generic_relationship


class Comment(db.Model):
    __tablename__ = 'comments'
    id = db.Column(db.Integer, primary_key=True)
    object_type = db.Column(db.Unicode(255))
    object_id = db.Column(db.Integer)
    object = generic_relationship(object_type, object_id)
</code></pre>

<p><a href="http://sqlalchemy-utils.readthedocs.io/en/latest/generic_relationship.html" rel="nofollow">Docs</a> for generic relationship</p>

<p>So basically what it does, it stores the <code>object_type</code> as answer or question and <code>object_id</code> as object's primary key.</p>

python sqlalchemy