Page 1 of 1

Database Structure...

Posted: Sat Dec 12, 2009 12:04 am
by Klutz403
Ok, the original version of my post is quite long...
Here's a short version :

Am I better off with a few (4-5) small tables or one great big table?

The option of using a few small tables gives me more flexibility but is it really much slower?
According to this presentation on scribd, "1000 small very quick unnecessary queries is worse than 1 slow query".

Help?

PS : The long version...
Hey guys,

I'm coding an exam bank for my student association and I'm unsure about how best to structure my database tables...

Each Exam has to have the following info :
Faculty, Department, Program, Course #, Course name, Professor's name, Year, Semester, Exam name, File name

I'm hesitating between two ideas...

A) One big table with each row being an exam, with the Faculty, Department, Program, Course # and Course name being stored as text

B) Four separate tables for Faculties, Departments, Programs and Classes, each row containing, for example, the Program's ID (a sequential index), the Department ID it's part of, it's abbreviation and it's full name.
And, one big table with each row being an exam, with the Faculty, Department, Program and Course # being stored as index numbers to the 4 other tables


The advantage I see in A is that I only run 1 SQL query to get all the info about a given exam and if I make Faculty, Department, Program and Course # indexes, I can quickly get listings for exams from a given Departmens, Program...

The disadvantage I see in B is that I'd need to run multiple SQL queries, although they'd all pretty much be simple index lookups (which are quicker than getting stuff from a textual index like in A?). The advantage to B is that it let's me do more stuff, makes listing the Classes from a given Program or Department much simpler, and simplifies management for certain tasks (class changes name).


I'm leaning towards B for the extra flexibility but I'm not sure if the time costs associated with multiple SQL queries will kill me if I start generating mucho traffic!


Opinions?

Re: Database Structure...

Posted: Sun Dec 13, 2009 3:28 pm
by Darhazer
Go with the small tables, unless your load is so high that you can't serve your content (millions of views per day)
Actually, you don't need separate queries, you can use JOIN in the query to get all of the data with single query.

Re: Database Structure...

Posted: Sun Dec 13, 2009 3:35 pm
by Klutz403
Sweet. Yeah I found out about JOIN queries and was told to go with B by someone else as well!

Thanks for the advice!