SQLite database for a micro/tumble blog application

Question

I'm creating a personal website where, among other things, there is a blog, or a "microblog" (I'm still not sure how to define the two), where I will be writing articles and having users, who will create accounts, write comments on the posts. In addition, the blog posts will be tagged to allow for easier searching of specific blog posts. I was also planning on having the users' comments and posts be listed on their profile page, so that it is possible to view all comments or all posts made by a specific user (and reach said comment or post from their profile).

The blog will be built using Python 3, Flask, and SQLAlchemy for the backend.

The structure for the website will be a list of Users, which has a list of Posts that they've created (and are related to in a one-to-many relationship). Each Post will have only one author (one-to-one), but can be tagged a variety of Tags (many-to-many since many posts can be tagged with many tags). Each post will also have a list of comments (multiple comments), but each comment can only be linked to one Post and one User (the author).

Does the models code that I have below accurately describe and implement the design of my database that I'm trying to achieve?

from hashlib import md5
from app import lm, db
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.login import LoginManager, UserMixin


class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id            = db.Column(db.Integer, primary_key=True)
    join_date     = db.Column(db.DateTime)
    username      = db.Column(db.String(64), index=True, unique=True, nullable=False)
    realname      = db.Column(db.String(128), index=True)
    email         = db.Column(db.String(128), index=True)
    role          = db.Column(db.String(64))
    about_me      = db.Column(db.String(500))
    last_seen     = db.Column(db.DateTime)

    posts         = db.relationship('Post', backref='author', lazy='dynamic')
    comments      = db.relationship('Comment', backref='author', lazy='dynamic')

    def avatar(self, size):
        gravatar_url = 'http://www.gravatar.com/avatar/%s?d=identicon&s=%d&r=pg'
        return gravatar_url % md5(self.email.encode('utf-8')).hexdigest()

    def __repr__(self):
        _repr = '<models.User instance; ID: {}; username: {}>'
        return _repr.format(self.id, self.username)

@lm.user_loader
def load_user(id):
    return User.query.get(int(id))


class Post(db.Model):
    __tablename__ = 'posts'
    id            = db.Column(db.Integer, primary_key=True)
    author_id     = db.Column(db.Integer, db.ForeignKey('users.id'))
    tag_id        = db.Column(db.Integer, db.ForeignKey('tags.id'))
    title         = db.Column(db.String(128))
    body          = db.Column(db.String(20000))
    create_date   = db.Column(db.DateTime)
    comments      = db.relationship('Comment', backref='tagged_post', lazy='dynamic')

    def __repr__(self):
        _repr = '<models.Post instance; ID: {}; title: {}>'
        return _repr.format(self.id, self.title)


class Tag(db.Model):
    __tablename__ = 'tags'
    id            = db.Column(db.Integer, primary_key=True)
    title         = db.Column(db.String(64))
    tagged        = db.relationship('Post', backref='tag', lazy='dynamic')

    def __repr__(self):
        _repr = '<models.Tag instance; ID: {}; title: {}>'
        return _repr.format(self.id, self.title)


class Comment(db.Model):
    __tablename__ = 'comments'
    id            = db.Column(db.Integer, primary_key=True)
    post_id       = db.Column(db.Integer, db.ForeignKey('posts.id'))
    author_id     = db.Column(db.Integer, db.ForeignKey('users.id'))
    body          = db.Column(db.String(1500))
    create_date   = db.Column(db.DateTime)

    def __repr__(self):
        _repr = '<models.Comment instance; ID: {}; post_title: {}>'
        return _repr.format(self.id, self.post_id.title)

Here is a link to the (current) full source of the website.

Answer 1

Everything is good by this point but I'd change the relation between posts and tags from one-to-many to many-to-many.

You can do this with this code:

tags_to_posts = db.Table(
    "tags_and_posts",
    db.Column( "post_id", db.Integer, db.ForeignKey( "posts.id" ) ),
    db.Column( "tag_id", db.Integer, db.ForeignKey( "tags.id" ) )
)

class Post(db.Model):
    # ...
    tags = db.relationship(
        "Tag",
        secondary=tags_and_posts,
        backref=db.backref( "posts", lazy="dynamic" ),
        passive_deletes=True,
    )

Then tags will be just a list. Assuming my_tag and my_post exist, you'll be able to do something like this:

# Add tag to post
my_post.tags.append(my_tag)
db.session.add(my_post)
db.session.add(my_tag)
db.session.commit()

# Remove tag from post
# You need to be sure that my_tag is in my_post.tags
my_post.tags.remove(my_tag)
db.session.add(my_post)
db.session.add(my_tag)
db.session.commit()

I hope, it helps.