How to build crosstables with DBDesigner4 and symfony MyWay
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
- menu_id as autoincrement
- menu_name as VARCHAR
- some other stuff you like (description etc.)
- 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.
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.