EF Code First数据库设计调整：替换User为Employee与Customer

现有数据库设计运行正常，但无法集成到已有系统——该系统用Employee（内部用户）和Customer（外部用户）两张表区分用户。当前设计中UserNotificationSettings的UserId同时作为主键和外键，必须修改为仅使用EmployeeId或CustomerId其中一个，如何调整数据库设计？

前置说明 #

假设现有系统的Employee和Customer实体结构如下（仅展示关联所需字段，其余字段保持原有设计）：

// 现有系统内部用户表
public class Employee
{
    public Guid Id { get; set; }
    public string Username { get; set; } = default!;
    // 其他原有字段...
    
    // 新增导航属性，关联通知设置与通知
    public ICollection<UserNotificationSettings> UserNotificationSettings { get; set; } = default!;
    public ICollection<Notification> Notifications { get; set; } = default!;
}

// 现有系统外部用户表
public class Customer
{
    public Guid Id { get; set; }
    public string Username { get; set; } = default!;
    // 其他原有字段...
    
    // 新增导航属性，关联通知设置与通知
    public ICollection<UserNotificationSettings> UserNotificationSettings { get; set; } = default!;
    public ICollection<Notification> Notifications { get; set; } = default!;
}

1. 修改核心实体 #

调整UserNotificationSettings #

移除原UserId与User导航属性，替换为可选的EmployeeId/CustomerId，新增独立主键Id：

public class UserNotificationSettings
{
    // 新增独立主键，替代原复合主键
    public Guid Id { get; set; }
    
    public bool IsInternalNotificationsEnabled { get; set; }
    public bool IsEmailNotificationsEnabled { get; set; }
    public bool IsSmsNotificationsEnabled { get; set; }
    public DeliveryOption EmailDeliveryOption { get; set; }
    public DeliveryOption SmsDeliveryOption { get; set; }
    
    // 可选外键：二选一，不可同时为空或同时存在
    public Guid? EmployeeId { get; set; }
    public Employee? Employee { get; set; }
    
    public Guid? CustomerId { get; set; }
    public Customer? Customer { get; set; }
    
    public Guid EventTypeId { get; set; }
    public EventType EventType { get; set; } = default!;

    public Guid? SoundId { get; set; }
    public Sound? Sound { get; set; }
}

调整Notification #

同样替换UserId为可选的EmployeeId/CustomerId：

public class Notification
{
    public Guid Id { get; set; }
    public bool Seen { get; set; }
    public DateTime CreatedAt { get; set; }
    
    // 可选外键：二选一，不可同时为空或同时存在
    public Guid? EmployeeId { get; set; }
    public Employee? Employee { get; set; }
    
    public Guid? CustomerId { get; set; }
    public Customer? Customer { get; set; }
    
    public Guid EventTypeId { get; set; }
    public EventType EventType { get; set; } = default!;
}

2. 配置EF Core映射规则 #

修改AppDbContext中的OnModelCreating方法，添加约束确保数据合法性：

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    // 替换原Users DbSet为现有系统的Employee和Customer
    public DbSet<Employee> Employees => Set<Employee>();
    public DbSet<Customer> Customers => Set<Customer>();
    
    public DbSet<UserNotificationSettings> UserNotificationSettings => Set<UserNotificationSettings>();
    public DbSet<Sound> Sounds => Set<Sound>();
    public DbSet<EventType> EventTypes => Set<EventType>();
    public DbSet<Notification> Notifications => Set<Notification>();
    public DbSet<GlobalNotificationSettings> GlobalNotificationSettings => Set<GlobalNotificationSettings>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置UserNotificationSettings
        modelBuilder.Entity<UserNotificationSettings>(entity =>
        {
            // 唯一约束：同一用户（Employee/Customer）对同一EventType只能有一条设置
            entity.HasIndex(uns => new { uns.EmployeeId, uns.EventTypeId })
                  .IsUnique()
                  .HasFilter("[EmployeeId] IS NOT NULL");
            
            entity.HasIndex(uns => new { uns.CustomerId, uns.EventTypeId })
                  .IsUnique()
                  .HasFilter("[CustomerId] IS NOT NULL");
            
            // 互斥约束：确保仅关联一种用户类型
            entity.HasCheckConstraint("CK_UserNotificationSettings_UserType", 
                "(EmployeeId IS NOT NULL AND CustomerId IS NULL) OR (EmployeeId IS NULL AND CustomerId IS NOT NULL)");
            
            // 外键与删除行为配置
            entity.HasOne(uns => uns.Employee)
                .WithMany(e => e.UserNotificationSettings)
                .HasForeignKey(uns => uns.EmployeeId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(uns => uns.Customer)
                .WithMany(c => c.UserNotificationSettings)
                .HasForeignKey(uns => uns.CustomerId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(uns => uns.EventType)
                .WithMany(et => et.UserNotificationSettings)
                .HasForeignKey(uns => uns.EventTypeId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(uns => uns.Sound)
                .WithMany(s => s.UserNotificationSettings)
                .HasForeignKey(uns => uns.SoundId)
                .OnDelete(DeleteBehavior.SetNull);
        });
        
        // 配置Notification
        modelBuilder.Entity<Notification>(entity =>
        {
            // 修正原错误：移除把EventTypeId当主键的配置，保留Id作为主键
            
            // 互斥约束：确保仅关联一种用户类型
            entity.HasCheckConstraint("CK_Notification_UserType", 
                "(EmployeeId IS NOT NULL AND CustomerId IS NULL) OR (EmployeeId IS NULL AND CustomerId IS NOT NULL)");
            
            // 外键与删除行为配置
            entity.HasOne(n => n.Employee)
                .WithMany(e => e.Notifications)
                .HasForeignKey(n => n.EmployeeId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(n => n.Customer)
                .WithMany(c => c.Notifications)
                .HasForeignKey(n => n.CustomerId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(n => n.EventType)
                .WithMany(et => et.Notifications)
                .HasForeignKey(n => n.EventTypeId)
                .OnDelete(DeleteBehavior.Cascade);
        });
        
        // GlobalNotificationSettings配置保持不变
        modelBuilder.Entity<GlobalNotificationSettings>(entity =>
        {
            entity.HasKey(gns => gns.EventTypeId);

            entity.HasOne(gns => gns.EventType)
                .WithMany(et => et.GlobalNotificationSettings)
                .HasForeignKey(gns => gns.EventTypeId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

3. 关键约束说明 #

• 互斥约束：通过数据库检查约束CK_*强制每条记录仅关联Employee或Customer中的一个，避免数据混乱。
• 唯一约束：确保同一用户对同一事件类型的通知设置唯一，防止重复配置。
• 级联删除：保持原有逻辑，用户删除时自动清理其关联的通知设置与通知。
• 兼容性：直接对接现有系统的Employee和Customer表，无需修改原有系统的核心结构。

内容的提问来源于stack exchange，提问作者nop