Database Design Opinions Needed

[GoldenEagle]

I am working on a database for college professors that will allow them to check graudation requriments for each of the students that they advise. We have the following datafields for each student:

StudentID
FirstName
LastName
MiddleInitial
PhoneNumber
Address
EMailAddress
Classification
Major
Track
Transfer
CatalogYear
AcademicStatus
GPA
DSUGPA
MajorGPA
TotalHours
QualityPoints
HoursDSU
Hours300
HoursJC
HoursOtherSC
HoursCorr
HoursCLEP

My question is: should I break down the database further? All those fields rely on the primary key, so it is in 2NF. But not all those fields will be on the main form (doing a VB front end) and if we broke it down it would making searching faster.

[MikeVic]

If you want the faster searching, what about splitting the fields up into whatever searches would be for? For example, one table could be just the name and address stuff, and another table with all course information? I don't know what some of these fields mean, but maybe even breaking it down further into "Hourswhatever" being in a different table as well?

StudentID
FirstName
LastName
MiddleInitial
PhoneNumber
Address
EMailAddress

StudentID
Classification
Major
Track
Transfer
CatalogYear
AcademicStatus
GPA
DSUGPA
MajorGPA
TotalHours
QualityPoints
HoursDSU
Hours300
HoursJC
HoursOtherSC
HoursCorr
HoursCLEP

[GoldenEagle]

That is what I was thinking about doing. The only problem is the StudentID would be the primary key in each of those tables.

[MikeVic]

Originally Posted by GoldenEagle

That is what I was thinking about doing. The only problem is the StudentID would be the primary key in each of those tables.

I don't have crazy experience or anything, but I remember reading that yes, normalization of databases is good... but sometimes there are cases where not following normalization for the sake of performance arise. Maybe this could be one of those cases? I don't know, just offering my 2 cents.

[rjolley]

Depending on your DBMS, there's nothing wrong with having all of this data in one table. I've often had tables this wide or wider with no performance issues. Plus, the columns wouldn't be wide enough to majorly impact performance.

Since you're going to retrieve the information listed as the second table often for the application you're writing, keeping it all in one table shouldn't be aproblem. However, if the load gets heavy, depending on your DBMS, you may have to worry about page or table level locks with one table.

So, unless you plan for this application to be heavily used, I'd stick to one table. I don't think a table split would buy you much.

(Splitting the table doesn't constitute 3NF, if I recall my classes. Storing 'Computer Science' as a major in this table vs. storing 42 in this table and having a Majors table that had 42,'Computer Science' does. But I may remember that wrong.)

[MikeVic]

Originally Posted by rjolley

(Splitting the table doesn't constitute 3NF, if I recall my classes. Storing 'Computer Science' as a major in this table vs. storing 42 in this table and having a Majors table that had 42,'Computer Science' does. But I may remember that wrong.)

Yeah, I believe you're right there... you're talking about look-up tables.

[Antmeister]

Well to determine if you want to split the data into other tables, you have to ask yourself which fields are going to have duplicate data. Whatever fields those are should be seperated into another table and referred to by its primary key. The only fields that look like they may qualify are:

Classification
Major
Track
Transfer (unless this is a boolean field)

The reason why I would put each of these fields in its own tables is to use them as look-up tables so that the user could add more categories later on. If they are numeric fields, the duplication rule doesn't usually apply.

I also like to put student info in its own table just in case they want to expand the database at a later date which is the information below:

StudentID
FirstName
LastName
MiddleInitial
PhoneNumber
Address
EMailAddress

[Riggins44]

One table is my vote. Sounds like it will always be a one to one relationship (student to graduation info).

[Franklinnoble]

You could use SQL 2005 express (it's free) and have more indexing options...

[Franklinnoble]

http://www.microsoft.com/technet/pro...ads/books.mspx

You can get a really good "owner's manual" for SQL 2005 for free here.

Download the docs, and read the section on index concepts.

[Antmeister]

Originally Posted by Riggins44

One table is my vote. Sounds like it will always be a one to one relationship (student to graduation info).

I must be anal because I try to limit the amount of info a person has to type to avoid errors, variations in the wording. and to cut down on duplicate data whenever possible.

That creates problem when you making queries later on. Plus I always try to make it easy to expand the database later, because it almost always happens.

[GoldenEagle]

I am thinking about taking the GPA fields out because they can be easily calculated. I am still not decided on the table split as of yet.

Franklin, I wanted to use SQL Express, but my sponsor insisted on on my group using access.