Many years ago, back in the late 80’s, one of my first big development contracts was to create a database for NAALAS - the North Australian Aboriginal Legal Aid Service (no longer around).
Basically, this organisation provided legal assistance for Aboriginal people to help them through the quagmire that is the Australian legal system. The database I would design would be a case management and recording system that would enable their legal team to keep track of the progress of their client’s cases through what could be a many year long arduous process.
On the surface, it was a fairly simple database design, and at the time, I designed it using dBase III before migrating it over to Clipper eventually.
But at the outset, I made one critical mistake in the schema design for the client record. I had all the standard ‘people’ fields such as first name, last name etc., as well as a standard date field to capture the birth date of the client.
And that was where things fell apart when we went ‘live’.
You see, there were quite a lot of Aboriginal elders on their client files, and it turns out that a lot of Aboriginal people born in outlying communities do not actually know the exact date of their birth. Most of them only knew the month and the year, and some older ones only knew the year!
But they still needed to capture as much as they could of the birth details, and dBase’s date field could not capture partial dates when the operators tried entering in 00/05/1958, or 00/00/1947 etc.
This means a last minute rush back to the drawing board where I had to break up the birth date entry field into three separate (optional) entry fields for the date, month and year, and then only complete the actual birthdate field programatically in the back end only if all three components were present.
Reports still had to be generated on these ‘dates’, i.e. all clients who were born between 1960 and 1970 etc., so there was some judicious workaround implemented in the system to still accommodate this using partial or complete dates.
It was an early lesson that stuck with me to this very day, and still influences all design decisions I make around database schemas and user interfaces.