I am trying to build a database design for a product catalogue table where product’s different variations like multiple colour and/or sizes will be referred from another table. I should be allowed to have a situation where a product may not have any variations at all and there should be a provision of having all possible variations entered in the variations table despite not products referring to it. I don’t see this supported by usual relationships available in the SQLAlchemy ORM. This is what I have done so far:

class Catalogue(Base):
    __tablename__ = 'catalogue'
    id                  = Column(String(8), primary_key=True, default=lambda: uid_generator(prefix='P', size=6))
    created             = Column(DateTime())
    updated             = Column(DateTime())
    enabled             = Column(Boolean())
    name                = Column(String(64), nullable=False)
    title               = Column(String(255), nullable=False)
    sub_title           = Column(String(255))
    variations          = relationship('Variation')
    brief_description   = Column(String(512))
    description         = Column(String(1024))
    notes               = Column(String(1024))
    category            = Column(String(255))
    manufacturer        = Column(String(255))
    
    def __str__(self):
        return self.name

class Variation(Base):
    __tablename__ = 'product_variations'
    id              = Column(String(8), primary_key=True, default=lambda: uid_generator(prefix='V', size=6))
    catalogue_id    = Column('catalogue_id', String(8), ForeignKey('catalogue.id'))
    created         = Column(DateTime(), nullable=False, default=now())
    updated         = Column(DateTime())
    name            = Column(String(255), nullable=False)
    category        = Column(String(64), nullable=False)
    sub_category    = Column(String(64), nullable=False)
    unit            = Column(String(64), nullable=False)
    text            = Column(Text())
    
    def __str__(self):
        return self.name

When I try to add a variation like below

if request.method == 'POST' and form_variation.validate():
        
        variation = Variation(  name            = form_variation.name.data,   
                                category        = form_variation.category.data,
                                sub_category    = form_variation.sub_category.data,
                                unit            = form_variation.unit.data,
                                text            = form_variation.text.data,
                            )
        db_session.add(variation)
        db_session.commit()

I get the following error:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'catalogue_id' in 'field list'")
[SQL: INSERT INTO product_variations (id, catalogue_id, created, updated, name, category, sub_category, unit, text) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('V-J1F2Z3', None, datetime.datetime(2024, 4, 28, 9, 13, 25, 818094), None, 'a', 'a', 'a', 'a', 'a')]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

One alternative I was thinking was to put a comma separated ids in the catalogue table’s variations field. A quick but a dirty way, what are the alternatives do you guys suggest?