CODEWITHSUNDEEP
.netsql-serverentity-frameworkazureazure-sql-database
Ebsan
Ebsan

Reputation: 767

How to create nested one:many relationships in Entity Framework?

So I've got a schema that was set up by our DBA and now I need to translate it to SQL Azure. I'm having some trouble trying to convert it over to the .NET backend we have for our Azure Mobile Service. Here's the schema:

Schema diagram

All of these are one-to-many relationships. So you'll notice that there are foreign keys that relate back multiple tables. For example in the Scenario table there is a foreign key to the projects table and areas table. Is this a good way to set up the database? Couldn't you just relate the tables to have pointers to their parent tables? For example in the Scenarios table, a FK to only SubAreas? At first I tried to create EntityData classes in my Azure Service like so:

public class SubArea : EntityData 
{
    public string SubAreaAttenuation {get; set;}
    ...
    ...
    public virtual Area Area {get; set;}

    public virtual ICollection<Scenario> Scenarios {get; set;}
}

Do I need explicit keys in SubArea for Area and Project? Or is a pointer back to Area enough? I feel like the DBA had these extra keys for a reason (possibly to make searching easier?) But I don't know enough about databases yet to really be sure.

Upvotes: 1

Views: 1170

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109137

Bad design, for two reasons:

  1. It's a bad decision to have meaningful values as primary keys. Your DBA should know that if a project name should change one day it will require table rebuilds of all involved tables because one can't just modify a primary key value.

  2. Your DBA should also know that this is not well-normalized design, because all these key values are redundant. The idea of normalization is to reduce redundancy. If an Area needs to know its Projects name it should join in the Project. RDBMS's are extremely well optimized for these kinds of back joins.

All these composite keys should be removed. The primary keys should be singular values that have no bearing on business logic, so they can be immutable (so-called surrogate keys - it's an academic privilege to consider these an anti pattern). It's a PIA to write joins with composite keys. Even with EF, that generates the queries for you, its much easier to have singular primary keys.

If any combination of fields should be unique, a unique index should be applied, possibly clustered. The primary key is clustered by default, but when designing the tables it is possible to choose another index as the clustered index. This is something to contemplate profoundly.

Upvotes: 2

Aravind
Aravind

Reputation: 4163

It is possible with EF6 code first approach. check this out http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application

Upvotes: 0

Related Questions