Using a smalldatetime or int for storing a month in database

Question

I'm currently developing a monthly checklist system for our organization. A user may login, select a month, then submit a list of yes/no questions relevant to that month for our organization's purposes. Some of the questions are used in more than 1 month's checklist, so I'm creating an intersection table to facilitate this one-to-many relationship. The fields are ChecklistMonth and ChecklistQuestionID.

I'm unsure of how to store the ChecklistMonth field, however. If I use a smalldatetime, it seems a bit overkill, as I am only interested in the month. It will also look a bit dated in future years. On the other hand, it seems a bit wasteful to create a table with the fields MonthID and Month in order to identify only the month.

What is everyone's opinion on this? Thanks in advance.

Answer 1

If it is a month without regard to year, I would just use a TINYINT. I don't think that you need a separate lookup table since the numbers of the months are pretty distinct and universal (without getting into Chinese or Jewish calendars, etc...).

If you use any sort of datetime then you always need to remember the exact rules around it. Are you storing it as the first day of the month? Middle day of the month? What year? Plus, it's extra, unnecessary room being used in the DB.

EDIT: I thought that I had already added this to my response, but apparently not... remember to add a check constraint to the column:

CHECK (month BETWEEN 1 AND 12)

Answer 2

Bite the bullet, use the MonthID. It's the better decision in the long term, as it's clearer, and the waste involved in having an enumeration table for Month is trivial.

(And by the way, the decision to use an enumeration table for month, while some may consider it unnecessary, is, I think, the right one.)

Answer 3

I'd store it as an int. I agree with you that smalldatetime is overkill, and could be confusing in the future. Not to mention you'd still have to pull the month out to check if it's the month your querying for.

Do you need a cross reference table? Your MonthId should be 1=Jan, 2=Feb up to 12. I think having a field which has the month number is preety self documenting and doesn't require additional lookup tables. Assuming that your only dealing with a single calender here of course.