I am the therapist for a specialized program for youth inthe juvenile justice system. Within the program we facilitate 2 differenttrafficking awareness curriculums. Each youth attends at least one of thetrafficking programs while enrolled in the main program. We can call themprogram A and program B. There are pre and post-tests given for each of theprograms and I need to be able to enter each youth’s pre and post-test scoresinto the database, so that I can determine if there was a change. All of theyouth will attend one of the programs (A or B) and some of the youth willattend both of the programs (A and B). I have a table that has each youth’sbasic information in it (Last Name, First Name, Date of Birth, etc) and theprimary key for that table is ClientID (autonumber). I want to know if I shouldcreate one table for program A with both the pre and post-test scores in that anda similar one for program B or do I need to create 4 tables (program A pre-testtable, program A post-test table, program B pre-test table, program B post-testtable)?
If I created one table for Program A I would think it wouldlook as follows with ClientID from the client information table joined to theClientID on this table:
ProgramAPrePostID (Primary Key)
ClientID
PreTestDate
PreTestScore
PostTestDate
PostTestScore

If I created 2 tables for Program A I would think they wouldlook as follows with ClientID from the client information table joined to theClientID on each of these tables:
ProgramAPreID (Primary Key)
ClientID
PreTestDate
PreTestScore

ProgramAPostID (Primary Key)
ClientID
PreTestDate
PreTestScore

I was also thinking about creating a table with just ProgramA and Program B on it as follows:
ProgramNameID
ProgramName (Where ProgramNameID 1 is for ProgramA and ProgramNameID 2 is for Program B)

Then I could have either one or 2 tables (I’m not sure whichwould be most appropriate) for the scores. Using one table it would look asfollows:
ProgramPrePostID (Primary Key)
ClientID
ProgramNameID
PreTestDate
PreTestScore
PostTestDate
PostTestScore

Using 2 tables it would look as follows:
ProgramPreTestID (Primary Key)
ClientID
ProgramNameID
PreTestDate
PreTestScore

ProgramPostTestID (Primary Key)
ClientID
ProgramNameID
PostTestDate
PostTestScore

In the last 3 tables above the ClientID field would bejoined with the ClientID on the Client Information Table and the ProgramNameIDwould be joined to the ProgramNameID on the table right before the last 3 Iprovided. Any thoughts?
As an extra component I also have to keep track of how manyhours of Program A and Program B each youth attended. I have no idea how tocreate a table (or if I need to create multiple tables) to keep track of thatnumber. Each youth needs to attend 5 hours of program A OR 7 hours of programB.

Any help would be appreciated!