Table Design for Football Club Database

[patkeaveney]

I am trying to build a database for a local football team, and am having trouble with the design of the tables.
The database contains numerous tables but my main problem is that within the database there are 4 types of people information:
Players:
(a player can have more than 1 guardian attached, eg mother, father, uncle, etc
A player could also be a member of staff eg coach)
Guradians of the players
(a guardian can be attached to more than 1 player, eg two sons playing for the same club
a guardian could be a member of staff, eg manager).
Staff Members
(A staff member can also be a guardian)
Doctors
(a doctor could be a staff member and also a guardian).

What is the best way to approach the design of these tables as i dont want to have to enter the same name, address details etc, more than once for if a person is in more then one of the people information categories.

The main focus of the database is driven by the player Information.

I want to show what guardians are responsible for each player, who the players doctor is, etc.

 

[ScottGem]

Two tables, tblPlayers and tblAdults. The latter table listing all guardians, staff doctors etc. Then add tblAdultRole
AdultRoleID (PK Autonumber)
AdultID (FK)
RoleID (FK) (i.e. Guardian, Staff, Doctor)

you then use the join table I mentioned previously to join adults to players.

 

[patkeaveney]

Thanks Scott

 

[patkeaveney]

I am still a little confused.

As a player can have more than 1 guardian, what fields do I need in the player table to make the link to the adult table entries.

Thanks

 

[ScottGem]

none, Again, what you use is a JOIN table. its the JOIN table that manages the relation.

 

[patkeaveney]

Sorry what field do i use in the player table to make the link to the Junction table.
If i use adultid, surly this will only link to one adult record,with many roles.

A player can be attached to more than one adult.

Apologies if i am missing the point here.

 

[ScottGem]

I explained this inthe other thread. A join table would look like this:

tblPlayerAdult
PlayerAdultID (PK Autonumber)
PlayerID (FK)
AdultID (FK)
AdultRoleID (FK)

This way you link the player to the adult and show the relation.

 

[patkeaveney]

Thanks for all your help Scott

I now have it working

 

[fsagwe]

#How does one come up with a Joint table and can you assign PK & FK on one single entity table?

Kind Regards,

 

[ScottGem]

fsagwe,

I'm not entirely clear on your question. Also you should start a new question rather than piggyback on a 9 yr old thread.

You come up with a join table to create two 1:many relationships to both sides of a many to many as explained in the thread. In its simplest a join table consist of 2 fields that are FKs to the 1 side of each relationship.

If you need a further explanation, please start a new thread.