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