Using MySQL with MVC

Hi folks, hope someone can help...

I've just made the jump over to MVC from webforms and was hoping someone could give me a pointer on the best-practices to follow when using MySQL.

My app's going to be doing a lot of database interactions so unless I do things properly I fear I could end up littering the app with database connections. My best approach so far is to create a class with the db connections and get my models to inherit from that - although that doesn't feel very MVC to me. Does this sound like a good approach?

I have no problem with the syntax or anything like that, it's just the design philosophy behind it.

(I'm still a bit new to MVC so forgive me if my terminology is wrong - or if none of this makes sense!)

Thanks!

there's two school of thoughts... 1) since the data originates in the model, the model should be in charge of opening the data source (db, text file, what ever), getting the data and closing it. The other camp says 2) the controller should open the data source, pass a reference to it to the model where the model will then get the data. The controller would then be incharge of closing it too.

there's advantages and disadvantages to both ... if the model connects to the data source, then if that source changes, only the model needs to be changed... it isolates changes... but the down side is that if you have multiple models, it could result in multiple opening and closing hits against the data source. That's where the controller method comes in... you open the connection once, your models get their data, then the connection is only closed once. But if you change one model from being db-based to text file based ... you have to make changes to the model and the controller.

In short, it's the subject of some heated debates... I've done it both ways and usually boils down to either personal preference or need-based, and going with what works.

If it helps... rather than creating a base class to inherit from, a third option (and is one I use from time to time too) is to create a helper db connections class. then you can use the methods from either model or controller... and now your models are no longer db-based and become more source agnostic.

-tg

Each of your points make a lot of sense! Given the great way you've articulated it I'd be more inclined to let the model perform the opening and closing, albeit with the trade-off you mention.

Thanks very much for your answer tg - I found it really useful.