Introducing an 'age' category in a patients database 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?
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 &lt; 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.