Database/Table structure...HELP!

[PlaGuE]

Okay so for this script i have these ppl called trainers.

Now with each trainer they can make training routines.

These routines need to be able to be assigned to a trainee.

The values of the routines are trainee independant. Meaning that a trainee's progress should not affect other trainee's of the same routine.


If thats confusing...Well it is to me too.

Hopefully this example will help.


I am a trainer.
I have 1 routine which i use.

it consists of "Three" tasks.

Routine Name: StarCraft:: Terran Training

Can make 20 nukes in under 10 minutes.
Can expand to 3 bases in under 5 minutes.
Has 6 groups of dropships full of snipers and tank within 8 minutes.


Now i train three users.Trainee's.

Trainee#1
Routine Name: StarCraft:: Terran Training

Can make 20 nukes in under 10 minutes. :: Complete
Can expand to 3 bases in under 5 minutes. :: Complete
Has 6 groups of dropships full of snipers and tank within 8 minutes. :: Complete


Trainee#2
Routine Name: StarCraft:: Terran Training

Can make 20 nukes in under 10 minutes. :: Complete
Can expand to 3 bases in under 5 minutes. :: Incomplete
Has 6 groups of dropships full of snipers and tank within 8 minutes.:: Incomplete


Trainee#3
Routine Name: StarCraft:: Terran Training

Can make 20 nukes in under 10 minutes. :: InComplete
Can expand to 3 bases in under 5 minutes. :: InComplete
Has 6 groups of dropships full of snipers and tank within 8 minutes. :: InComplete


Now see my problem?...If its an easy solution then shoot me dead.Otherwise I think to hard.

What i need help on is the fact that i dont know how i am to go about the database's tables aswell as row insertion?

So far i have this as my table structure
http://vbforums.com/attachment.php?attachmentid=47560&stc=1&.png

Its just 1 row atm "Routines Main Header Column".Hence the parent_id (-1).
The value column is for the true or false. True = Complete. False = Incomplete.
Atm... Idk what i was thinking with the user_id (-1) which meant its the default structure.

I put it there right before i posted this topic.

ANymore info you need to help me figure out this... this.. thing. Dont hesitate ask.

[penagate]

Have you ever studied databases, in particular relationships? If not I suggest you do so, it will help a lot.

Although I don't understand your specific problem particularly well, I believe you need seperate tables for trainers, trainees, and training routines.

If we use the analogy of classes in a school (something that I do understand), let me explain it this way. Each "entity" if you like has its own table. One table for teachers, one for students, and one for classes. Each table has a unique primary key field, the ID - so we have TeacherID, StudentID, and ClassID.

Study first the relationship between teachers and classes. One teacher can teach multiple classes, but one class can only have one teacher (for the purposes of this example, anyway). This is called a one-to-many relationship. All that is required to create this relationship is a TeacherID field in the Classes table - so each class is assigned one unique individual teacher. This means one teacher can be assigned to multiple classes, but one class can only be assigned one teacher. Make sense?

Now look at the relationship between students and classes. One student can, obviously, be enrolled in multiple classes. One class also has many students. This is known as a many-to-many relationship. This is very slightly more complex than a one-to-many relationship, as it requires one extra table. Let's call this table Enrolments. Each enrolment represents one student being enrolled in one class, so it needs to hold the StudentID and ClassID. What we've created here is a bridge between two one-to-many relationships - students to enrolments and classes to enrolments - and effectively created a many-to-many relationship.

If you're not clear on any of that please let me know. I hope you can apply that principle to your problem, with trainers/trainees/training routines in place of teachers/students/classes.

[PlaGuE]

Okay. Well i have a table called trainer2trainee.

The table consists of 3 columns.

trainee_id INT(11)| trainer_id INT(11)| completed INT(11) (Completed training)

In the members table is where the user is defined as "trainer".

I have some knowledge of database relationships.Not alot but its gotten me far. I think i may have done something like this similiar in the past..but since i lost all my backup discs...I wouldnt know.


The relationship is close to what you said with the teachers - classes. or students to classes. For this each work.

The thing im having problems with is how to go about setting up the relationship between 1 "trainee" too 1 "routine" with multiple "items". Any "trainee" can have the same routine. But will have there "own values".

This is probably really a basic setup im looking at. But for some reason i cannot figure it out.I might just be trying to hard and confusing myself.And if im confusing myself its probably due to the fact that im wanting something like this.

Note: By looking at this example you can see how confused i am.

a table with a routine - trainee relationship

routine_id | trainee_id


Then a table with the routine - trainer (creator of the routine) relationship
routine_id| creator_id |

Then a table with the routine - (routine item)'s relationship

routine_id | parent_id ( -1 == the routines main name) | item_name

But now the question arises of how to have each trainee have the same routine but have there own values...?


Would i need to make a table just for this?

Where it'd hold each trainee's progress for each routine?:
item_id(routine_id from, routines table) | trainee_id | value | comment ???????

??????
I am thankful for your help. For without it.I'd still have a headache..Well a bigger one then the one i have now... Thanks to you.
Any more info needed .Just ask.

[penagate]

I think you're muddling the tables with each other.

