Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Foreign key constraint and indexes use
  • Hi all,
    I was wondering what are the best practices for orm/database design.

    I checked ormauth package migrations and found that there are some foreign key constraints but only a few. I also found that there were no indexes at all on other foreign keys.

    So the questions are: Is there any reason ormauth is using only a few fk constraints? Should I create index on any fk (with explicit mysql constraint or not)? Can the oil generate command deal with constraints and indexes?

  • HarroHarro
    Accepted Answer
    Yes, simply because I don't like them. ;-)

    I know DBA's love them (they don't trust developers with "their" data, and they may have a point).

    But it's magic that happens outside of your application control (your constraint may cause lots of records to be deleted, which the ORM still has cached versions in memory. using them will crash your app), Also, the current DB layer is very MySQL centric, and DBUtil is that almost 100%.

    Which means that if constraints are defined in the migrations, you can never use Auth on postgresql, Oracle, or something else that PDO supports...

    Fuel v2 has platform drivers, so it's possible to write generic constraints that will translate to the correct SQL for your target platform.

    It will not remove the magic though. So I will still not like it. :-)

    You can create indexes where needed, just create a migration for it. Most people only have a limited amount of users in the system, so an index doesn't give you very much.

    I'm a supporter of "code first, optimize later", and "don't over optimize". So I never create any secondairy indexes until performance need to be improved, and it's proven that this index will do that (and not have performance hits elsewhere). Same goes for the INNODB vs MyISAM selection for a specific table.
  • Thanks for the quick answer and sharing your point of view. But if you don't like them why did you use them a little bit? I suppose it is for cascade delete support when using simpleauth but I am not 100% percent sure.
  • There is only a constraint definition for the Opauth session and sessionscopes tables, and it is there because Opauth requires it. It was directly copied from the Opauth installation instructions.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion