C# MVC Code First Complex Model

Question

I have one table "Adverts" which stores basic info about adverts (eg: Name, Excerpt, Creation date...), and I need to store more detailed info in a separate table, But, here's my problem. Adverts can be different by type (sell, buy, rent, ...), category (residential, commercial, ...), so, detailed info is also different (eg: Commercial Advert don't need kitchen area property). I want to make few models which will describe detailed info for specific type or category

Here's my Adverts model:

[Table("Adverts_Adverts")]
public class Advert {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid AdvertId { get; set; }

    public virtual Metadata Metadata { get; set; }

    [Required]
    [DataType(DataType.Text)]
    public String Name { get; set; }

    [DataType(DataType.Html), AllowHtml]
    public String Content { get; set; }

    [ForeignKey("Section")]
    public Guid SectionId { get; set; }
    public virtual Section Section { get; set; }

    [ForeignKey("Category")]
    public Guid CategoryId { get; set; }
    public virtual Category Category { get; set; }

    [ForeignKey("Type")]
    public Guid TypeId { get; set; }
    public virtual Type Type { get; set; }

    public Decimal Price { get; set; }

    [DataType("Enum")]
    public Currency Currency { get; set; }

    [ForeignKey("Details")]
    public Guid DetailsId { get; set; }
    public virtual ?????????? Details { get; set; }

    [ForeignKey("User")]
    public String UserId { get; set; }
    public virtual User User { get; set; }

    [ReadOnly(true)]
    [DataType(DataType.DateTime)]
    public DateTime Added { get; set; }

    [ReadOnly(true)]
    [DataType(DataType.DateTime)]
    public DateTime Updated { get; set; }

    public Int32 Views { get; set; }

    [ReadOnly(true)]
    public Status Status { get; set; }

    ...
}

here's my detailed info model for residential adverts:

[Table("Adverts_Details")]
public class ResidentialDetails {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid DetailsId { get; set; }

    [ForeignKey("Advert")]
    public Guid AdvertId { get; set; }
    public virtual Advert Advert { get; set; }

    [Required]
    public Int32 Storeys { get; set; }

    [Required]
    public Int32 Floor { get; set; }

    [Required]
    public Int32 Rooms { get; set; }

    [Required]
    public Decimal TotalArea { get; set; }

    [Required]
    public Decimal LivingArea { get; set; }

    [Required]
    public Decimal KitchenArea { get; set; }

    ...
}

and this may be for commercial adverts:

[Table("Adverts_Details")]
public class CommercialDetails {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid DetailsId { get; set; }

    [ForeignKey("Advert")]
    public Guid AdvertId { get; set; }
    public virtual Advert Advert { get; set; }

    [Required]
    public Int32 OfficesCount { get; set; }

    [Required]
    public Int32 Floor { get; set; }

    [Required]
    public Decimal TotalArea { get; set; }

    ...
}

So, how can I access both, ResidentialDetails and CommercialDetails, data within advert's property "Details"?

(Thank in advance)

Answer 1

This is an architecture problem, which is hard to answer without a complete understanding of your business rules. I can give you some general advice that will hopefully help you along.

As much as possible, remove complexity. I'm not sure what a "kitchen area property" is, but can you generalize it at all? Based upon context, you can call it something different, use it differently, etc. but if it's just a text field, then you can repurpose it in other contexts. Maybe for a residential advert it's "kitchen area" while maybe for commercial it's "break room area". (I really have no idea what this property is for, but I'm just trying to make the point that the same property can have a similar but slightly different meaning in different contexts).

If you can't generalize, then you'll need to start working on inheritance strategies. Create an object graph. How are these types and categories of adverts related. How are they different. Which ones are supergroups of others, etc.? Again, I don't know anything about the business rules at play, but maybe you need classes like Advert, ResidentialAdvert : Advert and CommercialAdvert : Advert. Then, you can add additional properties to these subclasses as necessary.

You'll also need to decide on a relational strategy. By default, EF will implement simple inheritance as STI (single-table inheritance, aka table per hierarchy or TPH for short). In other words, with the classes above, you would end up with an Adverts table with a Discriminator column. The value for this column would be one of "Advert", "ResidentalAdvert", or "CommercialAdvert", indicating which class should be instantiated, but all of the columns for all of the subclasses would reside in the same table. The benefit is that no joins are necessary, but the detriment is that all additional columns on your subclasses must be nullable or have default values. Other possible strategies would include, table per type (TPT), a compositional strategry, or table per concrete type (TPC), where every subtype gets its own unique table with all the fields from all supertypes.