复杂的关系VS简单的表格实体框架codeFirst [英] Complicated relationships vs simple tables Entity Framework CodeFirst

问题描述

数据库设计的问题:)
难道聪明，使很多相互关联的表（正常化）的或者是更聪明的重复数据，使查询更简单？

Database design question :) Is it smarter to make a lot of interconnected tables (normalize) or is it smarter to duplicate data so queries are simpler?

下面是我的情况：

public class TransferRequest
{
    [Key]
    public int TransferRequestId { get; set; }

    public int By { get; set; }

    public int? For { 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; }
}

在此创建：

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; }
}

这正好为船员：

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 so on. You get the idea. Should I simplify the situation? This is getting messy to follow.

推荐答案

复制为路径腐败 - 如果你更新一个副本，而忘记 ¹ 更新其他，你不再有确定这两个副本是有效的好方法。您已经有效地损坏的数据。

Duplication is path to corruption - if you update one copy, and forget¹ to update the other, you no longer have a good way of determining which of these two copies is valid. You have effectively corrupted the data.

正常化的整个目的是除去这样的重复的，因此，降低的方式的数据可能被破坏的数量。而且，由于规范化是在数据模型层，它增加了数据库的能力的自动的来自车的客户保卫本身。

The whole goal of normalization is removal of such duplication, and therefore decrease of the number of ways data can be corrupted. And since normalization is at the data model level, it increases the capability of the database to autonomously "defend" itself from buggy clients.

性能 ² 表示几乎没有数据不正确，所以标准化是规范的（没有双关语意）。如果它做明智和以有限的方式，来解决一个非常大的性能问题（无法得到解决的否则），并且可以通过的测量的在重新$ p证明的性能优势$ psentative的数据的。

Performance² means little if data is incorrect, so normalization is the norm (no pun intended). Denormalization is considered acceptable only if it's done judiciously and in a limited way, to solve a very big performance problem (that cannot be solved otherwise), and you can prove the performance benefit by measuring on representative amounts of data.

在一言以蔽之：第一个的正常化，的然后的非规范化的如果的测量证明它

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

¹ 要注意的是在并发环境下，两个独立的客户端可能尝试更新不同的副本（重presenting同一条信息）并行，保持数据同步是非-不重要的。即使在一个单一的客户机环境，虫子的寿命。一个事实的

¹ Beware that in a concurrent environment, where two independent clients may try to update different copies (representing same piece of information) in parallel, keeping data synchronized is non-trivial. Even in a single-client environment, bugs are a fact of life.

² 或客户code简单，对这一问题。的

² Or simplicity of client code, for that matter.

这篇关于复杂的关系VS简单的表格实体框架codeFirst的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！