Using smalldatetime or int to store month in database
I am currently developing a monthly checklist system for our organization. A user can log in, select a month, and then submit a list of yes / no questions related to that month for our organization's purposes. Some of the questions have been used in the checklist for over 1 month, so I am creating an intersection table to facilitate this one-to-many relationship. Fields: ChecklistMonth and ChecklistQuestionID.
However, I'm not sure how to store the ChecklistMonth field. If I am using smalldatetime it seems a little overkill since I am only interested in the month. It will also be slightly out of date in the future. On the other hand, it seems a bit wasteful to create a table with MonthID and Month fields to only identify the month.
What it is? Thanks in advance.
a source to share
If it's a month excluding the year, I would just use TINYINT. I don't think you need a separate lookup table as the numbers of the months are quite different and versatile (not falling into Chinese or Hebrew calendars, etc.).
If you are using any datetime, you should always remember the exact rules around it. Do you store it as the first day of the month? Average day of the month? At what year? In addition, this is an extra, unnecessary room used in the database.
EDIT: I thought I already added this to my answer, but apparently don't ... remember to add a validation constraint to the column:
CHECK (month BETWEEN 1 AND 12)
a source to share
Bite the bullet, use MonthID. This is the best solution in the long run as it is clearer and the waste of having a transfer table for a month is trivial.
(And by the way, the decision to use a month-by-month listing table, while some might think it is unnecessary, is correct, I think.)
a source to share
I would store it as an int. I agree with you that smalldatetime is overkill and may be confusing in the future. Not to mention, you still have to pull out a month to check if this month has your request.
Looking for a cross reference table? Your MonthId should be 1 = January, 2 = Feb. Up to 12. I think that a field that has a month number is self-grabbing and doesn't require additional lookup tables. Assuming you are of course only dealing with one calendar.
a source to share