establish relationship in mysql or let php take care of it?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jaad
Forum Commoner
Posts: 95
Joined: Fri Jan 03, 2014 5:30 am
Location: Vancouver Canada

establish relationship in mysql or let php take care of it?

Post by jaad »

Hello all,

I am somewhat new in writing codes in php and been wondering about something:

Every book I read and tutorial I follow, I never see any examples where relationships between tables are established in mysql. everything seems to happen while coding in php and joining when or where it is necessary. AM I better off not establishing relationships in mysql and deal with relationship while I write php codes or it better practice to reinforce relationship from mysql first?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: establish relationship in mysql or let php take care of

Post by Celauran »

I'm willing to bet a lot of the tutorials use the default MyISAM storage engine, which does not support foreign keys. InnoDB does support foreign key relationships (as well as transactions and other good stuff) and is really what you want to be using.
User avatar
jaad
Forum Commoner
Posts: 95
Joined: Fri Jan 03, 2014 5:30 am
Location: Vancouver Canada

Re: establish relationship in mysql or let php take care of

Post by jaad »

yes i used InnoDB. So you would recommend establishing all the relationships in mysql then and not count on php to keep referential integrity intact as a good design practice?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: establish relationship in mysql or let php take care of

Post by Celauran »

I like to establish foreign key relationships, cascading deletes, compound primary keys for join tables, etc. I design my database independently of my application. If you're going to be writing your own queries directly rather than leveraging an ORM, I think that's a good way to go. I have encountered some ORMs that don't like any or all of the above. So the answer, sadly, is that it depends largely on the tools you'll be working with.
User avatar
jaad
Forum Commoner
Posts: 95
Joined: Fri Jan 03, 2014 5:30 am
Location: Vancouver Canada

Re: establish relationship in mysql or let php take care of

Post by jaad »

Well I think I agree with you on that. I'm not that advanced in php but I can hold my own with databases. I;m not going to take any chances. I am going to establish all my relationships within the DB and if it comes a time when it cause a problem while I learn PHP I can always remove the relationship. thank you for your feed back.
Post Reply