CODEWITHSUNDEEP
databasedatabase-design
Joan Roca
Joan Roca

Reputation: 111

Multiple inheritance modeling in Dbs

I'm modeling a system where there are Presents. Those presents can be presents you Offer or presents you Receive. Also, those presents can be Single or Multiple and, finally they can also be of a type, like Toys, Food, Trips or Other.

So, I thought of :

Presents --> Toys --> Offer   --> Single
         |        |           --> Multiple
         |        --> Receive --> Single
         |                    --> Multiple
         --> Food --> Offer   --> Single
         |        |           --> Multiple
         |        --> Receive --> ...
         --> Trip --> ..
         --> Other --> ..

I found that this is the only solution to my system. All the "leafs" would be implemented in different tables, and thats because all the "leafs" can have different models, sometimes it will be one more attribute. Sometimes it will be more. So, its a triple inheritance, where the model of the Present class varies in it's attributes depending on three factors.

The thing is, that I found this solution to be very ugly, if you know what I mean. Thats why I wanna ask you guys your opinion on this matter.

Thanks in advance. Joan

Upvotes: 0

Views: 1017

Answers (2)

Brad
Brad

Reputation: 2320

There was a very similar question asked here....

How to model this multiple inheritance relationship with a RDBMS?

If you're using PostgreSQL, multiple inheritance is allowed.

 CREATE TABLE presents
 (
    gift_id INT,
    PRIMARY KEY(gift_id)
 );

 CREATE TABLE offer_receiver
 (
   is_offer BOOLEAN,
   PRIMARY KEY(is_offer)
 );

 CREATE TABLE present_quantity
 (
   qty_received INT,
   PRIMARY KEY(qty_received)
 );

 CREATE TABLE present_info
 (
   person_id INT,
   PRIMARY KEY(person_id)
 ) INHERITS(present, offer_receiver, present_quantity);

This article has a good explanation of when and how to use inheritance:

http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html

All that being said, it might be a good idea to rethink this design. This looks more like an object hierarchy than an ER model. Remember that modeling the data in a program might be parent->child, but modeling the data in a database may be backwards.

Upvotes: 0

emperorz
emperorz

Reputation: 427

Without knowing more, I can only speculate, but making some assumptions:

  1. There are concepts: Presents, Toys, Food, Trip, Other, Offer, Receive, Single, Multiple.
  2. Each of these concepts has a stable set of attributes.
  3. Currently you have 16 tables, each structured differently.

Your model could be described with 10 tables, one per group of stable attributes, plus one link table that pulls them together.

The link table would need sparse columns i.e. you would have null in three out of four reference columns for (toys, food, trip, other), so this is not normalised, but a bit more like a data-warehouse schema.

The fact table would look a little like (omitting the column types):

gift_id not null, //primary key
present_id not null, // always have one of these
toy_id null, //refers to a row in the toy table
food_id null, // refers to a row in the food table...and so on below
trip_id null,
other_id null,
offer_id null,
receive_id null,
single_id null,
multiple_id null

It is still a little unpleasant in that this fact table may require additional columns as new varieties of thing are needed, but adding columns that are nullable is a very safe thing to do.

Upvotes: 1

Related Questions