Hi - I want to write a web based app with a database backend for my wife who works in the recruitment industry. I was wondering whether I should use InnoDB or MyISAM for my tables.

MyISAM seems to have the ability to do a full text search while innodb does not. Innodb has the ability to maintain referential integrity while MyISAM does not.

Which of these is more important? I was thinking that i could maintain referential integrity in myisam by checking that foreign key values being inserted really do exist via my php code. However, there seems to be no workaround for the lack of full text search in InnoDB.

What are you thoughts? Please note that I really am new to databases - this will be my first ever database project. I'll post my database design seperately for feedback

that's up to you

as you point out, you can do the relational integrity checking with application code

however, it's hard to do fulltext searching with application code

how badly do you need to do fulltext searching? have you considered doing LIKE comparisons only?

well the full text search was going to be carried out on some free form fields where she can write notes for each discussion she has with a client. I was thinking maybe i can make the whole database in innodb and then just make the freeform text stuff in a sepearte table which is myisam..

In my opinion, that is the ideal approach. You get the integrity offered by InnoDB where you really need it, and you get the "flat file" features offered by MyISAM where you need them... Using the best features of the different storage engines to get the solution that best suits your needs seems to be as good as you can get to me!

-PatP

Perfect! Thanks so much for taking the time to reply.

I hope i see you in the thread im going to make asking if my database design is sane I'm just going to spend another day making sure I have checked everything in triplicate before I post it up.

triplicate eh? I thought you said your wife worked in recruiting not the government. ;-)

When you might consider using MyISAM after all and would like to implement referential integrity, don't do that in the php code of the webpages. Write some triggers that do that for you, otherwise you might end up writing that same php code several times. But I agree that the InnoDB with one MyISAM for fulltext search is the way to go.

Gr,
Yveau