Following up from a series of previous posts, here is the granddaddy summary of all time.
One important thing to remember is that a database is not necessarily just a backing data store for an application (in isolation). Other applications and process could eventually require access to the database, especially in large or “enterprise” databases (or applications) and especially given enough time.
It’s important to consider:
Also, some design consideration is in order. Are you tuning for faster selects, or faster writes? One data access design might perform better than another.
I’m not stating that there is a single silver bullet, but what I do caution is that any data access design pattern requires “big picture” thought – will it address today’s concerns and what you can reasonably predict might be tomorrow’s needs?
Also, will you be providing an external API or some framework for consistent data access? Will it be exposed directly or indirectly?
There’s a place, I think, for both the Entity Framework/LINQ to SQL, traditional Stored Procedures and other tools like NHibernate (etc), but you should justify and rationalise the choice of technology first, and try to ensure it is appropriate for the present and future needs.
Lastly, the big one: maintainability. Some of the template driven solutions offer you some decent wins in being able to regenerate a DAL after schema changes, over others (like hand written stored procedures). It’s worth weighing up the productivity gains vs the disadvantages.
