EF Code First database schema: Represent User entity by two tables where either one should be used

Question

I modelled the database this way and while it works just fine, I'm unable to integrate it into an existing system because the User is in that system is representated by two tables: Employee and Customer. There are internal (Employee) and external (Customer) to the system users. If you look at the schema I did below, you will see that UserId is PK and FK in UserNotificationSettings. This has to change.

The question is what should I change in the database design below so that the UserId becomes both EmployeeId and CustomerId. By the way, either EmployeeId or CustomerId should be used, but not both.

public class EventType
{
    public Guid Id { get; set; }
    public string Name { get; set; } = default!;

    public ICollection<UserNotificationSettings> UserNotificationSettings { get; set; } = default!;
    public ICollection<GlobalNotificationSettings> GlobalNotificationSettings { get; set; } = default!;
    public ICollection<Notification> Notifications { get; set; } = default!;
}

public class GlobalNotificationSettings
{
    public bool IsInternalNotificationsEnabled { get; set; }
    public bool IsEmailNotificationsEnabled { get; set; }
    public bool IsSmsNotificationsEnabled { get; set; }
    
    public Guid EventTypeId { get; set; }
    public EventType EventType { get; set; } = default!;
}

public class Notification
{
    public Guid Id { get; set; }
    public bool Seen { get; set; }
    public DateTime CreatedAt { get; set; }
    
    public Guid UserId { get; set; }
    public User User { get; set; } = default!;
    
    public Guid EventTypeId { get; set; }
    public EventType EventType { get; set; } = default!;
}

public class Sound
{
    public Guid Id { get; set; }
    public string Name { get; set; } = default!;
    public string Url { get; set; } = default!;
    
    public ICollection<UserNotificationSettings> UserNotificationSettings { get; set; } = default!;
}

public class User
{
    public Guid Id { get; set; }
    public string Username { get; set; } = default!;
    public string Password { get; set; } = default!;
    public string Email { get; set; } = default!;
    public string Phone { get; set; } = default!;

    public ICollection<UserNotificationSettings> UserNotificationSettings { get; set; } = default!;
}

public class UserNotificationSettings
{
    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 UserId { get; set; }
    public User User { get; set; } = default!;
    
    public Guid EventTypeId { get; set; }
    public EventType EventType { get; set; } = default!;

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

public enum DeliveryOption
{
    Immediate,
    DailySummary
}

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

    public DbSet<User> Users => Set<User>();
    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)
    {
        modelBuilder.Entity<UserNotificationSettings>(entity =>
        {
            entity.HasKey(uns => new { uns.UserId, uns.EventTypeId });
            
            entity.HasOne(uns => uns.User)
                .WithMany(u => u.UserNotificationSettings)
                .HasForeignKey(uns => uns.UserId)
                .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);
        });
        
        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);
        });
        
        modelBuilder.Entity<Notification>(entity =>
        {
            entity.HasKey(n => n.EventTypeId);

            entity.HasOne(n => n.EventType)
                .WithMany(et => et.Notifications)
                .HasForeignKey(n => n.EventTypeId)
                .OnDelete(DeleteBehavior.Cascade);
            
            entity.HasOne(n => n.User)
                .WithMany(u => u.Notifications)
                .HasForeignKey(n => n.UserId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

Answer 1

Based on your question, you have a customer table and an employee table.

Customer
--------
Customer number   PK
Other customer information

Employee
--------
Employee number   PK
Other employee information

Now, you need to create multiple additional tables with the following structure.

Additional
----------
Number   PK
COE Flag   (Customer or employee)
Additional information

You write the SQL to join this table with the customer table this way. The syntax will vary depending on which relational database you're using.

SELECT Additional information, Customer information
FROM Additional, Customer
WHERE Additional informaton meets some criteria
AND Number = Customer Number
AND COE Flag = 'C'  

You write the SQL to join this table with the employee table this way. The syntax will vary depending on which relational database you're using.

SELECT Additional information, Employee information
FROM Additional, Employee
WHERE Additional informaton meets some criteria
AND Number = Employee Number
AND COE Flag = 'E'     

I don't know how to make the concept any clearer.