Page 1 of 2
InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Tue Dec 16, 2008 11:07 am
by sergeidave
Hi,
I'm kind of new about using MySQL and databases in general. I want to practice web development by creating a website that will have social networking features. I'm positive that such a website will need a relational database, and if this is the case, I will need tables that relate to each other via foreign keys (is there another approach?).
So I'm wondering if MySQL is used by sooooooo many web developers, how come that the tables are defaulted to use MyISAM engines which do not support foreign keys? Are there any other methods of creating a relational database besides using foreign keys?
I'm sorry if I sound very novice (in fact I am novice), thank you so much for your help!!
Cheers,
David
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Tue Dec 16, 2008 3:57 pm
by jmut
I'd suggest to stick to InnoDB and use foreign keys, transactions etc.
Of course for tables where no relations is needed or no transactions required you can still use MyIsam engine.......mysql is perfectly working with mixture.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Tue Dec 16, 2008 4:26 pm
by Eran
Foreign keys are not required to build a relational database, only for "forcing" the relationships. Foreign keys impose integrity on your data - but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
There are other considerations between storage engines - MyISAM uses table-level locking vs. row-level locking in Innodb. Innodb supports transcations. MyISAM caches row counts, so count(*) is much faster than Innodb. MyISAM can have FULLTEXT indexes and so and so on. You better research a little about those storage engines if you are interested, but for simple application it would probably not make a difference.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Wed Dec 17, 2008 1:31 am
by sergeidave
Thanks a lot for your comments, guys!! It is a lot of help!
Best wishes,
David
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Wed Dec 17, 2008 5:46 pm
by califdon
Good summary, pytrin!
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 5:37 am
by VladSun
pytrin wrote:Foreign keys are not required to build a relational database, only for "forcing" the relationships. Foreign keys impose integrity on your data - but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
I do not agree (in general).
You can have several applications (web based, desktop, services...) which use a single DB. So, the DB-side "forced relationships" will greatly help you to "force" the developers of all of these applications to use them and will keep your DB clear from "bogus records".
There are, indeed, restrictions defined in each application but they should be business logic restrictions, not DB constraints.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 10:37 am
by Eran
they should be business logic restrictions, not DB constraints
That's exactly my point. Foreign keys implement "hard" constraints, while in application code you can be much more flexible. I had many instances I had to dance around some particularly annoying database constraint that I was forced to work with instead of having it in the application code.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 12:59 pm
by VladSun
A foreign key constraint means that the record doesn't make sense if there is no corresponding record. It doesn't depend on business logic, but on proper DB design.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 1:12 pm
by Eran
I said:
but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
In some cases there a foreign key might be put to good use. In my experience with web development,
most of the time that is not the case.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 1:41 pm
by califdon
I think we're getting into the religion of database design now. I sort of agree with both of you. I, too, have encountered instances where I found it necessary to depart from pure database design principles in order to get an application working within a reasonable time/effort/money, and after evaluating the potential problems that might arise. I am a strong proponent of proper design, normalization, etc. and you may have seen some of the tirades I've posted here. But a competent developer who understands the consequences of breaking the rules must sometimes do so in the name of practicality.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 2:00 pm
by VladSun
pytrin wrote:I said:
but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
In some cases there a foreign key might be put to good use. In my experience with web development,
most of the time that is not the case.
I am referring to :
pytrin wrote:Foreign keys implement "hard" constraints, while in application code you can be much more flexible. I had many instances I had to dance around some particularly annoying database constraint that I was forced to work with instead of having it in the application code.
where you explain that foreign keys are "annoying database constraints", and I argue that if application logic doesn't conform DB constraints then there is something really wrong with one or both of the designs (app or db)...
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 2:04 pm
by Eran
I iterated some experiences I had in which a database design was handed to me that included overuse of foreign keys constraints. Working with those was really annoying.
I didn't say that all foreign keys are annoying... and I don't consider blatant use of foreign keys "proper database design". Proper database design is using all the tools at your disposal in an appropriate manner.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 2:12 pm
by VladSun
Still can't agree.
If one can use any of the constraints (foregn keys, unique etc.), one should use it. Also using transactions together with constraints is even better.
It will prevent some logical errors and bugs in one's application to write bogus records in DB and will raise an error which will be very helpful for tracking such issues.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 2:37 pm
by Eran
That sounds like old-school database design. The database structure I mentioned before was designed by a former Oracle DBA (for many years), so I could understand where he came from.
In order to handle the errors you mentioned gracefully, the application needs to be aware of the those constraints in some way (otherwise a generic error message would be shown). This means your logic is repeated twice, which I consider a waste and requires more system changes when the logic changes.
Also, you can make logical mistakes with the database exactly as you would with the application, albeit changing constraints in the database is more difficult and harder to synchronize across environments (database schema revision is always a touchy subject).
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Posted: Thu Dec 18, 2008 3:11 pm
by VladSun
pytrin wrote:That sounds like old-school database design. The database structure I mentioned before was designed by a former Oracle DBA (for many years), so I could understand where he came from.
Interesting - what's the new-school DB design
pytrin wrote:In order to handle the errors you mentioned gracefully, the application needs to be aware of the those constraints in some way (otherwise a generic error message would be shown).
Yeah, some call this "testing"
pytrin wrote:This means your logic is repeated twice, which I consider a waste and requires more system changes when the logic changes.
1. Think of client-side filtering/validation and server-side filtering/validation

.
2. If you change so much of the logic then it's a bad design from the very beginning or user-requirements have not been collected properly and completely. In both cases refactoring would rarely work - you will need to redesign it and rewrite it.
pytrin wrote:Also, you can make logical mistakes with the database exactly as you would with the application
No, I can't. It's not true!
Also, a SQL schema create script file would usually contain < 1000 lines (with some SP defined < 2000-3000). Constraints definition lines number will be much less - lets say 10%. It is far far away from the number of code lines used in the application. So... calculate the possibility of errors.
pytrin wrote:albeit changing constraints in the database is more difficult and harder to synchronize across environments (database schema revision is always a touchy subject).
I must agree that keeping DB schemas on different production machines synchronized with the latest release one is a nasty thing to do

I've solved (almost) this problem by keeping track of SQL dump files into a CVS. After making a new release, CVS checkout and executing the SQL file is enough.