How to Design for this Requirement Table Design. [Resolved]

Posted by Jayakumars under .NET Framework on 3/6/2016 | Points: 10 | Views : 388 | Status : [Member] [MVP] | Replies : 2
Hi

--- Famil Members Entry
-- How to Create Table design
-- Parents ex: Father,Mother
--Childerens 7 Childerns have this parents 3 boy 4 girls
-- How to design the table

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 3/7/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@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

Posted by: Jayakumars on: 3/7/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Rajnilari2015

Wonderful

can you share your email id?

I have another one question


Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response