Mysql Complicated Relations

[radalin]

Hi,

I'm curious to learn how is mysql with relations. But I'm not talking about simple ones. I'm intended to learn how is it's perfomance when realtions get complicated (like a relation with b, b relation with c, c relation with d and go on...) and relation numbers and db size increase a lot, can I ecnounter any problems? Or any bugs can occur like broken relationships or nothing happens instead of an operation with relations.

What kind of problems can I encounter using mysql in a big project. What are the differences of InnoDB and MyISAM in a big project. What are they advantages. Why should I choose one of them?

Thanks.

[fenway]

That's a very complicated question. In general, complexity grows as a function of the number of relations. That is, you'll need to JOIN in more tables, and so on. MySQL has no "problem" with this -- you'll just have to be more careful about choosing (covering) indexes properly, keeping tables fixed-width, not NULL, etc. to keep queries running optimally.

As far as broken relationships, it's up to you -- InnoDB can enforce FK constaints, while MyISAM does not. There are arguments to be made for both; it really depends how your tables will be used (mostly reads vs writes).

Hope that helps a bit.

[radalin]

So the thing is up to me. How good I made my architecture. Well is there a tutorial or tips&tricks on how to make a good relational db.
And yes your post really helped a lot :) At least now I don't worry about my choice. Thanks :)

[fenway]

Any primer on database normalization is a good start -- you'd better have your tables in 2NF or better. How far to go really depends on your application. I know I'm saying that a lot, but it's true. Of course, any and all questions you have as they relate to your specific needs are always welcome.