How to build crosstables with DBDesigner4 and symfony MyWay

Introduction

A crosstable (or hashtable) is needed if you want to use "n:m" or "many-to-many" relations in your database. This sometimes happens, when you f.e. build a menu-structure, where one mainmenu can have several submenus and can belong to several mainmenus. (f.e. the sublink sitemap).

Building the ER model in DBDesigner 4

For we can convert DBDesigner and Powerdesigner Databasemodels into our beloved schema.xml, I will explain it for the freewareversion.

Start DBDesigner and add two tables to your model

• mainmenu
• submenu

mainmenu has:

• menu_id as autoincrement
• menu_name as VARCHAR
• some other stuff you like (description etc.)

submenu has:

• submenu_id as autoincrement
• submenu_name as VARCHAR
• some other substuff

Now, choose the n:m Relation button from the menu on your left and click the two tables to relate them in n:m You will see that a third table is created automatically by DBDesigner named something like "mainmenu_has_submenu" containing the two ids as PRIMARY KEY.

Adjusting the ER model to work with our admin_generator

Like we see, DBDesigner has generated the Relations as "Idetivied Relations" and therefore generates both of the ids as PRIMARY KEY. The symfony framework won't identify the generated code as that what we want, with our

$ symfony propel-init-admin

because of these two PRIMARY KEYS. We could create a new Criteria in our classes but this is more coding which we want to avoid (smile). If we change the relations between the two tables into "non-identified" and delete the two PRIMARY KEY's and build the model again, we get other serious problems with the generated code, because some functions and controllers (if-else-foreach) are left blank. If we would choose one of the ids in the cross-table this column is not shown in the generated pages.

Solution

So, to get a page generated, that gives us the possibility to select an id from 'mainmenu' and relate it to an id of 'submenu' we need to add an own id to the cross-table with a normal auto-increment.

Our mainmenu_has_submenu table should now have:

• main_to_sub_id //as own autoincrement id
• mainmenu_id //as 1:n relation to our mainmenu table
• submenu_id //as 1:n relation to our submenu table

The generated code using the

$symfony propel-init-admin ourApp maintosub MainmenuHasSubmenu 

will now generate a page that lets us choose an insert in table mainmenu and relate it to an insert in table submenu by dropdown.

DONE