CakePHP mysql database naming conventions question

First: I have read the naming convention rules from CakePHP cookbook, so dont direct me there :) I might though be understanding something incorrectly and if so please correct me ;)

The problem开发者_如何学运维 Im having is that I have tables:

• items
• quests

Then I HAD tables:

• item_has_quest_requirements
• quest_has_item_requirements
• quest_has_item_rewards
• quest_has_item_recommends

Which are just HABTM tables with 2 primary keys connecting these 2 tables. Now, some of them also have their OWN specific columns also which is why I had not put them into the same table. For example, item_has_quest_requirements has just the keys, but quest_has_item_requirements has also amount field as do rewards and recommends aswell.

However, if I follow CakePHP naming conventions, I should NOT use composite primary keys and if I wanted to connect 2 tables together as HABTM tables, I should name the tables in alphabetical order by table names. Which means AFAIK that these tables would ALL become: items_quests tables which is impossible.

Would the only way be then to create totally new extra table like: quest_requirements which would have the amount column and then make the HABTM table like: items_quest_requirements ?

This is what I have been rolling in my mind so could you enlighten a bit what would be the best approach?

There are no naming conventions for when you have multiple associations between two tables. In this case you have to specify the involved tables manually in your models, see http://book.cakephp.org/view/1039/Associations-Linking-Models-Together#hasAndBelongsToMany-HABTM-1044 for details.

However, you mention some join tables will contain additional columns. For such a scenario it might be a better approach to use Join Models (also known as "hasMany through") instead of HABTM associations.

Try to reproduce it with http://cakeapp.com, it forces you to use the cake naming conventions. And allows you to download the DB schema afterwards.