A complex data model. Users vote on bands, and on associations between bands and genres. (For example: User "Alice" thinks that "Screaming Orgasm" is a "Metal" band.) The `BandGenre` class isn't actually necessary: you can get the set of all `Genre` objects associated with a specific `Band` just by doing a join on `BandGenreVote` and grouping by…

models.py

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.sql import select, func
from sqlalchemy.ext.hybrid import hybrid_property
from datetime import datetime

__all__ = ['User', 'Band', 'Genre', 'BandVote', 'BandGenre', 'BandGenreVote']

db = SQLAlchemy()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    pw_hash = db.Column(db.String(60))
    created_on = db.Column(db.DateTime, default=datetime.utcnow)
    created_bands = db.relationship('Band', backref="created_by")
    band_votes = db.relationship('BandVote', backref="user")
    band_genre_votes = db.relationship('BandGenreVote', backref="user")

    def __repr__(self):
        return u"<User '{name}'>".format(name=self.username)


class Band(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(160), unique=True, nullable=False)
    creator_id = db.Column('user_id', db.Integer, db.ForeignKey('user.id'),
        nullable=False) # created_by
    created_on = db.Column(db.DateTime, default=datetime.utcnow)
    votes = db.relationship('BandVote', backref="band")
    voters = db.relationship(User, secondary="band_vote",
        backref=db.backref('bands', lazy='dynamic'))
    genres = db.relationship('Genre', secondary="band_genre",
        backref=db.backref('bands', lazy='dynamic'))

    @hybrid_property
    def vote_weight(self):
        return (db.session.query(func.sum(BandVote.weight))
                .filter(BandVote.band == self)).scalar()

    @vote_weight.expression
    def vote_weight_exp(cls):
        return (select([func.sum(BandVote.weight)])
               .where(BandVote.band_id == cls.id)
               .label('vote_weight'))

    def __repr__(self):
        return u"<Band '{name}'>".format(name=self.name)


class Genre(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)

    def __repr__(self):
        return u"<Genre '{name}'>".format(name=self.name)


# many-to-many association tables!
band_vote = db.Table('band_vote',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('band_id', db.Integer, db.ForeignKey('band.id'), primary_key=True),
    db.Column('weight', db.Integer, default=1),
    db.Column('created_on', db.DateTime, default=datetime.utcnow),
    # one vote per user per band
    db.UniqueConstraint('user_id', 'band_id'),
)

band_genre = db.Table('band_genre',
    db.Column('band_id', db.Integer, db.ForeignKey('band.id'), primary_key=True),
    db.Column('genre_id', db.Integer, db.ForeignKey('genre.id'), primary_key=True),
    # can't assign the same genre multiple times to a band
    db.UniqueConstraint('band_id', 'genre_id'),
)

band_genre_vote = db.Table('band_genre_vote',
    db.Column('band_id', db.Integer, primary_key=True),
    db.Column('genre_id', db.Integer, primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('created_on', db.DateTime, default=datetime.utcnow),
    db.Column('weight', db.Integer, default=1),
    # constraints
    db.ForeignKeyConstraint(['band_id', 'genre_id'],
        ['band_genre.band_id', 'band_genre.genre_id']),
    db.UniqueConstraint('band_id', 'genre_id', 'user_id'),
)


class BandVote(db.Model):
    __table__ = band_vote

    def __repr__(self):
        try:
            return u"<BandVote user='{user}' band='{band}' weight={weight:d}>".format(
                user=self.user.username, band=self.band.name, weight=self.weight)
        except AttributeError:
            return u"<BandVote user_id={user_id} band_id={band_id} weight={weight:d}>".format(
                user_id=self.user_id, band_id=self.band_id, weight=self.weight)


class BandGenre(db.Model):
    __table__ = band_genre
    votes = db.relationship("BandGenreVote", backref="band_genre")
    voting_users = db.relationship(User, secondary=band_genre_vote,
                        backref=db.backref("band_genres"))

    @hybrid_property
    def vote_weight(self):
        return (db.session.query(func.sum(BandGenreVote.weight))
                .filter(BandGenreVote.band_genre == self)).scalar()

    @vote_weight.expression
    def vote_weight_exp(cls):
        return (select([func.sum(BandGenreVote.weight)])
               .where(BandGenreVote.band_id == cls.band_id)
               .where(BandGenreVote.genre_id == cls.genre_id)
               .label('vote_weight'))

    def __repr__(self):
        try:
            return u"<BandGenre band='{band}' genre='{genre}'>".format(
                band=self.band.name, genre=self.genre.name)
        except AttributeError:
            return u"<BandGenre band_id={band_id} genre_id={genre_id}>".format(
                band_id=self.band_id, genre_id=self.genre_id)


class BandGenreVote(db.Model):
    __table__ = band_genre_vote

    def __repr__(self):
        try:
            return u"<BandGenreVote user='{user}' band='{band}' genre='{genre}' weight={weight}>".format(
                user=self.user.username, band=self.band.name, genre=self.genre.name,
                weight=self.weight)
        except AttributeError:
            return u"<BandGenreVote user_id={user_id} band_id={band_id} genre={genre_id} weight={weight}>".format(
                user_id=self.user_id, band_id=self.band_id, genre_id=self.genre_id,
                weight=self.weight)