Unable to establish correct parent-child relationship in sql

Question

The problem:

SQLAlchemy model code for parent-child relationships is not being created as expected by SQLacodegen.

My workflow:

In the ER diagram, product is the parent table, feature and related are child tables. The relationship between product and feature is supposed to be many-to-many, as is the relationship between product and related.

Excerpt of what I want the Product class to look like:

class Product():
    relateds = relationship('Related', secondary='product_related')
    features = relationship('Feature', secondary='product_featured')

Excerpt of what SQLacodegen does:

class Product():
    relateds = relationship('Related', secondary='product_related')
    # features is missing

class Feature():
   # products appears here, making Feature the parent of Product
   # when it should be the other way round
   products = relationship('Product', secondary='product_features')

Original SQL code for the tables:

-- Table: product
CREATE TABLE product (
    id bigserial  NOT NULL,
    mpn text  NULL,
    title text  NULL,
    price real  NULL,
    msrp real  NULL,
    stock_availability boolean  NULL,
    country_id int8  NULL,
    description text  NULL,
    weight_packaging real  NULL,
    item_type varchar(20)  NULL,
    upc varchar(40)  NULL,
    model varchar(60)  NULL,
    sku varchar(40)  NULL,
    badges text  NULL,
    url text  NULL,
    site_item_id text  NULL,
    brand_id int8  NULL,
    CONSTRAINT product_id PRIMARY KEY (id)
);

CREATE INDEX country_id on product (country_id ASC);

CREATE INDEX product_idx_2 on product (brand_id ASC);


-- Table: related
CREATE TABLE related (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT related_pk PRIMARY KEY (id)
);

-- Table: product_related
CREATE TABLE product_related (
    product_id int8  NOT NULL,
    related_id int8  NOT NULL,
    CONSTRAINT product_related_pk PRIMARY KEY (product_id,related_id)
);

CREATE INDEX product_related_idx_1 on product_related (product_id ASC);

CREATE INDEX product_related_idx_2 on product_related (related_id ASC);

-- Reference: product_related_related (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_related
    FOREIGN KEY (related_id)
    REFERENCES related (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_related_product (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Table: feature
CREATE TABLE feature (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT feature_pk PRIMARY KEY (id)
);

-- Table: product_features
CREATE TABLE product_features (
    product_id int8  NOT NULL,
    feature_id int8  NOT NULL,
    CONSTRAINT product_features_pk PRIMARY KEY (product_id,feature_id)
);

CREATE INDEX product_features_idx_1 on product_features (product_id ASC);

CREATE INDEX product_features_idx_2 on product_features (feature_id ASC);

-- Reference: product_features_feature (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_feature
    FOREIGN KEY (feature_id)
    REFERENCES feature (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_features_product (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

Full code generated by SQLacodegen:

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

    products = relationship('Product', secondary='product_features')


class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')

What I would expect SQLacodegen's output to look like:

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')
    features = relationship('Feature', secondary='product_feature')

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

Answer 1

The parent-child relationship is determined based on the order of table names in the database. The table names are alphabetically sorted in the database. In this instance, the Product is coming before Related, therefore, the Product is the parent of Related. However, since Feature comes before Product, the former is bein defined as the parent of the latter.