One table for multiple images associated with one object/model

[mattupstate]

trafficstars

Is it possible to use one table for all the images associated with one object/model? It appears that two tables are required as illustrated by the UserPicture and UserFrontCover relationships in the sqlalchemy_imageattach.entity module documentation.

I'd much prefer something like:

class User(Base):
    avatar_image = image_attachment('UserImage')
    background_image = image_attachment('UserImage')

Given the above model, SQLAlchemy can't determine the join condition. When I've come across this before, I generally have the foreign key on the "parent", in this case the User model and looks like this:

class User(Base):
    avatar_image_id = Column(Integer, ForeignKey('user_images.id'))
    avatar_image = relationship('UserImage', foreign_keys=avatar_image_id)
    background_image_id = Column(Integer, ForeignKey('user_images.id'))
    background_image = relationship('UserImage', foreign_keys=background_image_id)

Is there a reason you chose to store the foreign key on the Image model? Is there a way of getting around requiring two tables? Or am I just missing some configuration option to avoid using two tables?

[mattupstate]

Actually, I just realized I can probably do this:

class UserImage(Image, Base):
    __tablename__ = 'user_images'

class User(Base):
    avatar_image_id = db.Column(db.Integer, db.ForeignKey('user_images.id'))
    avatar_image = image_attachment('UserImage', 
        foreign_keys=avatar_image_id, single_parent=True)
    background_image_id = db.Column(db.Integer, db.ForeignKey('user_images.id'))
    background_image = image_attachment('UserImage', 
        foreign_keys=background_image_id, single_parent=True)

[dahlia]

You can put any arbitrary columns to UserImage class e.g. type, and union two types of images (avatar_image and background_image) into one table and distinguish two by filtering type column.