SQL Design question...

[Entity]

I'm converting our student database for my department from access to mysql right now, and am going through the old design and trying to redesign it to make it more efficient, etc. I'm stuck with a bit of a problem right now...

Just so y'all know, here's a picture of the current DB structure:

http://pichosting.pcthike.com/geekphotos/newstudentdbmodel

The question I'm really having is with the "Grades" table. Right now, with the way it is setup (I'm importing the data from Excel, so it's just a flat file right now), there are thousands of entries -- 3120, to be exact. This table will, of course, keep growing as our students take more classes; I'm afraid that at a certain point, running queries on grades on this table will become inefficient. In the grades table itself, the rows "Year" and "Student #" are duplicated many times over. I'm not sure, though, how to design the table to be more efficient; the only thing I've thought of so far isn't really a design change as much as a general change -- taking out the "Year" column and separating the Grades table into Grades-2002, Grades-2001, etc.

Any better ideas?

thanks,
Rob

 

[bunker]

I have tables with over 300k records. As long as you build indexes all will be fine.

 

[manly]

A few thousand records isn't very many. All you'll probably need is an index on the student foreign key column.

What you're referring to is database normalization (informally to reduce duplication of information), which you can look up in any good RDBMS reference. In this case, it doesn't seem like much of a concern at all.

What utility are you using for design btw? Standard disclaimer: I haven't done ER modeling in ages, so I'm not an authority on this subject. Personally, I would not partition the table unless you can show there is a serious performance problem.

 

[Entity]

Thanks, guys. All the design I've done so far is small and done by hand. This latest model was done in visio, but that's only so I could show our faculty what it looked like, without having an ugly sketch drawn up. The student id column will be indexed, so I'm not too concerned.

Rob

 

[Haircut]

As the others have said a few thousand rows is no problem.
We have some tables at work that get 1 million rows a month added to them, with good indexing accessing them is still pretty quick.

I would keep the grades table the way it is now. If you modify it so that you have multiple tables for different years then you end up with a situation where, if you want to find all grades that a particular student got over their entire course, you would need unions between multiple tables.

Something that I would point out though is in the admissions table. You have 3 different lots of
School, Barrons, State, Degree and GPA in this table.
IMHO you would be better separating this into another table.

Also, you seem to have quite a few different tables where studentID is primary key, is there any reason not to use one table for all of these?

 

[Entity]

Originally posted by: Haircut
As the others have said a few thousand rows is no problem.
We have some tables at work that get 1 million rows a month added to them, with good indexing accessing them is still pretty quick.

I would keep the grades table the way it is now. If you modify it so that you have multiple tables for different years then you end up with a situation where, if you want to find all grades that a particular student got over their entire course, you would need unions between multiple tables.

Something that I would point out though is in the admissions table. You have 3 different lots of
School, Barrons, State, Degree and GPA in this table.
IMHO you would be better separating this into another table.

Also, you seem to have quite a few different tables where studentID is primary key, is there any reason not to use one table for all of these?

The three tables where StudentID is the PK are separate because there will be a number of blank columns in the latter two (DegreeInfo and Admissions) which are rarely needed; most of the queries will not need to pull information from either of these two tables. However, since they are still on a 1-1 relationship with the Students table (and studentID), there was no real need to create a separate PK for them. I'll think about that Student/Barrons/School thing you pointed out; I might be able to create another table for that, though that might be more trouble than it is worth.

Rob