Database Design: Year of Birth /Year of Death best practice - Postgres

[fbio]

I have a table of people (authors), and columns for Year_of_Birth and Year_of_Death. These are integers, as I wish to be able to compute things like current_age and age_at_death and age_when_first_published etc.

1. Clearly living authors do not have an Year_of_Death.

2. I don't have any birth/death information for at least some authors.

3. I have only a range of dates for some authors (eg, born 1560-1564)

4. For some authors, I don't yet have the information, but will fill in the columns in due course.

At the moment, these are all represented as NULL values.

My question is: Is there a best practice for distinguishing these potentially different column states?

For example, if I set living authors Year_of_Death as (say) 9999, and wrote a function to return current year in an age calculation when the year of death value is 9999, would that seem really hacky? (I really don't expect my database to last 7,977 years...)

Also, if I've searched for birth/death info and can't find it, and coded that as (say) 9998, I would at least know an attempt has been made, and could distinguish those rows from rows where I haven't yet looked for the data.

Or would a better solution be to add another column that codes e.g.'Living, Unknown, range x...y, Unsearched' to avoid polluting my year column with synthetic values?

Thanks for any pointers!

[tahishae]

OK - I've figured out this was a badly formed question. I was conflating (at least) two different questions:

1. What do you do when NULL can mean different things?

2. How do you store non-datatype data in a typed column?

The answer to (1) is: model the meanings you wish to store. And the answer to (2) is: place those models in other columns and tables. If your column is a series of integer facts, (eg, year of death) - don't overload it with coded integers, otherwise your whole app needs to understand the overloaded meanings.