Encapsulation or abstraction in database design

Question

In my primary language (C#) its very easy to abstract data classes. For example:

public interface IFruit
{
    public TimeSpan GrowthTime { get; set; }
    public string Color { get; set; }

}

public class Apple : IFruit
{

    public TimeSpan GrowthTime
    {
        get
        {
            throw new NotImplementedException();
        }
        set
        {
            throw new NotImplementedException();
        }
    }

    public string Color
    {
        get
        {
            throw new NotImplementedException();
        }
        set
        {
            throw new NotImplementedException();
        }
    }
}

How would you go implementing this type of functionality in a database design? Let's say I have a table named Bulletins which can hold many different types of bulletins whether they be Announcments, Ads, or Events.

I thought about creating a Bulletins table and a separate table for each of the above classes with an FK column for each contained within the Bulletins table; however, this will not work since FK's are mandatory (or rather bad practice if nullifying them) and one Bulletin post can never be more than one of the classes listed above.

I could make separate tables for each of them but the user's external model view is only that of the Bulletins. To them they see a bulletin that can each be a different type, not a separate entity each. Any suggestions?

score 1 · Answer 1 · edited May 23 '17 at 12:21

FK's are mandatory (or rather bad practice if nullifying them)

That's actually not true. It is perfectly legitimate to have a NULL foreign key to model "optional" parent. Though you wouldn't necessarily need that for inheritance.

Inheritance¹ is not natively supported in today's relational databases, so there is "impedance mismatch" between OOP and database that needs to be overcome, and there are couple of ways to do it, none of them ideal.

My default approach would probably be "class per table" with enforcement of both exclusivity and presence of children at the application level, and only look at other approaches if there is a specific reason for that.

¹ Also known in database modeling as "category" or "generalization hierarchy" or "subclassing".

Encapsulation or abstraction in database design

1 Answers1