x

Database Design - Age vs Date

Our team has been discussing some design principles for collecting historical data collection. Specifically, we are trying to capture patterns of drinking.

For older people, it will be hard to capture exact dates/months, because it is simply just too hard to recollect that information. In this case age makes sense to capture, because really we probably don't know a date, or at least it would always be estimated (birthdate + 21 yrs, etc).

For younger people, it may be easier to recollect more precise dates/months, so we would have more precision.

Trying to keep these in the same design model, what would your suggestions be? Age, date, other, etc?

Thanks!
more ▼

asked Aug 25, 2011 at 06:58 AM in Default

iojohnso gravatar image

iojohnso
23 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

In general, I support going with actual dates rather than ages, but this is one of the exceptions. In our data processing, we go by age rather than date. Federal TEDS (Treatment Episode Data Set) requirements use age of first use for substance abuse tracking, as it can be very difficult getting exact dates down. There might be some subset of your population which does know exact dates, but that percentage is probably low enough that "date of first use" is too noisy a signal to do anything reasonable with. Even years may be hazy for some people, but it's more reasonable that somebody could remember how old they were when they started drinking than on which date they started.

The other reason why an age would be OK from a data modeling perspective is that it doesn't change. Unlike a "3 years ago" type of field, "age of first use" is static: a person who began drinking at 18 will always have begun drinking at 18. If you were to have a field which has "how many years ago did you begin?" that would be a problem. It doesn't sound like you're doing that, though, so that's safe.
more ▼

answered Aug 25, 2011 at 12:33 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Great response Kevin. Following federal standards is something we need/should consider. I'm 99% sure we will be going with ages, but will post back once we are back from the drawing board.
Aug 25, 2011 at 01:00 PM iojohnso
(comments are locked)
10|1200 characters needed characters left
I would go for age, unless the time-of-year will be used in the analysis (do kids drink more in the summer etc?). Then data analysis could be done without calculating the age. But it does really depend on how you will analyse your data, it's hard to say without knowing your data and your requirements.
more ▼

answered Aug 25, 2011 at 07:06 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

Good point Magnus. My guess is age is primarily going to be used for analysis, but if we were to collect more, we could always round up. In the case where we know more data, would we want to capture it just incase future analysis desire more precise dates?
Aug 25, 2011 at 07:23 AM iojohnso
(comments are locked)
10|1200 characters needed characters left
Are you building a system that is set to cope with personal data - a date of birth needs to be protected whereas an age is less likely to. If you have no other reason for the DOB then why not use age for everyone?
more ▼

answered Aug 25, 2011 at 07:03 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

This does deal with protected data, and we also already have the DOBs, so for internal use specific dates and DOB is fine, but if we were ever to report externally we would simply report in ages.

The only reason against using age is if we knew more precision, such as they stopped drinking on this date, then we would capture that. From here is where it ties into Magnus's response about how it is going to be analyzed...
Aug 25, 2011 at 07:21 AM iojohnso
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x28

asked: Aug 25, 2011 at 06:58 AM

Seen: 1308 times

Last Updated: Aug 25, 2011 at 06:58 AM