If I understand your situation correctl, you need 4 tables.

Trainers
Trainees
TrainingRoutines
TraineesAndRoutines (for want of a more appropriate name)

"Trainers" will hold the TrainerID, Name, etc.
"Trainees" will hold TraineeID, Name.
"TrainingRoutines" should have a RoutineID, TrainerID, Description.
"TraineesAndRoutines" will have a TraineeAndRoutineID, RoutineID, TraineeID, Progress.
The underlined field in each table is its primary key.

With this setup, each trainee can be in multiple training routines, and have unique progress states for one.

Does that make sense?

[PlaGuE]

Right.

However, A user is defined as a trainer in the members table. Which gives them the Trainer Options.


About:

Trainers
Trainees
TrainingRoutines
TraineesAndRoutines (for want of a more appropriate name)

"Trainers" will hold the TrainerID, Name, etc.
"Trainees" will hold TraineeID, Name.
"TrainingRoutines" should have a RoutineID, TrainerID, Description.
"TraineesAndRoutines" will have a TraineeAndRoutineID, RoutineID, TraineeID, Progress.
The underlined field in each table is its primary key.

With this setup, each trainee can be in multiple training routines, and have unique progress states for one.

Does that make sense?


IDK Why but im still confused. Could you possibly make a to the point example? So i can see it in action and get a better understanding of what your saying. And to see how you'de go about doin it?...

EDIT:: I'll Be trying things out while waiting

[penagate]

What do you want to select? explain in English and I'll help construct the SQL.

very rarely do you need join's. I prefer multiple queries, saves headaches.

[PlaGuE]

What i want to select is this:...
lets say this is my url

http://mydomain.com/index.php?console&OPT=myTrainee&act=shwprog&user=1


It will show the routine that the user is being trained on.

example.


Name::StarCraft Routine#23|
-----------------------------------------------------------------
Routine Items: | Comment(s)|Completed|
-----------------------------------------------------------------
Q#1: 20 Nukes in under 10 mins | None | O Yes O No
-----------------------------------------------------------------
Q#2: 40 snipers in 12 minutes. | None | O Yes O No
-----------------------------------------------------------------
Q#3:5 dropships in under 15 | None | O Yes O No
minutes. |


Well

ANd then it would load the routine for that user and fill in the comment and Completed fields with the trainee's data.

[penagate]

OK, Maybe I'm not explaining terribly well. And your requirements are slightly more complex than I initially thought. I missed the part about having multiple goals within a training routine. I believe you will need an extra table to hold those.

TrainingRoutineGoals
GoalID
RoutineID
Description

That will hold the data for each target such as "20 Nukes in under 10 mins", etc. as well as the ID of the training routine that it is part of.

You will also need an individual progress for each goal of each routine for each trainee. So there's another table.

TraineeRoutineGoals
TraineeAndRoutineID
GoalID
Completed (bool)

You should now have 6 tables.

"Trainers": TrainerID, Name, etc.
"Trainees": TraineeID, Name.
"TrainingRoutines": RoutineID, TrainerID, Name.
"TraineesAndRoutines": TraineeAndRoutineID, RoutineID, TraineeID.
"TrainingRoutineGoals": GoalID RoutineID, Description.
"TraineeRoutineGoals": TraineeAndRoutineID, GoalID, Completed.

I have underlined the primary keys of each table. I didn't feel the last one required an index.

Now to pull the data that you want to construct that page. You have the following input:
user=1

And you wish to show all routines and their goals that the trainee is currently participating in, along with his/her progress. Is that correct?

First I would enumerate all routines that the trainee is participating in.
For the sake of brevity, I have shortened the query function name to Q(). This would equate to mysql_query() or whatever you are using.
// Sanitise the user ID. This is very important to prevent arbitary SQL injection.
$userID = (int) $_GET['user'];

// Select trainee "enrolments"
$joins = Q(
'select * from `TraineesAndRoutines` '.
'where `TraineeID` = '.$userID
);

// For each of those:
while ($join = mysql_fetch_assoc($joins)) {
// Get the routine details.
$routine = Q(
'select distinct * from `TrainingRoutines` '.
'where `RoutineID` = '.$join['RoutineID']
);

// Write the routine name.
echo('Name: '.$routine['Name']);

// Enumerate all of the routine's goals
$goals = Q(
'select * from `TrainingRoutineGoals` '.
'where `RoutineID` = '.$routine['RoutineID']
);

// For each goal:
while ($goal = mysql_fetch_assoc($goals)) {
// Get the user's progress for this particular goal
$progress = Q(
'select distinct * from `TraineeRoutineGoals`'.
'where `TraineeAndRoutineID` = '.$join['TraineeAndRoutineID'].
'and `GoalID` = '.$goal['GoalID']
);

// Output the goal name and completion status:
echo($goal['Description'].':'.$progress['Completed']);
}
}


I sincerely hope I did not screw any of that up :p

I left out the "comments" field, you can add that as you see fit.

[PlaGuE]

And you wish to show all routines and their goals that the trainee is currently participating in, along with his/her progress. Is that correct?

Correct.

I'll post back with results.

Thanks.You've been a great help.