在关系数据库的一个表上哪一个应该首选1：1,0或可空字段？

I use Azure Sql and Entity-Framework Code First.

我使用Azure Sql和Entity-Framework Code First。

I have Transaction Table. There are 4 types which are buy/sell, withdrawal, deposit and fee.

我有交易表。有4种类型是买/卖,提款,存款和费用。

For example, I need DepositCode for deposit transactions but this column will be null for others. Also I need ItemPrice and ItemAmount for buy/sell but it will be null for other types.

例如,我需要存款交易的DepositCode,但其他人的此列将为空。我还需要ItemPrice和ItemAmount进行买/卖,但对于其他类型它将为null。

Example Transaction Table

示例事务表

 public class Transaction
    {
        public long Id { get; set; }
        public decimal Amount { get; set; }
        public int Type { get; set; }
        public string DepositCode { get; set; }
        public decimal? ItemPrice { get; set; }
        public decimal? ItemAmount { get; set; }
        public string WithdrawalIban { get; set; }
    }

or

 public class Transaction
    {
        public long Id { get; set; }
        public decimal Amount { get; set; }
        public int Type { get; set; }
        public DepositTransaction DepositTransaction { get; set; }
        public WithdrawalTransaction BuyAndSellTransaction { get; set; }
        public WithdrawalTransaction WithdrawalTransaction { get;set; }
    }


 public class DepositTransaction
    {
        public long Id { get; set; }
        public Transaction Transaction { get; set; }
        public string DepositCode { get; set; }
    }


 public class WithdrawalTransaction
    {
        public long Id { get; set; }
        public Transaction Transaction { get; set; }
        public string WithdrawalIban { get; set; }
    }


 public class BuySellTransaction
    {
        public long Id { get; set; }
        public Transaction Transaction { get; set; }
        public decimal ItemPrice { get; set; }
        public decimal ItemAmount { get; set; }
    }

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<WithdrawalTransaction>()
        .HasRequired(wt => wt.Transaction)
        .WithOptional(tr => tr.WithdrawalTransaction);

        modelBuilder.Entity<DepositTransaction>()
      .HasRequired(db => db.Transaction)
      .WithOptional(tr=> tr.DepositTransaction);

        modelBuilder.Entity<BuySellTransaction>()
      .HasRequired(bs => bs.Transaction)
      .WithOptional(tr => tr.BuySellTransaction);

    }

Which one should be preferred ?

应该首选哪一个?

2 个解决方案

#1

I prefer nullable fields when possible, which is the implementation in table per class hierarchy mappings. Using this approach EF will store all transactions objects in one table and will use a discriminator column to manage type loading for you. That said I would refactor your model as follows:

我尽可能使用可空字段,这是每个类层次结构映射在表中的实现。使用这种方法,EF会将所有事务对象存储在一个表中,并使用一个鉴别器列来管理类型加载。那说我会按如下方式重构你的模型:

public abstract class Transaction
{
    public long Id { get; set; }
    public DateTime TransactionDate { get; set; }
}


public class DepositTransaction : Transaction
{
    public string DepositCode { get; set; }
}


public class WithdrawalTransaction : Transaction
{
    public string WithdrawalIban { get; set; }
}


public class BuySellTransaction : Transaction
{
    public decimal ItemPrice { get; set; }
    public decimal ItemAmount { get; set; }
}

#2

You actually have only three transactions: buy/sell, deposit/withdrawal, fee. The difference between buy and sell is that the amount is positive for buy and negative for sell. Same goes for deposit and withdrawal.

您实际上只有三个交易:买/卖,存款/取款,费用。买入和卖出之间的区别在于买入为正,卖出为负。存款和取款也是如此。

create table Transactions(
    ID         bigint identity primary key,
    XType      char( 1 ) not null, -- 'B': buy/sell, 'D': dep/with, 'F': fee
    Amount     money not null,
    constraint CK_Transaction_Type check( XType in( 'B', 'D', 'F' ),
    constraint UQ_Transaction_Type unique( ID, XType )
);

The other tables (or subtables if you want to think of them as such) would look like this:

其他表(或子表,如果你想将它们视为这样)将如下所示:

create table BuySellTrans(
    TransID    bigint not null,
    TransType  char( 1 ) not null,
    ...,   -- other info regarding purchase or sale
    constraint CK_BuySellTrans_BuySellType check( TransType = 'B' )
    constraint FK_BuySellTrans_Trans foreign key( TransID, TransType )
        references Transactions( ID, XType )
);

As for the code, the best way may be to have an abstract super class Transaction with subclasses BuySell, DepositWithdrawal and Fee. I would also provide some convenient views named, coincidentally, BuySell, DepositWithdrawal and Fee. These views would provide a joined dataset of the Transactions table with the appropriate subtable. Triggers on the views (your system allowing) would simplify the application code greatly as each subclass would query and manipulate only through the views. The code wouldn't even need to know about the physical layout in the database.

至于代码,最好的方法可能是拥有子类BuySell,DepositWithdrawal和Fee的抽象超类Transaction。我还会提供一些方便的视图,巧合的是,BuySell,DepositWithdrawal和Fee。这些视图将提供Transactions表的连接数据集以及相应的子表。视图上的触发器(允许您的系统)将大大简化应用程序代码,因为每个子类只能通过视图进行查询和操作。代码甚至不需要知道数据库中的物理布局。