DB Design for family members

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 587
The DB design should be along the below side -

tblGenderMaster (will store the genders e.g. Male/Famale)
---------------
GenderId(PK) [INT]
GenderType VARCHAR(4)


tblFamilyMemberRoleMaster (will store the role of family members e.g. Father, Mother,Son,Daughter,Uncle etc.)
-----------------------
FamilyMemberId(PK) [INT]
RoleName[VARCHAR(20)]
RoleDescription[VARCHAR(50)]


tblFamilyGenere (will store the role of family generes like Family1, Family2 etc.)
------------------
FamilyGenereID(PK) [INT]
FamilyGenereName[VARCHAR(20)]
GenereDescription[VARCHAR(50)]


tblFamilyDetails (will store the actual family details and all the master's are present here as Foreign Keys)
-----------------------
FamilyDetailId(PK) [INT]
FamilyMemberId(FK) [INT]
GenderId(FK) [INT]
FamilyGenereID(FK) [INT]
MemberName [VARCHAR(50)]
Age [VARCHAR(50)]
Email [VARCHAR(50)]


Insert Data to the tables as below

(A) tblGenderMaster


GenderId GenderType
-------- -----------
1 Male
2 Female


(B) tblFamilyMemberRoleMaster

FamilyMemberId RoleName RoleDescription
-------------- -------- ---------------
1 Father Role Type: Father
2 Mother Role Type: Mother
3 Son Role Type: Son
4 Daughter Role Type: Daughter


(C) tblFamilyGenere

FamilyGenereID FamilyGenereName GenereDescription
-------------- ---------------- -----------------
1 Das This is for Das family members
2 CopperHood This is for CopperHood family members

(D) tblFamilyDetails

FamilyDetailId FamilyMemberId GenderId FamilyGenereID MemberName Email
-------------- -------------- -------- -------------- ---------- -------
1 1 1 1 NB NB@test.com
2 2 2 1 AB AB@test.com
3 4 2 1 RB RB@test.com
4 1 1 2 N_CHood NCHood@test.com
5 2 2 2 A_CHood ACHood@test.com
6 3 1 2 S1_CHood S1CHood@test.com
7 3 1 2 S2_CHood S2CHood@test.com
8 3 1 2 S3_CHood S3CHood@test.com
9 4 2 2 D1_CHood D1CHood@test.com
10 4 2 2 D2_CHood D1CHood@test.com
11 4 2 2 D3_CHood D3CHood@test.com
12 4 2 2 D4_CHood D4CHood@test.com


As can be observed that,

for the FamilyGenereID = 1, there are total 3 members (Father, Mother, 1 Daughter ).

for the FamilyGenereID = 2, there are total 9 members (Father, Mother, 3 Sons, 4 Daughters ).

In a similar way we can add more FamilyMember Roles. For a relationship to maintain, we can create a RelationshipMaster table and can add the RelationID as FK in the tblFamilyDetails table.

Hope this helps

Comments or Responses

Login to post response