DB design

Anon

When designing a database, does one need to take into consideration how the data is going to be presented in forms on a website?

For example, when a user fills out a form on a webpage, and one of the form fields is a drop-down box containing all the months in a year, what is the best way to represent this in the database? Would it be ok to just code it like this:

CREATE TABLE student_hours (

...

student_month varchar(15),

...

);

Thanks,
Chris

rickman

It's really a matter of taste. I usually have a table for each unique drop down box. The option tags have a value of an id number and display the text. Then, in the table I will store the selections in, I store the id number. I find adding new values to the drop down box easier if their values are in a seperate table. For your case however, I don't see us adding any new months in the near future so it would depend more on whether or not you want to translate it somehow later on - 12 to December for example.

xzilla

Another bonus of doing it in seperate tables (and imo as important as ease of updateing) is that this also helps you abstract out the information. You may need to reuse the dates in lists on several form fields, so you dont want dates tied to a specific form. heres an example a little more drawn out:

create table months{
month_id int,
month_name
}

create table student_hours{
student_id int,
num_hours int,
month_id int
}
(note student_id and month_id are both foriegn keys to thier respective tables)

create tables teacher_hours{
teacher_id int,
num_hours int,
month_id int
}
(note teacher_id and month_id are both foriegn keys to thier respective tables)