4 levels of 'relationality' (is that a word?)

[glennn.php]

ok, my first mistake was assuming my client knew what the heck he wanted. he had described a database to store a 'student', many 'subjects' for a student, with the fields 'date', 'hrs required', 'hours used', and 'description' for each subject. so i built it. easy, right?

 

ha.

 

because i was unable to read his mind, i wasn't aware that this data ACTUALLY needs to be related as the following, and I NEED HELP. ) please...

 

student will have many subjects .

 

subject will have many hours required (subtopic?) assigned to it.

 

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

 

 

i was tempted to just throw the hours done into an array for each hours required, but then looking up the ladder my brain gave up and began whimpering like Al Gore, so i'm HUMBLY asking for some help/advice on how to build these tables, if i can get away with using arrays (a LOT easier for me than JOINS all over the place, which means i'd be asking for help with those next -- ARRGGH!) or not.

 

can someone help a guy out? i'd really appreciate it.

 

GN

[trq]

Not to be harsh but I would suggest that your first mistake was taking on a client when you quite obviously are missing some basic database design and implementation skills. Joins are not difficult and in avoiding them because you think they are you are selling your client an inferior product.

 

You have already described the data relationships, where exactly are you stuck on implementation?

[Zane]

First off, you need your two main tables for:

- students    // This will include students' name, id, address, hours done, etc...

- subjects    //  This will include the hours required, id, name, description, etc..

 

From the ids of these two tables you will create a joining table for whatever you need joined.  So the next table would be for the subjects each student has.

  - studentsSubjects  // This will have a field for tableID, student ID, subject ID

 

As far as

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

I have no idea what you need...  what is a subtopic?

[glennn.php]

in fact i wasn't doing him a disservice if the data he required was AS he described - i wouldn't have needed any joins. so i appreciate it, but i care about my clients enough to know when i'm not qualified. for his original description, i AM qualified, thanks.

 

perhaps not everyone is as, oh, nevermind.

 

i didn't need joins until it became obvious he hadn't exactly presented the facts. so NOW i'm trying to get him taken care of.

 

thanks.

[glennn.php]

first, thanks for your help instead of sanctimony. now i'll go read your post and learn something.

 

(right, i just stuck subtopic in there as some sort of identifier) the subject is broken down into sessions, or 'sub_subjects', and each 'sub_subject' is then broken down into hours.

 

First off, you need your two main tables for:

- students    // This will include students' name, id, address, hours done, etc...

- subjects    //  This will include the hours required, id, name, description, etc..

 

From the ids of these two tables you will create a joining table for whatever you need joined.  So the next table would be for the subjects each student has.

  - studentsSubjects  // This will have a field for tableID, student ID, subject ID

 

As far as

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

I have no idea what you need...  what is a subtopic?

[glennn.php]

Not to be harsh but I would suggest that your first mistake was taking on a client when you quite obviously are missing some basic database design and implementation skills. Joins are not difficult and in avoiding them because you think they are you are selling your client an inferior product.

 

You have already described the data relationships, where exactly are you stuck on implementation?

 

and i didn't say i was 'avoiding' them, i've used them before, in fact, but i'm just not good with them.

 

you might note how your friend here was quite a bit more constructive (helpful) in his post which will surely be helpful for ANYONE who reads this thread, unlike this post.

[glennn.php]

First off, you need your two main tables for:

- students    // This will include students' name, id, address, hours done, etc...

- subjects    //  This will include the hours required, id, name, description, etc..

 

From the ids of these two tables you will create a joining table for whatever you need joined.  So the next table would be for the subjects each student has.

  - studentsSubjects  // This will have a field for tableID, student ID, subject ID

 

As far as

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

I have no idea what you need...  what is a subtopic?

 

ok, let me try a better description: the HOURS DONE doesn't apply to the student... let's try it this way:

 

BOB : ENGLISH : SESSION1 (hrs_reqd 6) : DAY1 (hrs_done 1) : DAY2 (hrs_done 1) : DAY3 (hrs_done 2) ...

BOB : ENGLISH : SESSION2 (hrs_reqd 9) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

BOB : ENGLISH : SESSION3 (hrs_reqd 12) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

 

BOB : GEOGRAPHY: SESSION1 (hrs_reqd 4) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

 

 

i know that i'm assigning id's to each entity, STUDENT, SUBJECT, etc, the part i have trouble with is associating each with the other. i can get ALL the SUBJECTS that belong to STUDENT, and i could work out a JOIN to get ALL the SESSIONS for each SUBJECT for each STUDENT, but that's about as deep as i can go.

[Zane]

Ok, so from what I understand, every subject has a set amount of sessions, right?  And each session has a set amount of hours required?

It seems the real dilemma here is keeping track of the days, which I assume will just be stored as a UNIX timestamp or MySQL DATE formatted field.

 

It seems to me that you would need a joining table that includes

- the primary key id of the table

- the id of the session

- the id of the subject

- the id of the student

- the datetime of hours earned for said session

- the number of hours earned for the said datetime..

 

I'm nothing close to an SQL expert so I'm pretty much just listing things right now.

 

Am I on the right track here?

[glennn.php]

Ok, so from what I understand, every subject has a set amount of sessions, right?  And each session has a set amount of hours required?

It seems the real dilemma here is keeping track of the days, which I assume will just be stored as a UNIX timestamp or MySQL DATE formatted field.

 

It seems to me that you would need a joining table that includes

- the primary key id of the table

- the id of the session

- the id of the subject

- the id of the student

- the datetime of hours earned for said session

- the number of hours earned for the said datetime..

 

I'm nothing close to an SQL expert so I'm pretty much just listing things right now.

 

Am I on the right track here?

 

not a set amount - it's arbitrary all down the line. can i upload an image? i've laid it out...

 

[glennn.php]

an image of the layout is attached. the numbers of all the rows is arbitrary...

[glennn.php]

So Dad has two kids, Bobby and Alice.

 

Alice is assigned 3 Subjects for the year. Bobby is assigned 2:

 

English and Math

 

For English, Dad will begin teaching him a topic that will require 6 hours to complete, and he will enter a 6 Hrs Req'd for Topic1, and he will enter a 1 (Hrs Done) for the first hour they spend on Topic1. He'll enter a 1 for the second hour they spend on Topic1. and so on and on...

 

Later on, he'll begin teaching him Topic2 and he'll enter an 8 hours required for Topic2 (STILL in English!), and he'll enter a 1 when they spend an hour on Topic2, and so on and on…

 

[Zane]

Alright then.

 

Looks like you'll need to have a joining table for the students' topics instead.

 

You should have a table for :

students

- would contain everything about the STUDENT

subjects

- would simply contain the subject name... and the primary key of course.

topics

- would contain every topic, along with subject id, description, and hours required.

 

Then, for the joining table, you'd have

- primary key

- student id

- subject id

- topic id

- hours spent

 

 

Grabbing all that information would look something like this

SELECT st.studentName, sub.subjectName, top.TopicName, top.hourReq, stop.hoursSpent, top.desc
FROM studentTopics stop
INNER JOIN students st ON (st.studentID = stop.student)
INNER JOIN subjects sub ON (sub.subjectID = stop.subject)
INNER JOIN topics top ON (top.topicID = stop.topic)

Then. you could craft your WHERE statement however you'd like.

For instance if you're looking for all students who have not satisfied the required hours.. you do

WHERE stop.hoursSpent != top.hoursReq

[glennn.php]

Alright then.

 

Looks like you'll need to have a joining table for the students' topics instead.

 

You should have a table for :

students

- would contain everything about the STUDENT

subjects

- would simply contain the subject name... and the primary key of course.

topics

- would contain every topic, along with subject id, description, and hours required.

 

Then, for the joining table, you'd have

- primary key

- student id

- subject id

- topic id

- hours spent

 

 

Grabbing all that information would look something like this

SELECT st.studentName, sub.subjectName, top.TopicName, top.hourReq, stop.hoursSpent, top.desc
FROM studentTopics stop
INNER JOIN students st ON (st.studentID = stop.student)
INNER JOIN subjects sub ON (sub.subjectID = stop.subject)
INNER JOIN topics top ON (top.topicID = stop.topic)

Then. you could craft your WHERE statement however you'd like.

For instance if you're looking for all students who have not satisfied the required hours.. you do

WHERE stop.hoursSpent != top.hoursReq

 

effin' brilliant! that makes sense... i can handle the WHERE statements, there'll be a few different ones for different parts (mainly displays for the parents. - i didn't mention the PARENTS of these little brats, did i!!! )

 

thanks, i'll say hi to Tony again in a minute.

[glennn.php]

ok, i have to throw a couple of questions in here:

well, one question:

 

an Hours_Spent entry has to be a new record each time, because the purpose of this is to display the individual times, pretty much like the table i uploaded. so the displayed table would show a column of Hours Spent instead of just a total...

 

is that going to work with the joining table as you defined it? i guess it would as long as the query is done right... right?

 

 

 

 

Alright then.

 

Looks like you'll need to have a joining table for the students' topics instead.

 

You should have a table for :

students

- would contain everything about the STUDENT

subjects

- would simply contain the subject name... and the primary key of course.

topics

- would contain every topic, along with subject id, description, and hours required.

 

Then, for the joining table, you'd have

- primary key

- student id

- subject id

- topic id

- hours spent

 

 

Grabbing all that information would look something like this

SELECT st.studentName, sub.subjectName, top.TopicName, top.hourReq, stop.hoursSpent, top.desc
FROM studentTopics stop
INNER JOIN students st ON (st.studentID = stop.student)
INNER JOIN subjects sub ON (sub.subjectID = stop.subject)
INNER JOIN topics top ON (top.topicID = stop.topic)

Then. you could craft your WHERE statement however you'd like.

For instance if you're looking for all students who have not satisfied the required hours.. you do

WHERE stop.hoursSpent != top.hoursReq

[Zane]

an Hours_Spent entry has to be a new record each time, because the purpose of this is to display the individual times, pretty much like the table i uploaded. so the displayed table would show a column of Hours Spent instead of just a total...

For this you would use an UPDATE query

something like,

UPDATE studentTopics stop 
SET stop.hoursSpent = stop.hoursSpent + 1
WHERE ... stop.topic = ? AND stop.student = ?

 

 

If you wanted to add more topics, you would use an INSERT query like so,

INSERT INTO topics `topicName`, `hoursReq`, `subjectID`
VALUES ("Session 77", 25, 1)

[glennn.php]

no, i mean they want to KEEP the hours individually - it's like a report, really. they want to see each hour spent, he claims, so i don't want to change it, i just need to keep adding rows and then go get them to display a whole bunch of rows on a page... it should work this way, tho.

 

also, each subject has to be unique to each student because this guy states he wants the parent to be able to edit the name of the subject, so i'm adding the child_id to the subject table...

 

 

an Hours_Spent entry has to be a new record each time, because the purpose of this is to display the individual times, pretty much like the table i uploaded. so the displayed table would show a column of Hours Spent instead of just a total...

For this you would use an UPDATE query

something like,

UPDATE studentTopics stop 
SET stop.hoursSpent = stop.hoursSpent + 1
WHERE ... stop.topic = ? AND stop.student = ?

[glennn.php]

god, it may not sound it, but i'm a bit beyond INSERT and UPDATE and DELETE - mainly it was just organizing all this data and how it all relates was confusing me. once i see how to lay it all out, i should be all right. i'll end up being comfortable with joins once i do enough of them.

 

thanks

[glennn.php]

major help you've been, Zane - one minor issue:

 

this worked, except that my one test student came back twice:

 

  
	  $result = mysql_query("SELECT c.name, s.sub_name, t.hrs_reqd, ts.hrs_spent, ts.work_desc
FROM a_topic_stuff ts
INNER JOIN a_child c ON (c.ch_id = ts.ch_id)
INNER JOIN a_subjects s ON (s.sub_id = ts.sub_id)
INNER JOIN a_topics t ON (t.t_id = ts.t_id) WHERE c.u_id = '2'") or die(mysql_error());

	  while ($child = mysql_fetch_array($result)) {
		  
		  echo $child['name'].", ";
		  
	  }
	  

 

 

id ch_id sub_id t_id hrs_spent work_desc

1   1         1       1     1               read a book

2   1         1       1     2               read a longer book

 

and i'm thinking this is the reason, but i have to have all my hrs_spent itemized like this - what do i need to change?

 

[Zane]

This may not be the problem, but it's the only thing that stands out to me.

 

Is uid, in the a_child table, set as a VARCHAR variable?  Why do you have 2 quoted?  And how did the query not fail if you do have it set as INT?

[glennn.php]

This may not be the problem, but it's the only thing that stands out to me.

 

Is uid, in the a_child table, set as a VARCHAR variable?  Why do you have 2 quoted?  And how did the query not fail if you do have it set as INT?

 

u_id is an INT -

 

i had the 2 quoted because i thought it was supposed to be, as an integer - nevertheless, unquoted i still get a duplicate return -

 

i don't know -

 

i shortened the Q. considerably to troubleshoot this:

 

  $result = mysql_query("SELECT c.ch_id, c.name, s.sub_name
FROM a_child c
INNER JOIN a_subjects s ON (c.ch_id = s.ch_id) WHERE c.u_id = 2") or die(mysql_error());

a_subjects: 
sub_id	ch_id	sub_name
1	         1	       English
2	         1	       History

 

STILL getting duplicate child (david, ch_id 1)

[Zane]

This is the point at which I fail at SQL.  Just as in PHP, there is probably a miniscule error going on in the JOINing of these tables.  I don't think I can be any more help than Google at this point.  :shrugs:

 

Since you've already made a troublshooting query, you should experiment with the different joins and see if you can figure it out.  That's what I'd be doing right now.

 

first I would eliminate the "INNER" portion on the INNER JOIN and see what yeilded with JOIN

If that didn't work, I would start using OUTER JOINs until the troubleshoot query worked.

LEFT OUTER JOIN

OUTER JOIN

RIGHT OUTER JOIN

 

if I'm not mistaken though, I do recall fenway once saying that a left outer was the same as a regular outer... or cartesian.. or something.  I could just be really wasted too.

[glennn.php]

This is the point at which I fail at SQL.  Just as in PHP, there is probably a miniscule error going on in the JOINing of these tables.  I don't think I can be any more help than Google at this point.  :shrugs:

 

's ok, you've given me a GREAT leap forward, that's why i sent the 2nd donation. i'll learn a lot from putting all this together.

 

hopefully i can aspire to be as smart as that other jerk with the sermon.

 

 

)

 

peace, Zane.

[trq]

hopefully i can aspire to be as smart as that other jerk with the sermon.

 

Calm down. I'm didn't say anything that wasn't true.

 

People charging other people to do work ought be able to do it. If I was forking out money to some guy to fix my car only to find out later that all he knew how to do was change oil I would be pissed.

 

The problem you where stuck with is essential knowledge as far as I'm concerned if your selling yourself as a developer.

[glennn.php]

thorpe, AS i already said, what he had originally described was different than what it later turned out to be, and was something i WAS able to do. this guy said, "i need my oil changed" and i charged him to change his oil, and when i was halfway through it, he said, "no, what i meant was i need new rings, which means you'd also have to change the oil".

 

why is that hard for you to understand?

 

i did NOT charge him to fix his transmission, i only charged him to do what he originally asked because THAT's what he originally described. so you are WRONG. you're just not willing to admit it.

 

which is why you are a sanctimonious jerk. which sounds like a GOOD place for me to mark this thread solved.

 

i'm going to have to remember how the administrators like to talk to their people in here next time i need some help. damn.

[trq]

Truth be known, clients rarely know what they need, it is part of a developers job to find these things out.

 

Having said that, I can see your point. You took on the job because you assumed the client knew what they wanted upfront and you understood enough to cover the complexities of the original requirement. In the end, it turns out the task was more complex than you anticipated.

 

I can see I've pushed your buttons, and for that I apologise. You should understand that my gripe is not with you in particular, but the general idea of many people selling themselves as developers and then coming to places such as this looking for answers to problems that most developers (in the position to sell themselves as such) should consider trivial.