Tutorial :How do I represent object classification hierarchy in a RDBMS



Question:

My data, IF it would be represented by objects, it would look like:

abstract class A{     int a;     int b;     string c;  }    class B inherits A{    string D;  }    class C inherits A{    int e;    int f;  }  

My question: Do I create a separate table for entities B and C, Or do I create one main table, and for each entity type I do different joins to fetch the data.
In the real world, I will have around 15 similar fields for all entities, and about 1-3 unique field for each entity.
I expect a max of 100K records.

Any insights?


Solution:1

You'll find no shortage of opinions on this topic. Many people advocate concrete table inheritance, where (as you describe in your first option) you define the data explicitly in each table.

Given your second statement, I would not recommend this pattern. I would go with the idea of having a "main" table representing your parent and auxiliary tables representing the children. You may want to include a type identifier of some kind in your main table in order to indicate what sort of entity it is, but this may or may not be necessary. At a minimum, you need something like...

tableA  (      ID (primary),      A,      B,      C  )    tableB  (      ID (primary and foreign->table_A),      D  )    tableC  (      ID (primary and foreign->table_A),      E,      F  )  


Solution:2

table_a contains all similar fields,table_B and table_c contain the unique fields

table_A     PKa     a int     b int     c string      table_B    PKb    FKa    D string      table_C    PKc    FKa    e int    f int  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »