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>I suggest you extract a base class for Question and Answer, e.g. Post, and make Comment relate to Post, such that a Post can have multiple Comments.</p>

<p>SQLAlchemy ORM supports few strategies to <a href="http://docs.sqlalchemy.org/en/latest/orm/inheritance.html" rel="nofollow">implement inheritance in the database</a>, and the right strategy to choose depends on the way you plan to query your entities. Here's the <a href="http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/inheritance.html" rel="nofollow">detailed documentation on how to properly configure it</a>.</p>

<p>So you'd get something like this:</p>

<p><em>(disclaimer: I have not directly ran this code, but composed it from your example, and my own code. If it ain't working for you, let me know and I'll fix)</em></p>

<pre><code>class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key=True)
    kind = Column(db.Unicode(64), nullable=False)
    body = db.Column(db.UnicodeText)
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    comments = db.relationship('Comment', backref='post', lazy='dynamic')

    __mapper_args__ = {
        'polymorphic_identity': 'posts',
        'polymorphic_on': kind
    }

class Question(Post):
    __tablename__ = 'questions'

    title = db.Column(db.Unicode(64), unique=True)
    answers = db.relationship('Answer', backref='question', lazy='dynamic')

    __mapper_args__ = {
        'polymorphic_identity': 'questions',
    }

class Answer(Post):
    __tablename__ = 'answers'

    question_id = db.Column(db.Integer, db.ForeignKey('questions.id'))

    __mapper_args__ = {
        'polymorphic_identity': 'answers',
    }

class Comment(db.Model):
    __tablename__ = 'comments'

    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
</code></pre>

python sqlalchemy