CODEWITHSUNDEEP
c#sql-serverinheritancerelational-databaseef-core-3.1
Developer688
Developer688

Reputation: 61

How to store 8 or more inherited .net type in relelational Database

I have one base Column class and other column types derived from this type but derived types may be increased lot more. I don't want to use the TPH approach because of the lots of unrelated column count. Other 2 approach (TPC and TPT) is also problematic because EF core not supported yet. What do you recommend storing this kind of information in RDBMS?

public class Column
{
    public string ColumnName { get; set; }
    public ColumnType ColumnType { get; set; }
    public string Description { get; set; }
    public bool IsRequired { get; set; }
}

public class HyperLinkColumn : Column
{
    public UrlFormatType UrlFormatType { get; set; }
}
public class YesNoColumn : Column
{
    public bool DefaultValue { get; set; }
}
public class DateTimeColumn : Column
{
    private DateTime _defaultValue;
    public DateTimeFormat DateTimeFormat { get; set; }
    public string DisplayFormat { get; set; }
    public bool IsValueDateTimeNow { get; set; }

    public DateTime DefaultValue
    {
        get => _defaultValue;
        set
        {
            if (IsValueDateTimeNow)
                _defaultValue = DateTime.Now;

            _defaultValue = value;
        }
    }
}
public class CurrencyColumn : NumberColumn
{
    public string CurrencyFormat { get; set; }
}
public class NumberColumn : NumericColumn
{
    public bool ShowLikePercentage { get; set; }
}
public abstract class NumericColumn : Column
{
    public int MinValue { get; set; }
    public int MaxValue { get; set; }
    public decimal DefaultValue { get; set; }
    public int NumberOfDecimalPlace { get; set; }
}
public class ChoiceColumn : Column
{
    public List<string> Options { get; set; }
    public ChoiceType ChoiceType { get; set; }
    public bool AllowFillInChoice { get; set; }
}
public class MultiLineOfTextColumn : Column
{
    public int NumberOfLineAllowed { get; set; }
    public TextType TextType { get; set; }
}
public class SingleLineTextColumn : Column
{
    public int MaxNumberOfCharacter { get; set; }
}

Upvotes: 1

Views: 59

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89191

What do you recommend storing this kind of information in RDBMS?

TPH or a JSON column with a Value Conversion.

The worst thing about TPH is not the null columns per se. It's knowing and enforcing which columns are applicable to which subtypes. And EF mostly fixes that, as the Entity model enforces which subtypes get which properties. EF Core has been slow to implement TPT precisely because TPH is the recommended model.

Upvotes: 1

J&#243;zef Podlecki
J&#243;zef Podlecki

Reputation: 11283

I don't want to use the TPH approach because of the lots of unrelated column count.

But data will be in one place and you avoid any joins.

What do you recommend storing this kind of information in RDBMS?

I saw metadata stored as XML or JSON in text column and that information was deserialized later in app. It wasnt that bad because data was cached in memory.

TPH looks fine to me though

Upvotes: 2

Related Questions