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