Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an attribute obj_type that records the type of event. All the subtypes share the most important attributes (represented on the main table) but a few of them (a minority) have aditional attributes that are represented in additional children tables.

I have to represent new relationships involving rows from the main table. For example record that an event of type A is in relation with an event of type B and an event of type C or D, or an event of type X or Y is in relation with an event of type Z.

Which one of the 2 schemas below is better? (is any of the two not advisable?)
The second duplicates some information but the constraint ensures the object ids in the relations are of the correct subtype.
It leaves me perplexed however to declare unique (obj_id,obj_type) since this is not a minimal key, since obj_id is already the primary key.
The first one doesn't duplicate data but doesn't perform any check on the correctness of event types participating in the relation.

SCHEMA 1:

create table supertype(  
obj_id varchar2(30) primary key,  
obj_type varchar2(1) check (obj_type in ('A','B','C','D',..)),  
attribute1  varchar2(1),  
attribute2  varchar2(1),  
attribute3  varchar2(1),
...  
attributen  varchar2(1)
)  


create table relation_A_with_B_and_cd(  
obj_id_subtype_A varchar2(30) primary key  references supertype(obj_id),  
obj_id_reference_to_subtype_B varchar2(30) references supertype(obj_id),  
obj_id_reference_to_subtype_C_or_D varchar2(30) references supertype(obj_id),  
attribute_det_a1  varchar2(1),  
attribute_det_a2  varchar2(1),
)   

create table relation_XY_with_Z(  
obj_id_subtype_X_or_Y varchar2(30) primary key  references supertype(obj_id),  
obj_id_reference_to_subtype_Z varchar2(30) references supertype(obj_id),  
attribute_1 varchar2(1),  
attribute_2  varchar2(1),
)   

----------------------
SCHEMA 2: 


create table supertype(  
obj_id varchar2(30) primary key,  
obj_type varchar2(1) check (obj_type in ('A','B','C','D',...)),  
attribute1  varchar2(1),  
attribute2  varchar2(1),  
attribute3  varchar2(1)  
)  
alter table supertype add constraint UQ_type unique(obj_id,obj_type)   

create table relation_A_with_B_and_cd(  
obj_id_subtype_A varchar2(30) primary key ,  
obj_type_subtype_A varchar2(1) check (obj_type in ('A')),  
obj_id_reference_to_subtype_B varchar2(30) ,  
obj_type_reference_to_subtype_B varchar2(1) check (obj_type_reference_to_subtype_B in ('B')),  
obj_id_reference_to_subtype_CD varchar2(30) ,  
obj_type reference_to_subtype_CD varchar2(1) check (obj_type reference_to_subtype_CD in ('C','D')),   
attribute_det_a1  varchar2(1),  
attribute_det_a2  varchar2(1),  
constraint fk_subtype_a foreign key (obj_id_subtype_A,obj_type_subtype_A) references supertype  (obj_id,obj_type),  
constraint fk_ref_subtype_B foreign key (obj_id_reference_to_subtype_B ,obj_type_reference_to_subtype_B) references supertype  (obj_id,obj_type),  
constraint fk_ref_subtype_CD foreign key (obj_id_reference_to_subtype_CD ,obj_type_reference_to_subtype_CD) references supertype  (obj_id,obj_type),  
)   

create table relation_XY_with_Z(  
obj_id_subtype_XY varchar2(30) primary key,  
obj_type_subtype_XY varchar2(1) check (obj_type in ('X','Y')),  
obj_id_reference_to_subtype_Z varchar2(30) ,  
obj_type_reference_to_subtype_Z varchar2(1)  check (obj_type reference_to_subtype_Z in ('Z')),    
attribute_1 varchar2(1),   
attribute_2  varchar2(1),  
constraint fk_subtype_XY foreign key (obj_id_subtype_XY,obj_type_subtype_XY) references supertype  (obj_id,obj_type),  
constraint fk_ref_subtype_Z foreign key (obj_id_reference_to_subtype_Z ,obj_type reference_to_subtype_X) references supertype  (obj_id,obj_type)  
)   
share|improve this question

