[SOLVED] Please add constructive feedback!

odisey · October 21, 2007

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 · October 21, 2007

[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 · October 21, 2007

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 · October 21, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 23, 2007

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 · October 24, 2007

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 · October 24, 2007

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 · October 24, 2007

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 · October 24, 2007

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 · October 24, 2007

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 · October 24, 2007

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 · October 27, 2007

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 · October 27, 2007

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 · October 27, 2007

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 · October 27, 2007

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 · October 27, 2007

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 · October 28, 2007

[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.

Sign In

[SOLVED] Please add constructive feedback!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information