Ndtyjky

I have difficulty to model these relations.

There is products, each one belonging to a product_type. Product_types have a list of different features eg. color, size, weight, volume, in a relation of many-to-many. In their turn, each features have differents feature_options eg. red, blue, green, black for colors. Finally products have some of the feature_options. One example should be the product BIC2006 which have the product_type pen, whose features are color and weight, and which has in these features the feature_options red and blue for the color and 1g and 2g for the weight.

At the end I would like to store the feature_options by products and query by products the list of feature_options by feature :

BIC2006 - Pen

Color : Red, Blue

Weight : 1g, 2g

What should be the best DB model to use with SQLAlchemy?

For now, as I need to store the feature_options by products I tried with this, but I'm not sure about how to retrieve the sort by features :

class ProductTypes_Features(Base):

    __tablename__ = 'product_types_features'

    product_type_id = Column(Integer, ForeignKey('product_types.id'), primary_key=True)

    feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)



class Feature_FeatureOptions(Base):

    __tablename__ = 'feature_feature_options'

    feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)

    feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)



class Product_FeatureOptions(Base):

    __tablename__ = 'product_feature_options'

    product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)

    feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)

    feature_id = Column(Integer, ForeignKey('features.id'))   #??



class Products(Base):

    __tablename__ = 'product'

    id = Column(Integer, primary_key=True)

    product_type_id = Column(Integer, ForeignKey('product_types.id'))

    feature_options = relationship("Product_FeatureOptions")



class FeatureOptions(Base):

    __tablename__ = 'feature_options'

    id = Column(Integer, primary_key=True)



class Features(Base):

    __tablename__ = 'features'

    id = Column(Integer, primary_key=True)

    feature_options = relationship("Feature_FeatureOptions")



class ProductTypes(Base):

    __tablename__ = 'product_types'

    id = Column(Integer, primary_key=True)

    features = relationship("ProductType_Features")

How can I query?

session.query(Products.id, 

    Products.feature_options.feature_id,

    Products.feature_options.feature_option_id).

    order_by(...).all()

Thanks a lot for your best practices advices!