database design - Complicated relationships vs simple tables Entity Framework CodeFirst -

database design question :) smarter make lot of interconnected tables (normalize) or smarter duplicate data queries simpler?

here situation:

public class transferrequest {     [key]     public int transferrequestid { get; set; }      public int { get; set; }      public int? { get; set; }      public int personid { get; set; }     public virtual person person { get; set; }      [foreignkey("transfer")]     public int? existingtransferid { get; set; }     public virtual transfer existingtransfer { get; set; }      [required]     [range(1, 999)]     public int pax { get; set; }      [range(0, 999)]     public int paxchild { get; set; }      [range(0, 999)]     public int paxinfant { get; set; }      public int startportid { get; set; }     public virtual port startport { get; set; }      public int endportid { get; set; }     public virtual port endport { get; set; }      [required]     [datatype(datatype.datetime)]     [uihint("pickuptimepicker")]     [display(name = "pickup time"), displayformat(dataformatstring = "{0:dd.mm.yyyy hh:mm}")]     public datetime pickuptime { get; set; }      public bool cargo { get; set; }      public string cargodescription { get; set; }      public int status { get; set; }      [foreignkey("transfer")]     public int? transferid { get; set; }     public virtual transfer transfer { get; set; } } 

after created:

public class transfer {     public transfer()     {         crew = new list<crewontransfer>();         transferpoints = new list<transferpoint>();         transferrequests = new list<transferrequest>();     }      [key]     public int transferid { get; set; }      [foreignkey("ship")]     public int shipid { get; set; }     public virtual ship ship { get; set; }      [foreignkey("shipcrew")]     public int captainid { get; set; }     public virtual shipcrew shipcrew { get; set; }      public virtual icollection<crewontransfer> crew { get; set; }      public virtual icollection<transferpoint> transferpoints { get; set; }      public virtual icollection<transferrequest> transferrequests { get; set; } } 

that goes on crew:

public class crewontransfer {     [key]     public int crewontransferid { get; set; }      [foreignkey("shipcrew")]     public int shipcrewid { get; set; }     public virtual shipcrew shipcrew { get; set; }      [foreignkey("transfer")]     public int transferid { get; set; }     public virtual transfer transfer { get; set; } } 

and on. idea. should simplify situation? getting messy follow.

duplication path corruption - if update 1 copy, , forget¹ update other, no longer have way of determining of these 2 copies valid. have corrupted data.

the whole goal of normalization removal of such duplication, , therefore decrease of number of ways data can corrupted. , since normalization @ data model level, increases capability of database autonomously "defend" buggy clients.

performance² means little if data incorrect, normalization norm (no pun intended). denormalization considered acceptable if it's done judiciously , in limited way, solve big performance problem (that cannot solved otherwise), , can prove performance benefit measuring on representative amounts of data.

in nutshell: first normalize, then denormalize if measurements justify it.

---

¹ beware in concurrent environment, 2 independent clients may try update different copies (representing same piece of information) in parallel, keeping data synchronized non-trivial. in single-client environment, bugs fact of life.

² or simplicity of client code, matter.