2

I am building an application that maintains an available cash balance for services purchased, funded by credit card or money order. Naturally, as a small startup, performance isn't much of an issue, since the volume won't be there in the beginning.

That said, my budget is tiny READ: open source for now. I would be a fool to use the traditional Z mainframe; oracle for that matter, so I am debating between innodb or myisam engine for mysql.

For most of my projects, I choose innodb. Could such be a viable option, for, say the first hypothetical 100K customers? Or myisam? Is table locking recommended for financial transactions?

FredTheWebGuy
  • 2,546
  • 3
  • 27
  • 34
  • 2
    Please don't use PHP if you can help it. http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/ – ricochet1k Apr 25 '12 at 02:27
  • The short answer is to use InnoDB if you use MySQL. It has better data integrity, and it will result in better performance in your situation. (My rule of thumb is to default to InnoDB unless I have a reason to use MyISAM, and as far as I can tell, you don't have a reason to use MyISAM.) – Corbin Apr 25 '12 at 02:28
  • 3
    @ricochet1k If you're going to state an opinion, at least try to support it. Right now you just look like a troll. – Corbin Apr 25 '12 at 02:29
  • @Corbin I was trying to find the URL. I don't like looking like a troll. – ricochet1k Apr 25 '12 at 02:29
  • 1
    @ricochet1k Much better now. It just annoys me when I see "M$ sucks!" or "PHP sucks!" type comments with no backing. Also, though one can easily argue that a different language may be preferable over PHP, people should probably not write financial software in a language they are not familiar with so for the time being, PHP is likely the best option. (Then again, one could easily argue that to become familiar with PHP takes a very, very long time :p) – Corbin Apr 25 '12 at 02:34
  • @Corbin Yes, I agree. Another site written in PHP handling my financial information scares me. – ricochet1k Apr 25 '12 at 02:35
  • @ricochet1k why not use php? I have worked on some pretty significant high-traffic financial applications that run on php, last I heard they seems to run fine. Furthermore, one of those apps is a rewrite of a Java project gone bad. Small start-ups should avoid complex scenarios, in my humble opinion and in Java/C++ world thing can get complicated fast when in the wrong hands. – Stephane Gosselin Apr 25 '12 at 02:51
  • @Corbin - MyIsAM is favored when you want speed. A blog, rss feeds, etc. *where data integrity is not so crucial* are perfect candidates for MyISAM. Just because data integrity is not built-in the DB engine does not mean it is to be avoided. You can ensure your integrity in your code, and MyISAM is actually considered quite robust. – Stephane Gosselin Apr 25 '12 at 02:54
  • @stefgosselin Read the article I linked in my first comment if you want to know why not PHP. I didn't say Java/C++ is a good idea either. Neither of them are good for web development, in my opinion, and I don't like C++ much in general. Something like Javascript with Node.js or even Python or Ruby is what I would choose if I could. – ricochet1k Apr 25 '12 at 02:56
  • @stefgosselin And by the way, there's no reason why good software can't be written in PHP. In fact, I work at a company that already uses it, although we are working on infrastructure changes that will allow us to incrementally replace it with something else, probably Node.js. A good programmer can write good code in any language, but that doesn't mean it's easy, cheap or fun to do so. – ricochet1k Apr 25 '12 at 03:03
  • @ricochet1k Ok. So the guy found a php quirk here and there. Of course, PHP has flaws. I still feel it is more accessible than other options, AND much easier to find developpers too. I love python myself, and even toyed with ruby a while back, but I have never even seen a single job offer in my area for Python or Ruby. – Stephane Gosselin Apr 25 '12 at 03:06
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10453/discussion-between-ricochet1k-and-stefgosselin) – ricochet1k Apr 25 '12 at 03:07
  • @stefgosselin I understand the pros and cons of MyISAM. I was speaking with specific reference to the question. In the situation the question outlined, MyISAM would have no advantages that would not be heavily outweighed by other down sides. – Corbin Apr 25 '12 at 03:12
  • @Corbin - In the question outlined, MyISAM is not what you should use. I think that is what my answer was about. Downvotes without explanation are not very constructive, I would of liked to know what is wrong in my answer, maybe learn something even. Got stuck with a totally worthless downvote. – Stephane Gosselin Apr 25 '12 at 03:28
  • @stefgosselin Not my downvote. I very rarely downvote answers, and when I do, I always explain. – Corbin Apr 25 '12 at 03:40
  • @richochet1k- This question was not asked to start a religious war. Any decent programmer knows that it's the programmer- not the language or tool used- that makes the application secure and performant. EVERYONE ELSE: Thank you very much for the constructive answers. – FredTheWebGuy Apr 25 '12 at 21:30

2 Answers2

2

Database Choice

I would vote for PostgreSQL http://www.postgresql.org/ its the closest you can have to Enterprise RDMS especially if you are coming form oracle

You can also consider MySQl http://www.mysql.com/

Engine Choice

If you choose MySQL then i would suggest you use innoDB over MyIsam because of Database Transaction support with is important for financial transaction

See :

http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm

PHP + MySQL transactions examples

I hope this helps

Community
  • 1
  • 1
Baba
  • 94,024
  • 28
  • 166
  • 217
  • Transactions! Yes! Innodb it is. For some reason or another I thought that there would be some inherent advantage to having table locking for financial transactions, (as with MyISAM), thus the need for my asking. Thank you, friend! – FredTheWebGuy Apr 25 '12 at 21:35
  • but innodb doesn't support table locking it only supports row locking – Ravinder Payal Apr 18 '16 at 15:33
0

When data integrity is of prime concern, InnoDB is the logical choice. Here are a few comparisons:

  • InnoDB is newer while MyISAM is older.

  • InnoDB is more complex while MyISAM is simpler.

  • InnoDB is more strict in data integrity while MyISAM is not as strict.

  • InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.

  • InnoDB has transactions while MyISAM does not.

  • InnoDB has foreign keys and relationship contraints while MyISAM does not.

  • InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.

  • MyISAM has full-text search index while InnoDB has not.

On the financial apps I have developped, the usual layout usually starts with everything on InnoDB, except maybe for tables doing logging stuff or similar tasks. The most important feature you need from InnoDB that MyISAM does not have is transactions. That is, multiple operations are treated as one. They all succeed or the transactions fails.

Once the application needs scaling, other non critical tables are converted to MyISAM and that helps somewhat especially if your app has a lot of traffic.

Hope that helps, cheers!

Stephane Gosselin
  • 9,030
  • 5
  • 42
  • 65
  • 1
    Don't know why you got downvoted, but I appreciate your feedback. I'm in the same boat as you, starting with innodb for most of my projects. It's versatile and fast enough to handle just about anything you can throw at it- great price, too- especially for bootstrappers such as myself! – FredTheWebGuy Apr 25 '12 at 21:37
  • Yes. But instead of pondering over the matter and writing two posts over it, mabye one could have upvoted my answer just to re-balance the offset, if it's not really wrong per se. ;) Just a thought. – Stephane Gosselin Apr 26 '12 at 08:08
  • 1
    No-can-do until I get 15 reputation, brah. A sincere "thank you" should suffice for now. – FredTheWebGuy Apr 26 '12 at 21:38
  • @DudeSolutions Don't worry about it, karma is over-rated anyhow a well worded thank-you is worth much more. Have a good day my friend. – Stephane Gosselin Apr 26 '12 at 22:13