Page 1 of 1

counting from a database

Posted: Thu Aug 20, 2009 3:08 am
by Hillu
Hi,

I am working on a school database and I was trying to count the number of male and female regular students belonging to each academic year.

In my database I have three tables:
Student…….. Containing all student information
Batch……….describing to which academic year a student belongs
Student_history……containing all the terms the student attended class

Since I need only the students who are attending class in the given term I will be using student_history table.

The batch of a student is stored in a string format code for example, DRB0802.
The first three letters describe if the student is regular or not. The next two digits indicate the year the student joined the school and the last two digits tell the term the student joined the school.

I decided to use two queries one for the male students and the other for female students.

Code: Select all

 
$query_female = "SELECT COUNT(s.student_id) AS femaleNum 
                        FROM batch b ,student s , student_grade_history sgh
                        WHERE sgh.term_id = $lastTerm 
                        AND sgh.term_year = $reportYear
                        AND s.student_id = sgh.student_id 
                        AND s.batch_id = b.batch_id
                        AND s.gender = 'Female'
                        AND (SUBSTRING(batch_code,1,5)= 'DRB".$selectedYear."' OR SUBSTRING(batch_code,1,5)='DAB".$selectedYear."')
                        GROUP BY s.gender";
                        
 
 
 
$selectedYear is the year the student joined the school.
$reportYear is the year the report is being generated.
$lastTerm is the term the student attended class last.


Any suggestions are appreciated.
Thanks a lot.

Re: counting from a database

Posted: Thu Aug 20, 2009 3:12 am
by VladSun
0. Normalize your DB design! :)

Things like:
The batch of a student is stored in a string format code for example, DRB0802.
The first three letters describe if the student is regular or not. The next two digits indicate the year the student joined the school and the last two digits tell the term the student joined the school.
are NOT even close to a good DB design.
Split your "string format" into several table fields (with appropriate types - DATE, DATETIME, ENUM, etc.)

Then try to rewrite you query and see the differences ;)

1. Use [ sql ] BB code tags instead of [ code ] (without spaces) when posting SQL code.

Re: counting from a database

Posted: Thu Aug 20, 2009 9:03 am
by Hillu
Hey, thanks a lot but I am not allowed to modify the database because there are other programmers working on other modules and some of the functionalities are already released.

Any other suggestions?

Re: counting from a database

Posted: Thu Aug 20, 2009 12:57 pm
by VladSun
You should talk with those programmers about normalizing your DB and you really should agree that it must be done ;)
While there are some modules released, there are more to come.

Things like this:

Code: Select all

(SUBSTRING(batch_code,1,5)= 'DRB".$selectedYear."'
can not use indexes, so your queries will become slower and slower as the DB grows up.
Not to mention that your queries are quite complex now.

Re: counting from a database

Posted: Sat Aug 22, 2009 1:35 pm
by Darhazer
USE:
[sql]batch_code LIKE 'DRB".$selectedYear."%';[/sql]
instead of SUBSTRING()
In this way you can use index on the field.

Re: counting from a database

Posted: Sat Aug 22, 2009 2:18 pm
by VladSun
That would require too long index key length. Better normalize the DB.

Re: counting from a database

Posted: Sat Aug 22, 2009 2:30 pm
by califdon
I fully agree with VladSun. A properly normalized database is not some kind of theoretical ideal, it is a practical necessity in every case! People without database experience often argue "I'd like to do that, but ..." I cannot ever accept such an argument. The relational database model was developed by Dr. E. F. Codd around 1970, based on mathematical set theory, and has strict rules that are still the basis of SQL and every relational database that exists. To violate those rules is like saying "Yes, I know that I really should follow the traffic laws and drive on the same side of the street as everyone around me, but I got started on this side, so I'm going to just go ahead and do it my way." Sorry, but I have no patience with that.

Re: counting from a database

Posted: Sat Aug 22, 2009 2:35 pm
by Darhazer
VladSun wrote:That would require too long index key length. Better normalize the DB.
You can use partial index:
INDEX(batch_code(5)) will index only first 5 characters of the column

I do not argue that DB normalization is better, but this is solution for someone who cannot change the DB Schema.

Re: counting from a database

Posted: Sat Aug 22, 2009 3:02 pm
by califdon
Let me make myself clear: I am not criticizing ~Darhazer for being helpful to the questioner, that's what we want the forum to be, helpful, and he is consistently helpful here. My comments were aimed at whoever is responsible for the database that the questioner is trying to work with. But I feel that, as a responsible resource for PHP and database technology, this forum needs to highlight underlying problems, even when they are not apparent to the questioner. Also giving a workaround may be justified, but I felt that I had to emphasize that there's an egregious flaw in the schema that was presented, and that I strongly encourage the questioner to bring it to the attention of those who are responsible for its design.

Re: counting from a database

Posted: Fri Aug 28, 2009 6:35 am
by forerx
The next two digits indicate the year the student joined the school and the last two digits tell the term the student joined the school.

Re: counting from a database

Posted: Fri Aug 28, 2009 12:14 pm
by califdon
If you (forerx) are the same person as the original poster (hillu), please identify yourself as such. We are suspicious of multiple accounts here.