Circular table relations in a symfony project

Circular relations in the database occur when a foreign key of one table is included in a separate table, whilst at the same time a further foreign key is located in the first table. Usually this is not desirable because it can produce many problems when making queries or inserting data. However sometimes it can improve performance. For example we could have a model like:

Client table
Contact table

There is a client that has a collection of contacts, but there is also a main contact. This record needs to be retrieved rapidly, in other words, without a query.

When working with symfony 1.* and doctrine there could be a shema like:

  tableName:                           client
    id:                                { type: integer  , length: 20 , primary: true    , autoincrement: true                                                  }
    main_contact_id:                   { type: integer  , length: 20                                                                                           }
    code:                              { type: string   , length: 10                    , notnull: true                                                        }
  tableName:                           contact
    id:                                { type: integer  , length: 20 , primary: true    , autoincrement: true                                                  }
    client_id:                         { type: integer  , length: 20                    , notnull: true                                                        }
    name:                              { type: string   , length: 200                   , notnull: true                                                        }
    position:                          { type: string   , length: 100                                                                                          }

Until now everything should be functioning correctly. However a problem can arise when trying to load data using the fixtures functionality because the ORM does not know which record to insert first. Nevertheless this problem can be fix by adding the following code into the Client.class.php file:

  public function save(Doctrine_Connection $conn = null)
    if (!sfContext::hasInstance())
      $mainContact = $this->getMainContact();

The code allows the client to be saved without a main contact. Once saved both the client and the contact set the main contact.

Now some data can be upload without hassle:

    MainContact: Contact_1
    code : '00001001'
    Client: Client_1
    name: 'Jonathan Nieto'
    position: 'Developer'

In this way circular data can be uploaded in a symfony project.

With circular relations there are usually problems, however because of their performance they are often needed.

Leave a Reply

Your email address will not be published. Required fields are marked *