[RESOLVED] creating a relational database

knucklehead

Hello. I have some beginners' dilemmas about creating a relational database so any help is really appreciated. I don't ask you to code for me and make my sql queries but please help me understand some things.
So, at the beginning let's say that i would like to make a database with some schools' information (students, teachers, classes, grades...). My "main" table is called 'students' and it contains some basic information about a student:

| SID | first_name | last_name | class |    dob     | street | city | etc...
----------------------------------------------------------------------------
|  1  | studentF1  | studentL1 |   5   | 2147483647 | street | city | etc...
|  2  | studentF2  | studentL2 |   3   | 2147348421 | street | city | etc...
etc.

Other tables contain less information and some of them are only tables which describe some information. For example, table 'parents' looks like this:

| PID | SID | first_name | last_name | type   |
-----------------------------------------------
|  1  |  1  | parentsF1  | parentsL1 | father |
|  2  |  1  | parentsF2  | parentsL2 | mother |
|  3  |  2  | parentsF3  | parentsL4 | father |
|  4  |  2  | parentsF4  | parentsL5 | mother |
etc.

'PID' in this table is unique parent's ID, 'SID' refers to 'SID' from 'students' table.

Table 'grades' looks like this:

| GID | SID | subject | grade | type   |
----------------------------------------
|  1  |  1  |    1    |   4   |    1   |
|  2  |  1  |    2    |   2   |    1   |
|  3  |  1  |    3    |   4   |    1   |
|  4  |  1  |    3    |   4   |    2   |
|  5  |  2  |    1    |   3   |    1   |
|  6  |  2  |    2    |   2   |    3   |
etc.

In this table 'GID' means grade ID and it is unique value, 'SID' refers to student's ID, 'subject' is just a 'pointer' to another table where all subjects are described, 'grade' is actual grade and 'type' describes what kind of grade is it (oral exam, written exam, etc).
According to this I created few more 'needed' tables.... For example, table 'grades_description':

| GDID | level | description   |
--------------------------------
|  1   |  1    | insufficient  |
|  2   |  2    | sufficient    |
|  3   |  3    | good          |
|  4   |  4    | very good     |
|  5   |  5    | excellent     |

Table 'subjects' looks like this:

| SUID | description   |
-----------------------
|  1   | maths         |
|  2   | language      |
|  3   | chemistry     |
etc..

Table 'teachers':

| TID | first_name | last_name | subject |
------------------------------------------
|  1  |  teacherF1 | teacherL1 |    4    |
|  2  |  teacherF2 | teacherL2 |    3    |
etc.

Every occurrence of 'SID' in tables other than 'students' refers to 'SID' in that same 'students' table.

My first dilemma is:

did I set up my tables correctly?
I tried to break information to smallest pieces possible but I want to be sure if I set it up correctly or am I missing some basic logic here? =)
Should I keep it that way?

Second thing on my mind is:

how should i run queries to get all the information i need from a single student? For example, if i want to get some kind of associative array at the end of my script which looks like this:

student_first_name=>student1
student_last_name=>student2
student_mother_first_name=>parent1
student_mother_last_name=>parent2
student_grade_subject1_oral=>4
student_grade_subject3_written=>3
etc.

i should make really big query to retrieve all I need, like this:

SELECT students.first_name, students.last_name, students.dob, students.city, parents.first_name, parents.last_name, parents.type, grades,subject, grades.grade, grades.type (........) FROM students,parents,grades (....) WHERE parents.SID = students.SID AND grades.SID = students.SID AND grades.subject = subjects.description AND ........

Does my query really needs to be so huge or is there any other method of retrieving data from 'relational' model?

Uf, I hope I explained everything good =) Sorry for possible confusion and thanks in advance for any help =)

Kudose

With a complex (common sense to some) relational system, you should be using different kinda of JOINS to get the data you want with the most effective look up times.

The only thing in your setup I question, which is a topic of debate, is your need for the grades description table. This looks to be a small static table. It would save your database engine time if you hard coded those values into the system instead of making the DB join another table ... but like I said, this is a topic of debate and some prefer one way and others another.

Also, if you use MySQL, experiment with different SELECT queries using the EXPLAIN and EXPLAIN EXTENDED syntax to make sure MySQL properly optimized your query and is making the correct index choices before making the look up. Indexes will be a critical factor in your setup's speed as well.

Weedpacket

Kudose wrote:
With a complex (common sense to some) relational system, you should be using different kinda of JOINS to get the data you want with the most effective look up times.

As a matter of fact, an INNER JOIN is semantically precisely equivalent to the "Where t1.this=t2.that" construct; any sensible dbms would generate identical query plans.

As for whether the query itself needs to be that huge ... probably not. Criteria that are frequently used in multiple queries (e.g., when you're retrieving a student's parents' names given the student ID, you typically want the student's name as well) can be encapsulated by a view: looks and can be treated like just another table, but is actually the result of a query. So instead of having to join two tables to get those names (and hence require two criteria) you only need to join one view.

knucklehead

Thank you very much for your thoughts!

@
What you mean about that 'grades_description' is that I could drop that table and write inside my script some conditions which will describe my grades with some switch or if statements, right? It seems reasonable, yes, since that table is really static... There will be no new values in there....
I am aware about indexes and EXPLAIN statements for optimizing queries and will use them for sure to speed up things a bit =)

@
Hello, friend ;-)
Yes, after some thinking, I think that I will need a 'big' query only once, just to retrieve 'initial' information about a student. All other INSERTs and UPDATEs will be much simpler. But let me see if I get your idea about 'views' right:
I could make some 'constant' condition like this

$condition = "SELECT 
				students.first_name,parents.first_name,parents.last_name 
				FROM 
				students,parents 
				WHERE 
				students.SID = ".$studentSID." AND parents.SID = ".$studentSID;

which will retrieve student's and parent's names. After that I can use that condition in other queries... Did I get it right? I'm just a little bit confused of how to 'reuse' such a thing =) I'm going to try google it to see if it will become clearer =)

Weedpacket

Views are a database thing; you create them in a way analogous to the way you create a table.

CREATE VIEW students_parents AS
SELECT
    students.id as id,
    students.first_name as student_name,
    parents.first_name as parent_first_name,
    parents.last_name as parent_last_name
FROM
    students, parents
WHERE
    students.SID = parents.SID;

Something that looks a bit like that, anyway.

Then later on you can write queries using that view as though it were a table (which looks exactly like what you get if you run that SELECT directly).

That's a pretty simple example, which doesn't do that much (it really only does what it says it does), but the SELECT statement that defines a view can be as complex as any other SELECT statement, and they never have to be seen outside the database.

Your DBMS's documentation should have a chapter or few on views, how to make them and what they're good for.

knucklehead

I will mark this resolved. Thank you for all your advices and thoughts! I was googling around for CREATE VIEW in mysql and I found it very interesting! It will help a lot for sure =)
Thanks again! =)