Inheritance in DataModel

[dragonwell]

I have been trying to base my data-models (tables and relations) on my domain model (classes, subclasses, interfaces etc.). So far I think it has helped.

I find that having one table per class makes the most sense, even if it means more query-joins.

Now I have just noticed something. I can map my inheritance in one of two ways.

Example:
Abstract Class Event.
Concrete Class Meeting, Inherits Event.

Method 1

CREATE TABLE Event(
 EventID int identity, --PK
 EventDate datetime
)

CREATE TABLE Meeting(
 MeetingID int identity,--PK
 EventID int, --FK
 Location varchar(50)
)

Method 2

CREATE TABLE Event(
 EventID int identity, --PK
 EventDate datetime,
)

CREATE TABLE Meeting(
 EventID int, --PK, FK
 Location varchar(50)
)

The only difference is that in method two, the primary key from the root class table is also used as the primary key if the derived table class (since it's a 1:1 relationship anyway).

I would like to hear anyone's opinions on this - what is better, why, etc.

Thanks!

 

[theoxyde]

Since the instance can be identified uniquely by the EventID, it should be the only key. The MeetingID is not needed and is not in 3NF.

Third normal form (3NF): "All non-key fields are dependent on the key, the whole key, and nothing but the key, so help me Codd."

 

[djam]

>> I find that having one table per class makes the most sense, even if it means more query-joins.

If you talk to a DBA, they would tell you that your doing it all wrong. The most important thing about relational databases is that they have to be normalized. You shouldn't have 1:1 relationships. I believe the DBA will do this

CREATE TABLE Event(
ID int identity, --PK
Date datetime,
Location varchar(50),
Type varchar(50) --FK
)

CREATE TABLE EventType(
Type varchar(50) --PK
)

This is how the raw data should be recorded in the database. Because you are using Object Oriented Programming, there is now an impedance match which you will have to make to get the data from the database into your objects. This usually accounts for 20% more code.

Another way to do it is to use a Object Oriented Database. This way you can eliminate the impendance matching.


" ahhh computers, how they made our lives much simpler "

 

[dragonwell]

First of all, thanks for your input!

Now I will agree that mapping the entire inheritance heirarchy to a single table (and using a type column to indicate the subclass) is the simplest solution. In fact this seems intuitive at first. But something about it doesn't feel right to me. The issues that stand out are:
1. It wastes space. Do you store nulls or what in columns that aren't members of the entity's class?

2. What about when I need to add another class(es) to the inheritance, such as "Board Meeting" or "Staff Meeting" which are subclasses of Meeting? Sure I could simply add a new row to the Type column of the Event table, but now I don't have any relation between these two new types - there is nothing that indicates that "Board Meeting" and "Staff Meeting" are Meetings, only that they are types of Event. I need to be able to get all of the "Meetings", regardless of whether they are "Board Meetings" or "Staff Meetings".

So I am leaning more towards theoxyde's way - which begs the question - when adding a new table for a class such as "Board Meeting" (which has no distinction from regular meeting other than it's type), would you just add a one-column table like this:

CREATE TABLE BoardMeeting(
 EventID int, --PK, FK
)

?????

Or should the Meeting table use an "MeetingType" column, combining the two approaches???

Finally, using a object database is not an option.

Regards, d

 

[djam]

You are trying create a relational database to model an object oriented structure. A relational database isn't suppose to do that, it's suppose to contain the raw data in a normalized format.

Since this is your project and your the developer and DBA, you can do whatever you want. But if you work for an organization where there is a DBA, there is no way they would let you do this. They tend to reuse tables whenever possible. It makes it easier for them to manage.

Whenever your adding another table such as "BoardMeeting", there is only a 1:1 relationship, this is not advisable in relational databases.

If you don't need the extra fields in the Event table, just put null. If you have types such as "Board Meetings" and "Staff Meetings" and you want to retrieve all the Meetings, just use the "where Type LIKE 'Meetings'"

Having a EVENT table and a EVENTTYPE table will:
1) reduce development time later on because if there is a new type of event, you don't need to add another table, just create a new event type
2) You only need to query one table to get all the Meetings, in your design you will need to query two tables.
3) Performance is faster

It is always a hassel to map data from a relational database to an object oriented structure...



" ahhh computers, how they made our lives much simpler "

 

[dragonwell]

Thanks a lot. I am starting to see the light.

 

[theoxyde]

Using a flag field is an easy way to designate which subclass (if any) an instance belongs to. Use bitflags if it can be more than one subclass. I can't say I have ever had a problem mapping OO classes to tables in a RDBMS, but that may just be a product of my background.

As for how many tables to use, that depends on the subclass' attributes. Are there attribute of the subclass that aren't inherited from the base class? If so, then those items need to be in another table or your normalization is broken. If all of the attributes of the subclass are inherited other than "type", then they should all be stored with the base class' data.

 

[dragonwell]

Turns out my model was flawed, which explains why it was giving me such a hard time. I had make the mistake of assuming that because two entities shared some common properties, they must be derived from the same base.

Interestingly, I showed a model that mapped multiple classes to one table and allowed null fields to a DBA and he said that allowing nulls is usually a sign of a bad design. That's how I've always thought, too.

Best Regards,

David

 

[theoxyde]

There are some cases where NULL might actually be a valid value, but that is the exception and not the norm. It sounds like you have your problem solved?

 

[dragonwell]

>>It sounds like you have your problem solved?
Ya! For now

Thanks