Unrecognized table getting created when Doctrine builds the database

Question

I have the following schema.yml

Proposition:
  actAs: { Timestampable: ~ }
  columns:
    name: { type: string(255), notnull: true }
    slug: { type: string(255), notnull: true, unique: true }
    proposition_type_id: { type: integer, notnull: true }
    icon: { type: string(255) }
    overview: { type: string(4000) }
    features: { type: string(4000) }
    benefits: { type: string(4000) }
    published: { type: boolean, notnull: true, default: 1 }
  relations:
    PropositionType: { onDelete: CASCADE, local: proposition_type_id, foreign: id }
    Products:
      class: Product
      refClass: PropositionProduct
      local: proposition_id
      foreign: product_id
      foreignAlias: PropositionProducts

PropositionType:
  columns:
    name: { type: string(255), notnull: true }

Review:
  actAs: { Timestampable: ~ }
  columns:
    proposition_id: { type: integer, notnull: true }
    review: { type: string(4000), notnull: true }
    name: { type: string(255), notnull: true }
    company: { type: string(255) }
    published: { type: boolean, notnull: true, default: 1 }
  relations:
    Proposition: { onDelete: CASCADE, local: proposition_id, foreign: id }

PropositionProduct:
  columns:
    proposition_id: { type: integer, primary: true }
    product_id: { type: integer, primary: true }
  relations:
    Proposition: { onDelete: CASCADE, local: proposition_id, foreign: id }
    Product: { onDelete: CASCADE, local: product_id, foreign: id }

Product:
  actAs: { Timestampable: ~ }
  columns:
    name: { type: string(255), notnull: true }
    slug: { type: string(255), notnull: true, unique: true }
    icon: { type: string(255) }
    ataglance: { type: string(4000) }
    idealfor: { type: string(4000) }
    details: { type: string(4000) }
    specsheet: { type: string(255) }
    chart: { type: string(255) }
    published: { type: boolean, notnull: true, default: 1 }
  relations:
    RelatedProducts:
      class: Product
      refClass: RelatedProduct
      local: product_id
      foreign: related_product_id
      foreignAlias: RelatedProducts

RelatedProduct:
  columns:
    product_id: { type: integer, primary: true }
    related_product_id: { type: integer, primary: true }
  relations:
    Product: { onDelete: CASCADE, local: product_id, foreign: id }
    Product: { onDelete: CASCADE, local: related_product_id, foreign: id }

Segment:
  actAs: { Timestampable: ~ }
  columns:
    name: { type: string(255), notnull: true }
    slug: { type: string(255), notnull: true, unique: true }
    published: { type: boolean, notnull: true, default: 1 }
  relations:
    Products:
      class: Product
      refClass: SegmentProduct
      local: segment_id
      foreign: product_id
      foreignAlias: SegmentProducts

SegmentProduct:
  columns:
    segment_id: { type: integer, primary: true }
    product_id: { type: integer, primary: true }
  relations:
    Segment: { onDelete: CASCADE, local: segment_id, foreign: id }
    Product: { onDelete: CASCADE, local: product_id, foreign: id }

I ran:

php symfony doctrine:build --all --and-load --no-confirmation

and the database has built successfully.

But why has a proposition_segment table been created?

CREATE TABLE `proposition_segment` (
  `segment_id` bigint(20) NOT NULL DEFAULT '0',
  `product_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`segment_id`,`product_id`),
  KEY `proposition_segment_product_id_product_id` (`product_id`),
  CONSTRAINT `proposition_segment_product_id_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE,
  CONSTRAINT `proposition_segment_segment_id_segment_id` FOREIGN KEY (`segment_id`) REFERENCES `segment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

From my understanding my scheme should detail that Segment and Product have a many to many relationship through the SegmentProduct table.

Likewise, Proposition and Product have a many to many relationship through the PropositionProduct table.

I can't understand why Doctrine is creating a proposition_segment table. Other than this the database looks correct — it's created tables proposition_product and segment_product as expected.

When I add data through the Symfony generated admin back-end the proposition_segment table remains empty, increasing my suspicion that it's created in error.

Answer 1

Thanks for the links greg0ire, after playing about with it for a few hours this morning I decided to create a new blank Symfony project and load my scheme.yml into that. After building it I'm getting the database structure I desire! So it turns out that the scheme is actually correct and it must be a form class somewhere that's generating extra tables? I'll do some more digging to try to find out what's going on. I've also altered the Alias and created an equal nest relation based on your recommendations - thanks.

EDIT:

Bingo! I found some classes in lib/model/ that weren't needed, tidying up this folder has fixed the extra tables being generated. I though Doctrine only read from the config/doctrine/scheme.yml file but I guess it also reads your models to? - Of course it does because in all the examples it shows the two different ways to create the classes. I'm still not exactly sure when these files were generated but I'll keep an eye on my models from now on.