Best practices for normalizing Entity Framework CodeFirst models

Data and Databases
1

I’m working on an EF6 project and struggling with proper database normalization. My current setup has two main entities but the details entity breaks normalization rules and I want to fix this. I’m pretty new to both SQL Server and Entity Framework so I’m not sure about the best approach.

public class PlaceEntity
{
    [Key]
    public Guid PlaceId { get; set; }
    
    public string Title { get; set; }
    
    public string Info { get; set; }
    
    public DbGeography Location { get; set; }
    
    public PlaceDetailsEntity Details { get; set; }
}

public class PlaceDetailsEntity
{
    [Key, ForeignKey("Place")]
    public Guid PlaceId { get; set; }
    
    public string World { get; set; }
    
    public bool? IsMainCity { get; set; }
    
    public int Citizens { get; set; }
    
    public string StarSystem { get; set; }
    
    public string Region { get; set; }
    
    public PlaceEntity Place { get; set; }
}

What would be the recommended way to normalize this structure properly? Should I create separate tables for different types of locations or use inheritance? Any guidance would be helpful since I need to maintain good query performance.

2

depends what you’re building, but i’d use table per hierarchy inheritance for different place types - like cityplace, spacestationplace, etc. queries stay simple and ef handles the discriminator column for you. just create a placetype enum and inherit your classes from a base placeentity.

3

Your model has a significant normalization issue; you are repeating strings like ‘World’, ‘StarSystem’, and ‘Region’ directly within the details table. It’s advisable to separate these into distinct reference tables that include their own IDs, and then use foreign keys within the PlaceDetailsEntity instead of using string values. Therefore, create tables for WorldEntity, StarSystemEntity, and RegionEntity, assign them IDs, and reference those IDs from your details table. This approach eliminates data duplication and maintains consistency. You can establish these relationships in EF Code First using either data annotations or the fluent API. Your current one-to-one relationship between PlaceEntity and PlaceDetailsEntity is appropriate, as it effectively separates core properties from extended attributes for improved performance.

4

What data are you putting in those World and StarSystem fields? Building a game or sci-fi app? That’d change how I’d normalize this - are these unique values or will multiple places share the same world/system?