Table Design Help

[patkeaveney]

i am creating a database for a local football club. I want to show player information (the main focus of the database), such as, what guardians are responsible for this player, what are the Doctors details for this player, what team does the player play for etc.
I have seperate tables for the Guardian, Doctor and Team information.

I also want to hold Staff details, (Name, address, staff title etc)

My problem is that someone in the staff table, could also be in the guardian table.

Should i combine the Guardian and Staff tables into one table as I dont want to have to enter the data twice for both a staff memger and a guardian

Note:
a player can have more than one guardian
a guardian can be responsible for more than one player.

What is the best way to approach the design of these database tables.

Thanks in advance

Pat

 

[ScottGem]

You should have ONE people table. Players, Guardians, Doctors, Staff, etc all go into this table with a flag to indicate the type of person. If there is data that is specific to a type, add a separate table with a one to one relation for those fields.

To indicate guardianship you would use a join table:

tjxPlayerGuard
PlayerGuardID (PK Autonumber)
PlayerID (FK)
GuardianID (FK)

 

[patkeaveney]

i have not used junction tables before.
If all my people are in one table, how can i show what guradians(can be more then one), are resonsible for a player, in a form.
I used seperate tables so the guardian info, would be shown as a subform on a player main form.

Sorry if i sound nieve.

Thanks for helping

 

[ScottGem]

Using the join table as I described. You would use that table as the RecordSource of your subform.

 

[patkeaveney]

A guardian could also be a member of staff. How do i deal with that.
Thanks again for your help