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
