Best way to store quarter and year in SQL Server?

Question

What would be the best way to store Quarter and Year in database? I have payments table and I need to assign quarter/year so that it's easy to tell for which quarter the payment was made.

I was thinking on:

a) adding two int columns to each payment
b) adding another table and add possible values up to 5 years ahead and use the ID to join that table with payments one.

What are other options? Maybe some is better or/and easier to maintain. This database will be used with C# program.

Answer 1

If you have to use separate year and quarter instead of a date (since you seem to have specific reporting requirements), I would go for a tinyint for quarter and smallint for year and store them in the PAYMENT table itself.

I would not store it in a different table. This is bad since:

• You have to make sure you have produced enough years/quarters
• You have to join and use a foreign key

If you store the data with the record, it will help performance on reads. Your table could be small but it is always good to keep in mind performance.

WHY

Let's imagine you need to get

all payments in specific quarter where payment has been more than specific amount and customer is a particular customer

In this case, you would need a covering index on all items and still does not help since your query is for specific quarter and not quarter year. Having the data on the table, however, will help with lighter execution plan.