Help creating a functional relational Database, Can't figure out relationship problem

[theresa]

I have created a number of tables but have been unable to get the relationship right. It's a database for a voluntary counseling service. The main tables are Counsellors, Clients, Counselling and Appointment. I made the Counselling table to enable some normalisation and hoped to connect the Appointments table to it as a child. The counselling table is to be used to hold specific data to be used in stats later such as number of no shows, number of counselling hours in period by counsellor, Clients finished counselling in period etc.. I'm wondering if I should create an unique primary key for the counselling table as it may be the duel Primary key that's not allowing me to connect it to the appointments table. It's my first database and I get that this is the most important stage to get it right but I've got lost somewhere. It's probably something simple but I just can't see it at the minute. Any help would be appreciated.



or

[orange]

theresa,

Here is a tutorial that will help you understand tables and relationships. You'll have to work through it, and there is a solution included. It all starts with a clear and concise description of the business that your database is intended to support.

Good luck.

[theresa]

Thanks for your reply, I have looked at that before but I'm still confused as to how best to connect the relationships in order to get the required information out of the database. I've been at this so long and tried many different ways that I've confused myself. Here's the brief that I've been given by the Counsellor to help her:

When a Client first contacts the service, information is collect from the client such as Name, Address, Phone, DOB, Date of Referral, Presenting Issues, Referral Source and Times/Days available. They are placed on a waiting List until a Counsellor is available.
An Active Counsellor has 4 or 5 Clients at any given time and does not receive a new client until one of their current Clients has finished their Counselling. The Counsellor collects records of their Appointments such as Date, Time, Length of session and if the Client shows up for their Counselling Appointment.
When a Counsellor becomes available, a Client is then selected from the waiting list and given their first Appointment. The Client is seen by the Counsellor through Appointments until the Client is Finished and/or referred elsewhere, which is noted by the Counsellor. When the Client is Finished, they are no longer an active Client for that Counsellor and a new Client is selected from the waiting list.
The Management require the following information both quarterly and annually:

• List of New Clients referred within the Period
• Current Waiting List within the Period
• Current Waiting List based on Category and Age within the Period
• Length of time on Waiting List
• New Counselling Clients within the Period (Current Clients)
• Counselling hours provided within the Period grouped by Counsellor
• Number of Clients finished within the Period
• Number of Client 'No Shows' within the Period grouped by Counsellor

The Management would also like the database to warn if a Client has 3 previous consecutive 'No Shows'.

The closest ERD I can see to fit is



I think I have it with this



I just want to be sure before moving on and hoped that someone could look at it to check That I'm on the right track or point out any flaws I've overlooked as it needs to be able to fulfill all of the above criteria