date representation in user interface

Question

Most of the sites capture the date (e.g. birth date) information as either a single entity (i.e via a calendar control) or instead as separate fields (date, month, year).

1. Are there any benefits in showing them as separate fields?
2. Are they represented in the database as DATE data type or as separate fields.

I am just trying to understand the pros & cons of representing the date field.

Answer 1

Never break up a date into separate database fields. It's one thing: a date. It's in the database as a single date field.

You don't break up an amount into separate database fields for dollars and cents. How would you do math? You'd have to reconstruct the number from the pieces.

You don't break up other numbers into separate database fields for individual digits. How would you do math? You'd have to reconstruct the number from the pieces.

The same is true for dates. You rarely need the pieces. When you do, simple functions will provide the pieces. You more often need the entire date to do math with.

Further, the pieces of a date (specifically the month and day) have such complex relationships (28 to 31 days per month based on seemingly random rules) that you never want to attempt date arithmetic in your own application software. Use libraries. Use the database libraries.

The UI display may have separate fields to make it "easier" on the users. [Personally, I find a lot of fields to be a lot of visual clutter.] But the database should not have separate fields.

Answer 2

It depends on how you intend to use the data. If you will be simply displaying it or if you need to figure out the age of someone from the date, store in one date field. If you need to to see who has a birthday in May, then the separate fields might work better. If you need to do both, then store both ways though calcuated fields top avoid the cost of having to scrape out parts of the the date continually.

Answer 3

It's easier to code the page if you do it as separate fields. But it's definitely better to do them as a single entity - better user experience.

You could use either representation in the DB. I personally would probably combine them back into a single date, so that all the related data was encapsulated in as small a unit as possible.

Anything that you need to check (like the birthday example above) can be done easily when stored as a single date field as well.