Circular table relations in a symfony project


Symfony | Technical | Development | July 26, 2010

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:

 
Client:
  tableName:                           client
  actAs:
    Timestampable:
  columns:
    id:                                { type: integer  , length: 20 , primary: true    , autoincrement: true                                                  }
    main_contact_id:                   { type: integer  , length: 20                                                                                           }
    code:                              { type: string   , length: 10                    , notnull: true                                                        }
 
Contact:
  tableName:                           contact
  columns:
    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.html"php">  public function save(Doctrine_Connection $conn = null) { if (!sfContext::hasInstance()) { $mainContact = $this->getMainContact(); $this->setMainContact(null); $this->addContact($mainContact); parent::save($conn); $this->setMainContact($mainContact); }   parent::save($conn); }  

 

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:

 

 
Client:
  Client_1:
    MainContact: Contact_1
    code : '00001001'
 
Contact:
  Contact_1:
    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.


Comments


Nicolas Martin said about 14 days later

Why don't you replace the 'main_contact_id' field by a 'Client_contact' table with many to many relationships ?

Jonathan Nieto said about 17 days later

The point when using this method is to know the main contact quickly, instead the contact table could have a "main" field which would indicate if the contact is the main of the client or not, but in this approach querying the main contact would be slower.

Norman said about 2 months later

I always inspired by you, your opinion and way of thinking, again, thanks for this nice post.

imigneegofSom said about 6 months later

det jeg var ute etter, takk



Hey Stranger, leave a comment:

Last Posts

Autoloading: Symfony vs Yii

Symfony2, PhpBB4 and Drupal8

Type and boolean columns with Doctrine and Symfony

Refactoring queries with Doctrine

Extending your Doctrine Model: Template Classes

Integrating Doctrine: Symfony vs Yii

Passing parameters from the action to the view: Symfony vs Yii

Yii framework

Adding custom information to your Doctrine schema

PHP Advent Calendar 2010


My Tweets

