database relationships?

[millers]

I'm very new to Access and I'm trying to create a database for patients. I have necessary information in four tables, eg :

Table 1
PatientID
Location
FamilyName
ChildName
DateOfBirth
MotherName


MotherAddress
MotherTelephone
MotherMobile
MotherEmail
FatherName
FatherAddress
FatherTelephone
FatherMobile
FatherEmail
FosterparentName
FosterparentAddress
FosterparentTelephone
FosterparentMobile
FosterparentEmail
ReportDue
LOAsent
Notes

Table 2
AdvocateID
AdvocateFirm
AdvocateAddress
AdvocateTelephone
AdvocateFax
AdvocateLeader
AdvocateLeaderTelephone
AdvocateLeaderEmail
AdvocateAssistant
AdvocateAsstTelephone
AdvocateAsstEmail
AdvocateAsstMobile

Table 3
SafeguarderID
SafeguarderName
SafeguarderAddress
SafeguarderTelephone
SafeguarderEmail
SafeguarderMobile

Table 4
SocialworkerID
Socialworker
SocialworkerAddress
SocialworkerTelephone
SocialworkerEmail
SocialworkerMobile

However, because of the complexity of these cases the Child, Mother, and Father may all have separate Advocates, as will the Socialworker. Plus, the Advocates, Socialworkers, and Safeguarders will all have many different children as clients. How do I create relationships between these tables that will take account of the multiple links?

Many thanks for any assistance you can give me.

[ajetrumpet]

whats the business driver? the patient, right? so that's really the only thing you need to concern yourself with. there is actually nothing wrong with duplicating patient data in the patient table if a father, mother or another sibling is a patient in one record, and a relative in another record for another patient, like a son or daughter. does that make sense?

you really should have the fields of ID, ID and ID, all from the advocate, safeguarder and socialworker tables included in the patient table. that would complete the normalization really. and unless those 3 child tables have other information associated with them, they will only be involved in those 3 relationships, all with the patient table.

[millers]

Thanks for your help. I shall try your suggestion and hope that I've understood it correctly.

Regards