We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

what is the best best practice for tables relations

Our database administrator asked me to drop all the relation between the tables in the database, and replace in code, meaning create the relation in php code instead of inside the DB itself.

Example: $this->belongsTo('id','App\Request','request_id', [ 'foreignKey'=> [ 'action'=>\Phalcon\Mvc\Model\Relation::ACTION_CASCADE ] ] );

We both bad a long debate regarding this, the main key of the debate was was the performance and data source best usage.

the question is whis is better regarding performance? a real db relation or doing this in coding? @phalconphp @phalcon



10.4k
edited Dec '14

Performance: it is always faster and safer, if database engine manage foreign keys integrity, and do appropriate actions in DB triggers

Flexibility: doing it on php/orm side gives you more flexibility at cost of performance

Choose what is better for Your project.

PS. If you want to do it on DB side, IMHO PostgreSQL i better choice than MySQL



98.9k

You might want to use virtual foreign keys instead of native foreign keys when:

  • You want to handle better errors related to foreign key integrity in the application layer rather than directly in the database
  • You want to speed up the application by checking foreign keys against a cached results in memcached or redis instead of the relational database server (performance)
  • You have models that map to tables in different servers or database systems without concerns on data integrity (scalability)


10.4k

You want to speed up the application by checking foreign keys against a cached results in memcached or redis instead of the relational database server (performance)

That's risky, what if DB is changed externally?



98.9k
in reply to Tomasz Zadora

Yes, you have to design a caching strategy only on those tables that rarely change and implement the right purge when needed

You want to speed up the application by checking foreign keys against a cached results in memcached or redis instead of the relational database server (performance)

That's risky, what if DB is changed externally?



2.3k

Makes me think YAGNI. If you are experiencing application performance issues due to your database implementing referential integrity, you probably have bigger issues to deal with.

Database are designed to enforce relations, do joins, optimize, yadda yadda. While you can certainly fall into anti-patterns and abuse features, definitely use proper, consistent table/column naming, add proper indexes and foreign key relations, etc.

Personally I have found that I tend to use mostly just the query builder feature of ORM libraries instead of string concat with lots of if statements to build a query.

Dont design your database around your application programming language limitations if you can help it. You dont have to be a purist either though.