Odtnhj

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!