19 days ago
Writing clean code in PHP 5.4 | Web Builder Zone: https://t.co/IAFFj3A8 via @addthis
3 months ago
Interesting tips about #scaling https://t.co/QC2paoDK
3 months ago
Learning to use #Windows #ActiveDirectory
4 months ago
Ayni - Blog: CUESTIONARIOS PARA ANÁLISIS-https://localhost:8080/AYNI-war/faces/ListarComentario.xhtml?txtIdPublicacion=2
4 months ago
Día tranquilo en casa :)#fbb
5 months ago
#Adobe Reader App crushed my new #Android movile :/
5 months ago
Testing from Android =D
6 months ago
#Refactoring code.
6 months ago
You should Snog, Marry or Avoid me https://t.co/pZCDIwW
6 months ago
Yahoo’s Options v@TechCrunchnhttps://t.co/yjzsKstKst What will Yahoo do?
6 months ago
Just realize my post https://t.co/nSuJjSp, written so many time ago, really contribute to make it happen: https://t.co/wMqYrV3
6 months ago
An Introduction to Redirecting URLs on an Apache Server https://t.co/s79ThOy via @WebmasterWorld
6 months ago
Actualizando a #Eclipse Indigo!
7 months ago
Vota x lamula.pe: https://t.co/WTWMkFG via @addthis
7 months ago
Apple Pushes Past Exxon To Become The Most Valuable Public Company In The World (Temporarily) via@TechCrunchhhttps://t.co/lowONSZZ
8 months ago
finally this class #semester is finished, new #projects in mind
8 months ago
Mark Zuckerberg Explains His Law Of Social Sharing [Video]https://t.co/sqq10Ehh via@TechCrunchh
9 months ago
retomando mi #twitter #notime
9 months ago
How Facebook Can Put Google Out of Businesshttps://t.co/HqDfQGoo via@TechCrunchh
10 months ago
So why not just cut out the middle man? Microsoft.https://techcrunch.com/2011/05/15/samsung-series-5-chromebook/
10 months ago
really like to #design class hierarchies with #compositepattern
10 months ago
Similarly, Microsoft.com started to use jQuery instead of their own ASP.NET Ajax framework. They are still using Windows, for whatever XD
10 months ago
Estudiantes de la PUCP le “voltean” campaña a esposo de Keiko | yoperiodhttps://t.co/ruI9UnmI9Unm@lamulaamula
10 months ago
Reading: Apress.-.Pro.html"https://www.twitter.com/search?q=#php">#php #performance #read
10 months ago
debug_backtrace() is very important on certain situations. #php #debug
11 months ago
@alvarezrodrich felicitaciones!
11 months ago
time to do some #uml diagrams, #classdiagram
11 months ago
hoy es el día,#votaa conciente#peruu
11 months ago
@skoop @funkatron I think someone had a bad day!, #Frameworks are there but you don't have to use them.
11 months ago
making #wireframes for a new #functionality
11 months ago
so much #spam on my #blog =(
12 months ago
learning new topics and tools that I did not use before #rcp
12 months ago
aprendiendo muchos temas y herramientas que no utilizaba antes #rcp
12 months ago
#tweaking httpd.conf #virtualhost
12 months ago
thanks #symfony 1.4, even when i'm not using the entire #framework, yours classes save my life!
12 months ago
integrating with #SOA using #soap
12 months ago
It seems my most #productive working hours are on #sunday #afternoon #evening! XD
12 months ago
#ASOT 500 =)
12 months ago
installing SCA_SDO on #Centos #php
12 months ago
My web service using #soap worked!!!!! #php #SCA #SDO
12 months ago
Working in a new place since last week!, #RCP: Red Científica Peruana, the one which sells the .pe domains in#Perúú ->#happypy
about 1 year ago
Finally with a new #laptop: #Toshiba =)
about 1 year ago
@pasku1 Thanks, I will try Pivotaltracker.
about 1 year ago
@doolphy thanks for your answer doolphy! I'll try you!
about 1 year ago
@jmasson thanks for your answer! Jira + Confluence is a good combination.
about 1 year ago
Which is the best project management and collaboration tool right now? #projectmanagement #tool #collaboration
about 1 year ago
What a #voice! Sied Van Riel feat Nicole McKenna - Stealing Time (Aly & Fila Remix) + #ASOT 497 #trancefamily
about 1 year ago
working on a situation where #php #traits would be very useful
about 1 year ago
It is #awesome when you finish doing a lot of changes and nothing is broken =) #TDD #testing
about 1 year ago
oh, happy birthday! @mtabini o mejor dicho feliz cumpleaños!
about 1 year ago
why do #IE8 not accept #javascript "const" keyword?
about 1 year ago
Discovering there is much #more to do with #javascript ... a lot.
about 1 year ago
#composition over #inheritance: #javascript
about 1 year ago
Awesome #song!: Cerf, Mitiska & Jaren - Another World (Original Vocal Mix) #ASOT 495 #trancefamily
about 1 year ago
My legal woman is #PHP, but I have an affair with #Javascript, overall when she wears #jQuery.
about 1 year ago
This presentation is one of the best I've seen about #unit #testing https://www.slideshare.net/avalanche123/clean-code-5609451
about 1 year ago
where to do a master on #IT: US or Spain? #survey #php #master plz RT
about 1 year ago
Bobina feat. Betsie Larkin - You Belong To Me: What a #beautiful voice -> #ASOT 494 #arminvanbuuren
about 1 year ago
OH NO, IT'S MONDAY -- 2011-02-07 https://t.co/pgaIxe5 via @gojkoadzic
about 1 year ago
it was not a + b, it was parserInt(a) + parseInt(b) =(, #javascript #fail
about 1 year ago
The models are complete representations of the system, whereas an #architectural #view focuses only on what is architecturally #significant.
about 1 year ago
@jmasson that would be great and finally #wikimedia, #drupal, #wordpress and maybe #joomla would push towards the same side, the #php side.
about 1 year ago
@jmasson Thanks!, #PHP has a bright #future ahead.
about 1 year ago
A new #blog post about not reinventing the #wheel: /article/symfony2_phpbb4_and_drupal8 #symfony #phpbb #drupal
about 1 year ago
@giorgiosironi #indeed, that's a very good #question. I think an average of 4 but also depends on how much that #developer work.
about 1 year ago
It seems the new platform for deploying, managing and scaling PHP apps is https://orchestra.io/ #cool
about 1 year ago
#Phase project planning vs #iteration project #planning - #project #management
about 1 year ago
#jeditable with #jquery save my life =)
about 1 year ago
Amazing #song -> Sied van Riel feat. Nicole McKenna - Stealing Time #ASOT 493 #trancefamily
about 1 year ago
I simply love "offset" #jquery function =)
about 1 year ago
New #blog post: type and boolean columns /article/type_and_boolean_columns_with_doctrine_and_symfony #doctrine #symfony
about 1 year ago
I really don't understand why projects like #drupal does not base their components in projects like #doctrine and #symfony
about 1 year ago
OH NO, IT'S MONDAY -- 2011-01-17 https://t.co/37pr4Bd via @gojkoadzic
about 1 year ago
@alvarezrodrich me alegra ver q ya borró su cuenta Sr. Rodrich,#twitterr es malo jajja, XD
about 1 year ago
Acabo de hacerle a mi #brother @diegonl89 un blog para que hable de #actualidad en general: https://www.elgatotechero.com #peru
about 1 year ago
I just made to my #brother a #blog to talk about current #events in #peru: https://www.elgatotechero.com
about 1 year ago
Amazing things can be done with #javascript and #css, and of course with the help of #jQuery =)
about 1 year ago
An architecturally significant element is an element that is important for #understanding the #system.
about 1 year ago
An architecturally element has a wide impact on the #structure, #performance, #robustness, #evolvability, and #scalability of a #system.
about 1 year ago
@giorgiosironi Definitely!
about 1 year ago
Playing with #table #inheritance in #Doctrine
about 1 year ago
Reading about #RUP, and how addresses the #major difficulties in a new #project.
about 1 year ago
A new blog #post about #refactoring your queries with #Doctrine: /article/refactoring_queries_with_doctrine #symfony
about 1 year ago
Yandex in 2010: 43 percent revenue growth https://t.co/cpjT5Jw via @cnet
about 1 year ago
Going forward!!!!! =) poco a poco llegan los resultados de tanto esfuerzo #fb
about 1 year ago
Perfect #system with respect to the #requirements but the #wrong system with respect to the #real #problem at the time of #delivery.
about 1 year ago
Going #forward! =) #fb
about 1 year ago
A new #blog #post about integrating #Doctrine with #Yii and #Symfony: /article/integrating_doctrine_symfony_vs_yii
about 1 year ago
@sam_dark Ok thanks!, but I don't understand why in #Yii documentantion use $_GET and $_POST instead of CHttpRequest https://bit.ly/i5emoL