Is this a bad approach to database design?

Question

I have to build an application for my university that will count as course credit for a Class that lasts 1 month. In this application I have to have a way for users to save a Teacher Class Followup Evaluation, which is a person goes to the classroom and checks out the teacher and ticks certain columns.

An example would be:

Pedagogical Aspects:
Show order and follows class sequence: YES NO Observations
Gives clear examples: YES NO Observations
Involves students in discussion: YES NO Observations

If the user (the one evaluating) chooses YES, then nothing is written in Observations, but if he chooses NO, he has to write observations without fail.

How could I handle this in my database? I'm having doubts about over normalizing. :x Any suggestion would be welcome at this point before I move on with the project.

My plan as of now is to just have a big table called Followup that has all these 'aspects' with a BIT datatype in Microsoft SQL and have a ShowOrderSequenceObservation field for every aspect that can be null. O_O I feel dirty just thinking about so I turn to you fellow developers. Thank you!

Answer 1

I would do something like this:

Table for the actual record - note that this is an anonymous recording from the student perspective

| record_id | question_id | YESNO | observation | teacher_id |

Table of questions.

| question_id | question_string |

Table of teachers:

| teacher_id | teacher_string |

In the general flow of things, I would also update the student table to note "has recorded" and insert the answers all in one transaction. This would preserve student anonymity yet also get the data in.

edit - I have no idea how I would ORM this thing. If I was developing it, I'd hack it out in 10-30 hours with perl and direct sql access. Most of the time would be spent beating on HTML formatting.

Answer 2

Sounds like the age old question of time vs quality. A denormalised table would certainly be fast and easy, but a normalised one with category and question tables would allow flexibility. You uni could use it for other things, allow new question types to be set up on the fly etc, and could get you a better grade.

If you think you can get what you want with a denormalised table, I'd go that way. It's not a production system and business needs aren't going to change in its lifetime. But if you want to push for the blue ribbon solution, I'd normalise it.

BTW, adding a < br > at the end of each option makes it more readable.

Answer 3

You know normalization isn't just for large enterprise level database (I know you know :). History has shown that if you don't normalize you will get anomalies. Start with 5NF and 'optimize' from there, though I suspect you will find that optimization is not required.

I suspect the propsed design will not suit its intended purpose e.g. data analysis. Try writing some typical SQL queries against it (e.g. average length of Observations across all Pedagogical questions then across all questions) and you will find it a pain: huge CASE statements, tables UNIONed many times over, ... it's likely you will end up writing VIEWs to normalise the data!