Wednesday, July 1, 2020

Data model relation mapping ideas

This post is only for myself. sometimes need to check the theory to finalize. Here it covered almost all kind database table/relation mapping.  I just copied it from my note. didn't even check the writing.



Data model relation mapping idea

1.    Multivalue – like Interest column (one can have multiple interest topics)
2 tables/relations.  a new relation (bridge table) need to create for this . main tables pk will be fk in multi-value table . multi-value will be a composite key

 
2.    One to one relationship
2 tables/relations
 answer: Either way is fine





3.    one to one ( one is full participation)

2 tables/relations but one way, need to avoid null value (emp --Dept : all emp must partcipate so Dept.pk will fk in Emp to avoid null value)



4.    One-to-many
Normal way, 2 tables



5.    many-to-many
3 tables, Separate relation (bridge table concept)

 

6.    Identifing relationship with weak entity type
 Like one-to-many (composite key in many table) (almost same with multi-value)








Super type and subtype

1)    Case-1 :  Mandatory - disjoint
(Mandatory means master must be relation with childs, disjoint means- chlid should separate (child1+child2=master), not overlapping)
Two child table (no master table) with all master table's columns


 

2)    Case-2 : Mandatory - Overlaping
Three table/relation, 1 Master table, 2 child tables (with pk of master table)
Or
1 table including all master and childs with a identifiyng flag column (not recommended, consistency problem)



3)    Case-3 : Non-Mandatory - Overlaping
   Same way as case-2, Overlapping are same way

 

4)    Case-4 : Non-Mandatory - Disjoint
Same way as case-2, 3 tables


 




Union type
In union type, looks like Master (actually not master table here, chlid of two masters table) table is a sub-type of Childs
3 tables:  it is like a normal type (sub-type table) table with artificial identifier/surrogate Id column as PK and referencing (FK) to two masters table.

 
Here is the overall picture:  EER (right  sided one) and How to Relation Mapping (left sided one) 



EER : extended Entity diagram and relation mapping



No comments: