We've can provide relief and also the month of the yearly reoccurring event. (Think about it as being your day of they year once the invoice needs to be delivered to the customer.)

We will need to perform some computation with this particular information. E.g when the customer is having to pay two times each year. Then we don't just see if this very day is today but additionally if the day is within 180 day.

We now have a number of options: a) We save the data inside a DATE area but disregard the year. b) We create two int fields within the database date_day and date_month. c) We produce a varchar area after which perform a regex and split e.g. 31.12 up each time we all do some calculation.

We did several tests and discovered that c) is certainly not fast enough. But we still have the choice a) and b). First we took it for b) but after a little tests we tend more to some).

It is possible to good technical reason why would actually make one option much better than another?

(We're using MySQL right now. If this sounds like worth focusing on.)

I'd choose b), since it more precisely reflects this is from the data. Getting an information structure where certain parts are meant to be "overlooked" is problematic. Let's say people just perform a simple date comparison, presuming the entire year is definitely exactly the same, but someone used another placeholder year, presuming the entire year does not matter anyway?

Always employ data structures that reflect your intentions.

That c) isn't good is, In my opinion, beyond reasonable discussion :-). And I am not considering performance reasons...

I'd make use of the date area and despite the fact that unnecessary, would still save the entire year too. Just strip it if you need to print it / utilize it. You will find a couple of reasons:

  1. You may discover that the later point the client does would like you to save the date. For the reason that situation you do not have to inflict changes for your database structure.
  2. You should use the SQL date functions to check dates, as needed. For those who have day and month in separate fields, you'll need a much more code to e.g. calculate the main difference between two dates (leap years etc).

The reason why give for selecting b) may also be easily solved with individuals SQL date functions. It is simple to pick occasions inside a certain month, for example, in one query.

I'd keep date from the first event, after which an interval for every subsequent event, kinda like the majority of calendar applications. Within this situation, you'd structure it such as this:

 first_event | interval | interval_unit
-------------+--------------------------
  2009-01-01 |        6 | 'month'
  2009-02-01 |        1 | 'year'

Regrettably, MySQL does not come with an INTERVAL datatype, so two posts and a little of publish-processing is going to be necessary, however i think it is the most flexible method to approach the issue.

I'd also affiliate with b), but use TINYINT for month ( to 255) and SMALLINT (-32,768 to 32,767) for year in order to save a little of space.

Pick One int area like 1601 for The month of january, first.

I'd choose b), since it will make queries much simpler: you'll have the ability to restore all occasions inside a range (December, specific day, day range) in an exceedingly easy manner. When you purchase a) - be sure to set the entire year to some specific one to compare and extraction reasons.

I'd go during the day-of-year number (e.g. just one number from to 365, after which include that towards the first of the month of january from the particular year you are looking at.

If you won't want to perform the extra math of the aforementioned solution, then use two fields one for month and something for day (but make certain you update both when you really need to!).

Remember, you suffer from leap years, so utilizing a date area is an awful idea since you'd need to store dates with 2 yrs Body a leap year and something not - very complicated!