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 two schemas below is better? (Is any of the two not advisable?)
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)
)
Considerations
The second one 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, becauseobj_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.