Design / Primary key Question

[comboy]

Hi all,

I'm new to sql so if I'm asking something that is plain obvious sorry in advance.

The situation is that I currently have a excel sheet that is used to store schedule information for three trainers
in the following format

DATE | Trainer 1 | Trainer 2 | Trainer 3|
12/1/07 | Mackens | In office | On Course|

My manager wants to place this info into a MySQL DB so that we can develop a php web site to manage the schedule and allow the trainers to view schedule info etc when offsite. I've designed most of the tables whose primary keys are all forigen keys of the schedule table but would like some advice on developing the schedule table in relation to its primary key.
I know that I can't use the date alone as a primary key so I was thinking of using both the Trainer_ID and Day(date) as the primary key as this should allow info for all trainers to be entered for the same date and be flexible enough to allow for growth in the number of trainers without having to edit any external php code etc.
However I was just wondering if I should continue with this method or could someone suggest a better method as I've missed something that an experienced DB admin/developer would spot.

Thanks in advance

Graham.

 

[obadare]

I think you need 3 tables:
#table 1
trainer_tbl(trainer_id,name,etc)

#table 2
programme_tbl(prog_id,location,description)

#table 3
schedule_tbl(trainer_id,prog_id,schedule_date)

#query to select shedules for a particular date
select distinct(trainer_id),prog_id,schedule_date from schedule_tbl where schedule_date= current_date group by trainer_id)

 

[comboy]

Hi obadare,

I do acyually have seperate tables that hold the information for the trainers, the courses and the customers. The primary keys of each of these tables will be foreign keys on the schedule table and the trainer_id key will be used in conjenction with the date to form a primary key for the schedule.
Sorry if I didn't make this clear on the first post, or am I just missing your suggestion?

Thanks,

Graham

 

[obadare]

Sorry I mis-read your posting, your approach in my opinion is the way to go

 

[comboy]

Hi obadare,
Thanks for your input its much appreciated esp for a novice like me

Graham