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开发者_JAVA技巧 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.

---

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.

---

I've always just used datetime value with the 1st of January/April/July/October representing each quarter. Makes computation of the start/end dates of the quarter simple:

• Start Date: the datetime column itself.
• End Data: dateadd(month,3,quarterColumn)

Another alternative would be ISO 8601. Here's an ISO 8601 profile for use in Internet protocols: RFC 3339 (proposed standard).

An ISO 8601 representation of each quarter of the year 2011 looks like this:

• 2011-01-01/P3M
• 2011-04-01/P3M
• 2011-07-01/P3M
• 2011-10-01/P3M

The above specify a duration by starting date and duration (in this case, 3 months).

The advantage of ISO 8601 date/time formats is that the strings are (A) human readable, (B) they collate properly, (C) they're easy to parse and (D) its an international standard.

Some people "extend" ISO 8601's week notation, where a week of the year looks like 2011W32 (the 32nd week of 2011), to a quarter notation. Using this unofficial extension, the quarters of the year 2011 looks like:

• 2011Q1
• 2011Q2
• 2011Q3
• 2011Q4

---

How about using computed columns based on the payment date? I'd rather do this than have both a date and quarter/year that might get out of sync. On the other hand, I suppose it's possible that you may need the ability to have a different year/quarter than the date indicates in which case you'd need to keep them separate. I'd at least think about using computed columns though as that seems the best way to ensure integrity.

---

For something so simple, I would just keep 2 int columns, and to build up the (pivotal) dates using dateadd when required to use date ranges.

Another option is a single date column, for which you can store the first day in the quarter, so the 4 dates in a year would be 1-Jan, 1-Apr, 1-Jul, 1-Oct. You can extract the quarter, year easily using datepart Q and Y.

---

How about two ints, one for the year, and one for the quarter (1-4). Is that what you meant by option "a"?

Option "b" would work, but you have to remember to maintain the table every year or so.

---

I agree two ints are fine.

I would add an index consisting of both columns in case you need to sort or filter by year and quarter.

---

You could even use a single tinyint. It's enough for storing in the form YYQ,like 111, 112,113,114, 121...for a few years.

---

Storing quarter and year in database depends on how your payment data is being organized. Examples would be; how many different payment values are being inserted. Will the quarter/year ranges vary? etc.

One good technique for "defining" a quarter/year range is making a separate table with a "DateTime" field that identifies a quarter. You don't need to join the table, you just need to do programming in C# to figure out if the range falls within a particular pay quarter.

For example:

Table 1: Payments
-----------------
paymentID (int)
paymentAmount (double(7,2))
paymentDateTime (DateTime)

Table 2: QuarterYear
--------------------
quarterYearID (int)
dateFrom (date)
dateTo (date)
quarter (tinyint)
description (varchar)

Example Data

paymentID | paymentAmount |   paymentDateTime
------------------------------------------------
    1     |    20.24      |  2011-04-18 08:14:20
    2     |    34.15      |  2011-04-19 07:42:15
    3     |    51.87      |  2011-04-20 13:04:22

quarterYearID |  dateFrom  |   dateTo   | quarter |  description
-----------------------------------------------------------------
      1       | 2011-01-01 | 2011-03-31 |    1    | first quarter
      2       | 2011-04-01 | 2011-06-30 |    2    | second quarter 
      3       | 2011-07-01 | 2011-09-31 |    3    | third quarter
      4       | 2011-10-01 | 2011-12-31 |    4    | forth quarter   

Example Query for getting all payments for "Quarter 2" dateValue is a dynamically pulled variable from the payments table. C# will handle 'dateValue' value.

SELECT quarter FROM QuarterYear WHERE cast('dateValue' AS date) BETWEEN dateFrom AND dateTo;