Introducing an 'age' category in a patients database

Question

I want to do the following in my DB related to patients:

• when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
• how can I translate the birthday into age category?
• how to design that age category?

Answer 1

Some of this depends upon your particular database implementation. The part that is general, however, is the idea of an age category. Here's one implementation, using ID as a surrogate key and written to work with SQL Server (meaning you would probably need to alter the syntax a little bit to get it to work with another product):

CREATE TABLE dbo.AgeCategory
(
    ID tinyint identity(1,1) NOT NULL,
    MinimumAge tinyint NOT NULL,
    Name varchar(20) NOT NULL
);
ALTER TABLE dbo.AgeCategory ADD CONSTRAINT [PK_AgeCategory] PRIMARY KEY(ID);
CREATE UNIQUE INDEX [IX_MinimumAge] ON dbo.AgeCategory(MinimumAge) INCLUDE(Name);

INSERT INTO dbo.AgeCategory(MinimumAge, Name) values
(0, 'Newborn'),
(1, 'Infant'),
(2, 'Toddler'),
(5, 'Child'),
(12, 'Adolescent'),
(18, 'Adult'),
(30, 'Some marker'),
(40, 'Something else'),
(50, '50+'),
(65, 'Retiree'),
(90, 'Amazing');

I've populated this with sample ages and categories. Here's an example of how you could use this table. I'm going to populate a Person table with dates of birth, and then get a query to find their age category.

Here's the sample data:

CREATE TABLE dbo.Person
(
    ID int identity(1,1) NOT NULL,
    Name varchar(50) NOT NULL,
    DateOfBirth datetime NOT NULL
);

insert into dbo.Person(Name, DateOfBirth) values
('Bob', '1983-01-08'),
('Mary', '1976-05-05'),
('Jane', '1960-04-01'),
('Tony', '2012-08-16'),
('Marcy', '1955-06-11'),
('Carl', '1930-12-24'),
('Joseph', '1918-11-11');

Here's the query:

with people as
(
    select
        Name,
        DateOfBirth,
        datediff(day, dateofbirth, current_timestamp) / 365.25 as YearsOld
    from
        dbo.Person p
)
select
    p.Name,
    p.DateOfBirth,
    p.YearsOld,
    a.MinimumAge,
    a.Name as AgeCategory
from
    people p
    cross apply
    (
        select top 1
            ac.MinimumAge,
            ac.Name
        from 
            dbo.AgeCategory ac
        where
            ac.MinimumAge < p.YearsOld
        order by
            ac.MinimumAge desc      
    ) a;

Note that this query works for SQL Server, but I can't guarantee the syntax will be the same for any other product (or that this is a relatively efficient way to solve this problem for any product other than SQL Server). What we're doing is calculating the age in terms of years based on the date of birth. Then, we can apply that to the age category table using the CROSS APPLY function. This will return the one best age category for a particular row.

Note that you could modify the AgeCategory table to have two age values: MinimumAge and MaximumAge. That would get rid of the cross apply statement and replace it with an INNER JOIN, but the downside there is that you might end up with overlapping rows: if you have a minimum age of 10 and maximum age of 20, and then another row with a minimum age of 15 and a maximum age of 25, you'll have two results for each person between the ages of 15 and 20. The single-entry table gets rid of that problem without having to introduce a trigger or data check other than a unique index on the age column, but might be more costly in terms of performance with large enough data sets.

Answer 2

For SQL SERVER, a very simple way of adding this column is as a computed column, which will be non-deterministic due to the CURRENT_TIMESTAMP dependency.

alter table PATIENT add AgeCategory as
    CASE
    WHEN DateAdd(Year,1,DOB) > Current_Timestamp then 'infant'
    WHEN DateAdd(Year,4,DOB) > Current_Timestamp then 'toddler'
    WHEN DateAdd(Year,10,DOB) > Current_Timestamp then 'children'
    WHEN DateAdd(Year,14,DOB) > Current_Timestamp then 'tween'
    WHEN DateAdd(Year,18,DOB) > Current_Timestamp then 'teenager'
    WHEN DateAdd(Year,65,DOB) > Current_Timestamp then 'adult'
    ELSE 'elderly'
    END;

i.e. For this table:

CREATE TABLE PATIENT
(
    ID integer primary key,
    FullName varchar(255) NOT NULL,
    DOB datetime NOT NULL
);

insert into PATIENT(ID, FullName, DOB) values
    (1, 'John Doe', '1900-01-01'),
    (2, 'Jane Doe', '1960-02-03'),
    (3, 'Joe Public', '1990-05-06'),
    (4, 'Bob the Builder', '2007-08-09'),
    (5, 'Ecco the Dolphin', '2009-12-31'),
    (6, 'Foo', '2012-01-09'),
    (7, 'Bar', '2012-11-11');

The following query:

SELECT * FROM PATIENT

Returns:

ID          FullName             DOB                     AgeCategory
----------- -------------------- ----------------------- -----------
1           John Doe             1900-01-01 00:00:00.000 elderly
2           Jane Doe             1960-02-03 00:00:00.000 adult
3           Joe Public           1990-05-06 00:00:00.000 adult
4           Bob the Builder      2007-08-09 00:00:00.000 children
5           Ecco the Dolphin     2009-12-31 00:00:00.000 toddler
6           Foo                  2012-01-09 00:00:00.000 infant
7           Bar                  2012-11-11 00:00:00.000 infant

---

For all other RDBMS I would probably recommend going with a view with an additional column, since AgeCategory cannot be persisted - by nature it can change between one select and the next depending on timing. The date manipulation functions are different for each DBMS, but the pattern would be similar.

e.g. Here's the version for SQLite (SQLFiddle)

create view PatientWithAgeCategory as
    select ID, FullName, DOB, 
        CASE
        WHEN Date(DOB, "+1 year") > Current_Timestamp then 'infant'
        WHEN Date(DOB, "+18 year") > Current_Timestamp then 'children'
        WHEN Date(DOB, "+65 year") > Current_Timestamp then 'adult'
        ELSE 'elderly'
        END AgeCategory
    from Patient;