Best way to save only day and month in database
database, database-design, date
Solution
I'd choose b), because it more accurately reflects the meaning of the data. Having a data structure where some parts are supposed to be "ignored" is problematic. What if people just do a simple date comparison, assuming the year is always the same, but someone used a different placeholder year, assuming the year doesn't matter anyway?
Always use data structures that reflect your intentions.
That c) is bad is, I believe, beyond reasonable discussion :-). And I'm not thinking of performance reasons...
Problem
We have to save the day and the month of a yearly reoccurring event. (Think of it as the day of they year when the invoice has to be sent to the client.) We will have to do some computation with this information. E.g if the customer is paying twice per year. Then we do not only have to check if this day is today but also if this day is in 6 month. We have now several options: a) We save the information in a DATE field but ignore the year. b) We create two int fields in the database date_day and date_month. c) We create a varchar field and then do a regex and split e.g. 31.12 up every time we do some calculation. We did several tests and found out that c) is definitely too slow. But we still have the option a) and b). First we wanted to go for b) but after some tests we tend more to a). Is there a good technical reason that would really make one option a lot better than the other? (We are using MySQL at the moment. If this is of importance.)