1 Answer 1

When implementing subtypes of an entity there are various solutions on Oracle:

  1. Create one wide table with the attributes of all subentities. Entity type stored in a field and used to enforce business rules ("You may only fill column A for entity type B").
  2. Create one master table with the shared attributes and per subentity another table. And relations back to the master table.
  3. Create one table for each subtype and include the shared columns.

Here you are suggesting option 2. Option 2 keeps you master table rather narrow in terms of columns and might save some storage. However, it has serious drawback when you want to enforce business rules declaratively or through PL/SQL triggers or alike.

For a very large table and option 2 I would recommend against using declarative constraints, even when I am a lover of robust databases. It can get really slow, you need to appropiate indexes for locking and parallel loading high volumes can be challenging in terms of performance and parallelization.

For a very large table I would recommend either option 3 or option 1. Option 1 is rather simple to understand and given the large number of columns Oracle allows in one table, it shall not be a problem. Storage should not be an issue; trailing null columns are not stored at all in data blocks and null values within the row itself only require 1 byte per column to signal "hi, I have no value for this column". For option 1 you can write well-performing and parallellizable views that select only one or more of the subtypes. Option 1 also can be loaded fast in parallel. And you can implement many rules requiring a fk as a check constraint on a row, which does not involve locking or other problems hindering high volume use.

Unless... of course when one of the subtypes have comparetively very wide columns compared to the rest. That would make each of the data blocks contain considerably less rows. In that case I would recommend option 3, since they are such wide differences and use 'union all' constructs to run queries on the whole supertype. A mix can be option 3 and option 1: option 3 for the subtypes with optional columns with lots of data (varchar2(4000) filled nicely) and option 1 for the other subtypes.

share|improve this answer
    
I agree with your analysis,but please note that the events requiring additional data are few with respect to the whole table(say less 1% of the rows).If I had to design the db now I would consider option 3 but changing the table now would require a major refactoring(because of other foreign keys).Performance wouldn't be an issue for populating or querying the new tables because while the main table is populated by a batch job the rows of the "relationship" tables that I intend to create are few and are added "interactively" by human users(they have the expertise to identify the related events) –  mic.sca Dec 4 '13 at 17:02
    
Taking option 2 as a given, which of the 2 implementations above would you suggest? –  mic.sca Dec 4 '13 at 17:29
    
Taking option 2 as a given, neither of the two implementations when I understand the question correctly. There are multiple relationships going back to the supertype. And somehow experts are adding more. It sounds like something that is to evolve, and has now evolved into joining multiple tables. This gives me the feeling that the datamodel is not static. In such cases I would recommend having one table per subtype, plus a generic relationship table. Please have a look at data vault modeling technique. –  Guido Leenders Dec 5 '13 at 8:39
    
The relations I would add using something like a link table or "create table eventrelations(obj_from_id, obj_to_id, relation_type_id)". The facts (shown above as "attribute1" etc) can be considered satelites, whereas the supertype can be considered a hub. All of course without time component. So you can tell management later that you followed data vault modeling technique because the model was already designed that way and you just needed to add a link table :-) Does this help you? –  Guido Leenders Dec 5 '13 at 8:41
    
Definitely yes, however going down this path my question comes up again: would it be better, in your opinion to have just eventrelations(obj_from_id, obj_to_id, relation_type_id) or eventrelations(obj_from_id,obj_from_type, obj_to_id,obj_to_type, relation_type_id) and being able to express constraint like "if relation_type_id is relation_a then obj_from_type must be in (x,y) and obj_to_type must be in (z)" althought this would require adding a unique constraint in the main table on (obj_id, obj_type) and the relative fks? –  mic.sca Dec 5 '13 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.