Problem With Database Design For MVC - 1 Table Per Model

Question

My biggest hurdle with getting started with an MVC framework has to do with with the 1 model to 1 DB table concept. To me it's overly simplistic and unrealistic for anything beyond a simple app. Yet, MVC is in use everywhere, including this awesome StackOverflow site. As is typical, all of the code examples and tutorials i come across are very simple and the 1 to 1 relationship works fine in those cases. But, what i'm looking for is a solid real world example of an MVC model that address table joins. In the case of stackoverflow, i would imagine a DB design with tables like Questions, Tags, Users, etc. In my DB design I may have a Question_Tag link table to to look up all tags associated with a given question. How does MVC deal with this?

Answer 1

I don't believe that there is anything about the MVC design pattern that states that there should be one database table per domain class. In fact, there is nothing about the MVC design pattern that even says that your model should be or has to be persisted in a relational database.

This is just the strategy that some popular frameworks (Ruby on Rails - maybe ASP.NET MVC too?) have taken on, for what appears to be convenience sake. But it is not a requirement of MVC. Spring MVC (for the Java world) has no inherent concept of how to map the model components to a database, in fact it's beauty is that it doesn't care - you just need to supply the model data to use, and where you get it from the MVC framework doesn't care about.

So in other words, you don't need to assume that the MVC pattern means that you have to use one database table per model component. Heck you don't even need to use a database, your model could come just as well from a flat file or web services (and what's great about MVC is that if you design your app properly, you could swap different data layer implementations in and out of your application and the View or Controller won't even know).

Answer 2

Maybe it's the active record concept that's confusing me. So in my model it sounds like it's ok to simply write sql join queries to get what i need. It just seemed like 1 model per table was a best practice to follow and I was trying to understand how some people implement this. Thanks for the feedback.

Answer 3

I think you are confusing MVC with ORM. ORMS bind you one to one with Database Tables and Objects.

What you need to do is send your Model objects from your MVC to a DAL layer that can populate them for you from SQL queries and return you the objects. Similarly you can send the populated objects with changes back to the DAL layer to be persisted.

This is a much cleaner and flexible design that you can use with the MVC pattern and you do not have to couple your objects to database tables, while using the join capabilities of SQL.

Answer 4

OK Ahmad that makes sense. But in all of the MVC examples I've seen, the model is something of a combined BLL/DAL. What you're talking about is keeping the Model as a pure BLL and creating a separate DAL. I thought one of the main features of using an existing mvc framework was to speed up development. If I'm going to have to create a DAL on top of MVC I may as well stick with my existing non-MVC development process of a creating pages with separate business logic and data access layers.