@Jayakumars, 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 figure out , for the FamilyGenereID =2, there are total 9 members (Father, Mother, 3 Sons, 4 Daughters ).
Hope this helps
--
Thanks & Regards,
RNA Team
Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator