[SOLVED] Please add constructive feedback!

[odisey]

Hello,

            I am creating a db that I will populate and parse with PHP.  I would like to create the most simple, powerful, and of course logically realtional tables as possible.  I will include here an example of input variables, and an example of data I would gather from the tables for output.

 

OK - here goes.  This is for a student evaluation ruberic.  There are 10 sections with 3 questions each on the ruberic (eg q1_1, q1_2, q1_3).  Each question can have an input value of 0 - 1 - 3 - or 4 for a midterm score, and for a final score.

 

*********************

Section one

_______________________

 

Question 1 = midterm value  | final value

Question 2 = midterm value  | final value

Question 3 = midterm value  | final value

 

TOTAL = value

*********************

*********************

Section two

_______________________

 

Question 1 = midterm value  | final value

Question 2 = midterm value  | final value

Question 3 = midterm value  | final value

 

TOTAL = value

*********************

etc......

*********************

 

Output - or information I want to parse and print to HTML using PHP

 

1. Each of the final values seen above in each section

2. A total 'midterm' score for all sections

3. A total 'final' score for all sections

 

            This looks simple enough.  I am juggling ideas for table structures with ideas for parse (query) or SELECT FROM options in php to print.  I want to get the most powerful relational power out of my tables though because we may modify output in the future. 

 

ANY and ALL feedback on suggestions for table structures and complementing queries is very welcome. 

 

Thank you,

Odisey

 

 

 

 

[Barand]

[pre]

section          question       

---------        -----------

section_id  --+  q_id

section_name  |  question

              +-< section_id

                  midtermvalue

                  finalvalue

[/pre]

 

However, should you later want to increase the frequency to more that just midterm and final, it would better to normalize the values also

 

[pre]

section          question          value

---------        -----------        ----------

section_id  --+  q_id      ----+    value_id

section_name  |  question      +--< q_id

              +-< section_id        rubric      (mid/final)

                                    value

                 

[/pre]

[odisey]

This is great - now if I just knew what it all meant!

 

Let me ask you - if I had a table off questions - say 10; and I wanted to return the total value from any field - how do I select and calculate that in PHP?  Using the ABS() function?  I have no idea yet - I am learning - I appreiate the input and help.  Do I use a SUM function in this query?

 

SELECT * FROM question_1 AS q_1_total WHERE student_id = 7001234

 

 

[Barand]

OK lets add student to the schema

[pre]

section          question          value                  student

---------        -----------        ----------              ----------

section_id  --+  q_id      ----+    value_id          +--- student_id

section_name  |  question      +--< q_id              |    studentname

              +-< section_id        rubric (mid/final) |

                                    value              |

                                    student_id  >------+

[/pre]

 

Suppose you want the section totals for student 7001234 in the midterms

SELECT s.section_name, SUM(v.value)
FROM value v
    INNER JOIN question q ON v.q_id = q.q_id
    INNER JOIN section s ON s.section_id = q.section_id
WHERE v.rubric = 'midterm' AND v.student_id = '7001234'
GROUP BY s.section_name

 

If you want his/her question values for the same exam

SELECT q.question, v.value
FROM value v
    INNER JOIN question q ON v.q_id = q.q_id
WHERE v.rubric = 'midterm' AND v.student_id = '7001234'

 

 

[odisey]

This looks great:

^{section          question          value                  student---------        -----------        ----------              ----------section_id  --+  q_id      ----+    value_id          +--- student_idsection_name  |  question      +--< q_id              |    studentname              +-< section_id        rubric (mid/final) |                                    value              |                                    student_id  >------+}

 

I just don't know enough about reading it.  Is this ont to many >-----+ for example?  Or the PK. 

 

 

[odisey]

This looks great in your example:

 

section          question          value                student

 

I just don't know enough about reading the notations.  Is this one to many >-----+ for example?  One to one?  Or the PK?  I see you are telling me how to structure tables, fields and ids.  I am not sure about how to read the >s -s and +s.  This is new. 

 

 

[odisey]

OK -  I studied this more closely.

 

section           question       

---------       -----------

section_id  --+   q_id

section_name 

                   |   question   

           

                   +-< section_id                 

                         midtermvalue                 

                         finalvalue

 

And I understand the --+--< now.  I can read your notation!  This is good! 

Now I have a question about your logic. 

 

The midterm and final values are represented in the table as field values in your example.  I had anticipated parsing (using queries in PHP) to gather the score values from each individual question in th db and then do math on them in a function on the front end to print out a report.  I am not sure why you represented the mideterm and final score values in the table - these scores are calculated only as some sort of function result of values input by the evaluator.  I mean the evaluator does not input a midterm score for example - the idea is to output a calculated midterm based on individual score values input. 

 

So - I am thinking there is a backend function I am not aware of which will automatically and synchronically tally the score values in these fields as individual scores are input.  Is this true?         

[Barand]

They are there purely because your opening question indicated that you wanted to store midterm and final values for each question.

 

However, as you now tell me

these scores are calculated only as some sort of function result of values input by the evaluator

then this original input data needs to be in the schema instead, so the mid and final values can be calculated.

 

So what does the evaluator input?

[odisey]

Now we are getting somewhere - I am pleased you have taken time to provide feedback.  From the join you constructed I can see you have a great amount of talent. 

 

I will explain the inputs.  The evaluator opens a web page - PHP - right.  There are a series of questions.  Ten global charistics.  I will outline two for space, and the model feedback you suggest should apply to ten anyway. 

 

In Question 1 there are 3 main areas that contain 2 questions each.  I know it sounds unorganized.  It is a rubric though.  So you can imagine -

 

QUESTION ONE

 

        Area 1

               1. The student was something ......  [Enter a value score]

               2. The student was also ........ [Enter a value score]

 

        Area 2

               1. Then the student was something ......  [Enter a value score]

               2. Then the student was also ........ [Enter a value score]

 

        Area 3

               1. Then after that the student was something ......  [Enter a value 

               score]

               2. Then after that the student was also ........ [Enter a value score]

 

QUESTION TWO

 

        Area 1

               1. The student something ......  [Enter a value score]

               2. The student also ........ [Enter a value score]

 

        Area 2

               1. Then the student did something ......  [Enter a value score]

               2. Then the student did also ........ [Enter a value score]

 

        Area 3

               1. Then after that the student did something ......  [Enter a value 

               score]

               2. Then after that the student did also ........ [Enter a value score]

 

Now keep in mind this same set of questions is actually evaluated 2 time over the entire year.  So the evaluator reads and answers all questions for a midterm grade.  After all the values are input in the midterm score answers - the front end will generate a score based on those inputs.  The final fields will remain zero at this point.  Then a second round for a final grade.  The evaluator reads and answers all the questions a second time.  All values for the midterm are retained.  New valued entered are their own independant variables and they overwrite nothing.  So you can image each quastion having a drop down for midtern score (0,1,3,4, or NA) and a drop down box for a final score (0,1,3,4, or NA). 

 

As scores are entered for each item I will report on the scores in PHP on the front end for each specific value for each question in eacr area - and a total midtern and final score for each QUESTION - and finally and midterm and final score for the entire lot. 

 

QUESTION

 

     1. As above and etc////

     2.

     

     1.

     2.

 

     1.

     2.

 

Front end:

 

     Report answers for question one midterm:

 

     1. 0 - Student needs help here

     2. 1 - Student passed but needs help

 

     1. 3 - Good

     2. 4 - Excellent

 

     1. 0 - Student needs help

     2. 3 - Good

 

Midterm total = 11   

 

     Report answers for question one final:

 

     1. 3 - Good

     2. 3 - Good

 

     1. 3 - Good

     2. 4 - Excellent

 

     1. 3 - Good

     2. 4 - Excellent

 

Midterm total = 11

Final grade = 31   

 

     Report answers for question two midterm:

 

     1. 0 - Student needs help here

     2. 1 - Student passed but needs help

 

     1. 4 - Excellent

     2. 4 - Excellent

 

     1. 0 - Student needs help

     2. 3 - Good

 

Midterm total = 12   

 

     Report answers for question two final:

 

     1. 3 - Good

     2. 3 - Good

 

     1. 4 - Good

     2. 4 - Excellent

 

     1. 4 - Good

     2. 4 - Excellent

 

Midterm total = 12

Final grade = 34 

 

 

 

Lastly - I will report a grand total midterm and final grades for the entire lot at the end of th ruberic.

 

Midterm grade score = 23

Final grade score = 65

 

 

The reason it is constructed this way is to track students progress through out the semester in each objective learning area.  And provide assistance for areas they are lacking in - and track progress from one semester to the next.  Thus - it is a ruberic.

 

Hopfully this will now make more sence.  Again I appreciate your help here.  I like to idea of performing a join as well if it can be accomplished. 

 

There are hundreds - to thousands of students this will be used with - over quite a few years.  Therefore I value your opinions - I see you are well experienced. 

 

Students data is entered into a students bio table with their id.  Actual questions are on the front end in xhtml.  Values through PHP forms are input to the db.  Reports are generated through db queries.  You know this - I am just running over it.

 

 

[Barand]

You originally had 10 sections with 3 questions in each.

 

Now you have 10 questions with 3 areas in each.

 

Is this just a confusing change of terminology and hierarchy or do we have

 

Section

  Questions

        Areas

[odisey]

I apologise - I had not intended on confusing the thread.  I originally thought if I simplify what I was building I could get good structure feedback and figure out the rest on my own.  After you shown me the join apporach - I had not anticipated - I decided to lay out the rubric as it actually is so you can have at it.  It is as I have outlined in the latest post.  I think I confused terms - that is not good for you and others.  I will clear that up here.   

 

For sake of a working model there are:

 

10 Questions.  We actually call them characteristics.  With in these ten there are three or more areas (in the first characteristic of 10 there are three areas for example).  And within these areas there are two questions.  I hope this is not confusing.  I had anticipated posting the actual questions on the front end and only dbing the input from evaluations through php forms.  The model is as I posted most recently above - such as: 

 

Characteristic one

 

           Area one

 

                         Q1

                         Q2 

 

           Area two

 

                         Q1

                         Q2

 

           Area three

 

                         Q1

                         Q2

 

Midterm score

Final score

 

And etc.....

 

 

              After ALL characteristics

 

                 Midterm grade score

                 Final grade score

 

             

[Barand]

I guess it would be something like this

[pre]

Characteristic      area              question          student_score        student       

--------------      -----------        ----------        ----------          ----------   

char_id      --+    area_id    --+    q_id      ---+    score_id        +--- student_id

char_desc      |    area_desc    |    q_number    +---< q_id            |    studentname

                +-<  char_id      |    question          student_id  >---+

                                  +--<  area_id            year       

                                                          mid_final (M/F)

                                                          score      >---+

                                                                          |    score 

                                                                          |    --------

                                                                          +---  value

                                                                                description

[/pre]

[odisey]

Now this looks good!  Yes I think genius - that's my vote anyway.  More complex logic I am learning yet - so you see I am here with questions and looking for feedback. 

 

I am still sorting your logic on the questions.  Give me a few hours - tomorrow I will reply.  I want to apply that join you have demonstrated - this is what I want to accomplish.  I can code all this upfront and have a ton of unnecessary queries upfront - I think I can anyway - on paper it looks good.  Getting the best logic and power out of MySQL functions is best though - especially for this. 

 

Thank you again!   

[odisey]

OK- I am looking at the table logic and of course thinking of queries that will produce the values I am looking for.  I have a few questions first.  

 

So we have a charistics table with an ID(PK) and description.  We have an area table with an ID (PK), description, and char_id(FK).  We have a questions table with ID(PK), question number, question, area_id(FK).

 

I am uncertain of the student score table.  You have a score_id.  And you also have another table called score.  Should the score_id in the student score table be a (FK) from the score table?  And in the score table the score_id = PK? 

 

 

 

[Barand]

student_score stores the scores entered for each student. score_id is just to give each record a unique identifier.

 

score table contains 4 records

 

val  description

------------------------

0  |  failed

1  |  passed needs help

2  |  good

3  |  excellent

 

and is used to interpret the score values, which is why it links to the score in the student_score table

[odisey]

OK - So where as other tables have PKs and representations in yet other tables as FKs, the score table does not necessarily need a PK or FK, as it is simply referenced (one of 4 values) to provide a value to 'score' in the student_score table. 

 

I plan on making this value a string or VARCHAR type because I would like to add one additional value - now that I see your method.  I had originially envisioned a different table - I like this logic much better though.  The other (5th) value is simply 'NA' or not applicable.  Do you think adding that option in the way I described is OK?   

[Barand]

The problem with making it non-numeric and having a value like "NA" is that you need to SUM this value for your reports.

[Barand]

I just tried SUM(varchar_col) containing

 

NA

7

NA

5

 

and it correctly gave 12, so you can do it that way. MySQL is lenient in letting you SUM a non-numeric column type.

[odisey]

I just tried SUM(varchar_col) containing

 

NA

7

NA

5

 

and it correctly gave 12, so you can do it that way. MySQL is lenient in letting you SUM a non-numeric column type.

 

Right - given your feedback though and considering the gravity of this project I am wondering whether it is a good idea to do it that way after all.  I don't want it breaking!

 

I will try to write the joins and I will post for feedback. 

 

Thank you for the expert help. 

 

I am learning here through your examples. 

[odisey]

I guess it would be something like this

[pre]

Characteristic       area               question           student_score        student       

--------------       -----------        ----------         ----------           ----------     

char_id       --+    area_id    --+     q_id      ---+     score_id        +--- student_id

char_desc       |    area_desc    |     q_number     +---< q_id            |    studentname

                +-<  char_id      |     question           student_id  >---+

                                  +--<  area_id            year       

                                                           mid_final (M/F)

                                                           score       >---+

                                                                           |     score   

                                                                           |     --------

                                                                           +---  value

                                                                                 description

[/pre]

 

OK - I am tring to write joins to get feedbck on:

 

1. Individual questions

2. Areas

3. Characteristics midterm

4. Characteristic final

5. Total midterm

6. Total final

 

using these as an example:

 

Suppose you want the section totals for student 7001234 in the midterms

 

Code:

SELECT s.section_name, SUM(v.value)

FROM value v

    INNER JOIN question q ON v.q_id = q.q_id

    INNER JOIN section s ON s.section_id = q.section_id

WHERE v.rubric = 'midterm' AND v.student_id = '7001234'

GROUP BY s.section_name

 

If you want his/her question values for the same exam

 

Code:

SELECT q.question, v.value

FROM value v

    INNER JOIN question q ON v.q_id = q.q_id

WHERE v.rubric = 'midterm' AND v.student_id = '7001234'

 

- I don't it yet. I'm not close yet. 

 

Is this on track?

 

Code:

SELECT Characteristic, SUM(student_score.value)

FROM value student_score

    INNER JOIN question q ON v.q_id = q.q_id

    INNER JOIN section s ON s.section_id = q.section_id

WHERE v.rubric = 'midterm' AND v.student_id = '7001234'

GROUP BY s.section_name

 

[Barand]

Assuming the tables are set up like I suggested

[pre]

Characteristic      area              question          student_score        student       

--------------      -----------        ----------        ----------          ----------   

char_id      --+    area_id    --+    q_id      ---+    score_id        +--- student_id

char_desc      |    area_desc    |    q_number    +---< q_id            |    studentname

                +-<  char_id      |    question          student_id  >---+

                                  +--<  area_id            year       

                                                          mid_final (M/F)

                                                          score      >---+

                                                                          |    score 

                                                                          |    --------

                                                                          +---  value

                                                                                description

[/pre]

 

Your query would be something like this, giving the student name and total score for each charateristic for that student

SELECT s.studentname, c.char_desc, SUM(ss.score) as total
FROM student_score ss
    INNER JOIN question q ON ss.q_id = q.q_id
    INNER JOIN area a ON q.area_id = a.area_id
    INNER JOIN characteristic c ON a.char_id = c.char_id
    INNER JOIN student s ON ss.student_id = s.student_id
WHERE ss.mid_final = 'midterm'
    AND ss.student_id = '7001234'
    AND year = '2006'
GROUP BY s.studentname, c.char_desc

[odisey]

Here is my guess -- for error analysis

 

SELECT charistic.char_desc, SUM(student_score.score)
FROM score.student_score
INNER JOIN question q ON student_score.q_id = q.q_id
INNER JOIN area a ON question.area_id = a.area_id
INNER JOIN char_desc c ON a.char_id= c.char_id
WHERE student_score.midfinal = 'midterm' AND student_id = '7001234'
GROUP BY charistic.char_desc 

 

 

I am going to build this now and start testing it.  I am getting an idea of where too start - you can see.

 

The little training I have has taught me that I can use aliase to simplify teh queries syntax.  You used one for 'AS total'

 

I am unsure of your code that uses the following:

 

SELECT s.studentname, c.char_desc, SUM(ss.score) as total

 

I have a question.  Is the s in s.studentname shorthand for student.studentname? 

And is SUM(ss.score) shorthand for SUM(student_score.score)

 

I am not sure if you are writing it this way to save notation time - and ramp up the intuitiveness for someone learning - or if it will actually execute that way.  I will try both the sorthand and long hand codes and see what happens. 

 

Do you have a resource for tutorials which lead the student through simple to complex table structures and query options?  If so I would like to study it. 

 

Thank you - Now I will give thiese tables a try and get back.     

[Barand]

there's this

 

http://www.w3schools.com/sql/sql_intro.asp

 

I prefer to use table aliases rather than keep repeating the table names oer and over, which, IMO, does nothing to aid readability and can be more error prone as somewhere you could misspell a tablename. With aliases you only have to type it once.Some times aliases are optional,as in this query, but in some queries there are required.

 

Without the aliases

SELECT student.studentname, characteristic.char_desc, SUM(student_score.score) as total
FROM student_score 
    INNER JOIN question  ON student_score.q_id = question.q_id
    INNER JOIN area ON question.area_id = area.area_id
    INNER JOIN characteristic ON area.char_id = characteristic.char_id
    INNER JOIN student ON student_score.student_id = student.student_id
WHERE student_score.mid_final = 'midterm'
    AND student_score.student_id = '7001234'
    AND student_score.year = '2006'
GROUP BY student.studentname, characteristic.char_desc 

[odisey]

there's this

 

http://www.w3schools.com/sql/sql_intro.asp

 

I prefer to use table aliases rather than keep repeating the table names oer and over, which, IMO, does nothing to aid readability and can be more error prone as somewhere you could misspell a tablename. With aliases you only have to type it once.Some times aliases are optional,as in this query, but in some queries there are required.

 

Without the aliases

SELECT student.studentname, characteristic.char_desc, SUM(student_score.score) as total
FROM student_score 
    INNER JOIN question  ON student_score.q_id = question.q_id
    INNER JOIN area ON question.area_id = area.area_id
    INNER JOIN characteristic ON area.char_id = characteristic.char_id
    INNER JOIN student ON student_score.student_id = student.student_id
WHERE student_score.mid_final = 'midterm'
    AND student_score.student_id = '7001234'
    AND student_score.year = '2006'
GROUP BY student.studentname, characteristic.char_desc 

 

 

I agree - mistakes are more likely with complex syntax.  You understand - not writing it and at a learning level - making sure of what I am reading reinforces the concepts of aliases, etc... 

 

I am building tables - I have the mid_final set to ENUM('M' , 'F').  I am down to the last table now.  This is something I don't understand yet - never read anything about it either.  I am unsure of the realtionship between student_score.score and score.value  It lookf like a PK FK relationship - it is not though.  I understand that this table has hard coded values with descriptions - and a value is called from this table to populate the score field container in student_score. -- I'm thinking it through  -- this should be simple.  Maybe it is an index relationship - I will try that first.

 

     

[Barand]

[pre]

score      >---+

              |    score 

              |    --------

              +---  value

                    description   

[/pre]

 

When entering scores you may want to provide a dropdown for each score

<select name='score'>
    <option value='0'>Failed</option>
    <option value='1'>Passed but needs help</option>
    <option value='3'>Good</option>
    <option value='4'>Excellent</option>
    <option value='NA'>N/A</option>
</select>

So the score table can be used to dynamically generate the menu.

 

Similarly, when reporting individual scores you may want to output "Good" instead of "3". This table also enables you to do this.