Table with many Foreign Keys -Junction table naming

Need Help
1

Hi,

How do I create my tables so I can capitalize on CakePHP ORM. The case being I have a table with many foreign keys.

example:
Vehicles

  • type [sports car, sedan, pickup, suv, motocycle, etc]
  • color [lots of different colored names]
  • brand [ brand names]
  • model [model names]

The type, color, brand, model are going to be foreign keys. So below is some text on Database Convention for CakePHP

Join tables, used in BelongsToMany relationships between models, should be named after the model tables they will join or the bake command won’t work, arranged in alphabetical order ( articles_tags rather than tags_articles ). If you need to add additional columns on the junction table you should create a separate entity/table class for that table.

In my sample above, the vehicles table the junction table? So I should name this table brands_colors_models_types?

2

A given vehicle surely has only one type, one color, one brand and one model. So, this isn’t a “belongs to many” relation, it’s just “belongs to”, which doesn’t use a junction table at all. Your table can simply be called vehicles, and would have columns type_id, color_id, brand_id and model_id.

3

Thanks for sticking with me, @Zuluru.

But that’s not what I’m going for. Though that is still worth noting.

I’m looking for the correct cakephp database convention. I’m not yet clear with the way my database design is right now so let’s just this is the table:

tables --> fka_id, fkb_id, fkc_id, fkd_id, fielda, fieldb, fieldc

Let’s just say that fk[a-d]s tables have many to many relationship, so my tables name will now be: fkas_fkbs_fkcs_fkds? Is this how it should be with cakephp database convention so I can effectively use its ORM or the bake console command. Generating the CRUD was great when I can apply it to my database. :slight_smile:
I ask this because I may have many to many relationship with more than two tables involved. In the cakephp tutorial 4, the example is just between two tables: articles_tags.

4

Many-to-many relationships are generally between individual tables. A single table might have multiple such relationships, in which case there will be one join table for each one. As a concrete example, in my application, the people table has many-to-many relationships with badges (join table is badges_people), groups (groups_people), teams (teams_people) and more.

If you have a scenario where it’s not multiple individual relations but a relation that somehow involves multiple tables all at once, then my first thought is that you need a new table that stores those combinations, and then a join table between that and your other table. But it’s hard to give specific advice without the specifics of your situation; when making things generic, people often miss something important, or make invalid assumptions based on their preconceived notion of the expected solution.

1 Like
5

I’m not sure why it would be valid you your Tables to make associations like this:

//Cars table associations

        $this->belongsToMany('Thing', [
            'foreignKey' => 'car_id',
            'targetForeignKey' => 'thing_id',
            'joinTable' => 'cars_features',
        ]);
        $this->belongsToMany('Widgit', [
            'foreignKey' => 'car_id',
            'targetForeignKey' => 'widigit_id',
            'joinTable' => 'cars_features',
        ]);
        $this->belongsToMany('Doodad', [
            'foreignKey' => 'car_id',
            'targetForeignKey' => 'doodad_id',
            'joinTable' => 'cars_features',
        ]);

// cars_features table has thing_id, widget_id, doodad_id, car_id
// cars table has id and other columns
// thing table has id and other columns
// widget table has id and other columns
// doodad table has id and other columns

Just speculating.

6

I recently wrote a short post about how to name junction tables that I believe underlines why it is important to get the naming right, and how to find a good name: How to Name your Junction Tables