How to model my database when using entity framework 4?

Question

Trying to wrap my head around the best approach in modelling a database when we are using Entity Framework 4 as the ORM layer. We are going to use asp.net mvc 2 for the application.

Is it worth trying to model using the class diagram modeller that comes with Visual Studio 2010 where you graphically configure your models into the EDMX file and then generate out the database structure?

I have run into a bunch of non trivial issues and for complex many to many mappings or multi primary key entities the answer is not that obvious even after poking around a while with the tools.

I figure its easy at this point to give up and start modelling the DB using real, working DB modelling tools and then try to generate out the EDMX from the database, rather than trying to do the model first approach.

Answer 1

It's really a matter of preference. If you are comfortable in SQL server that's probably the best place to start. But if you are more of a C# programmer, it's sometimes easier to start in the EDMX designer, make the model and then ask it to figure out what the database should look like.

Of course if you do go model first you'll still need to go in to SSMS and add indexes and maybe rename some FKs and tables more to your liking. Then you can bring the model back up to date with an Update from Model.

Modelling inheritance is also something you'll need to do in the designer, but again you can either do it in SSMS or in the EDMX designer. For inheritance I mostly prefer SQL first because there is the explicit decision as to what form of inheritance you want - per hierarchy, per class, or per concrete type.