Formalize your data!

As I approach semi-retirement (i.e., voluntary poverty) , I am thinking about future projects.
Two things are the bane of my programming existence: user authentication and site administration.
Authentication should be so simple, if some folks weren’t downright evil! I can’t think like them, so I tend to not even try, relying upon social media authentication (what are they doing watching me??) or a package (like Cake, one of my favorites).
However not so easy on the administration side. I pondered why we spend so much time creating the perfect database but then squander all that knowledge when creating admin sites. Why not use the expert knowledge that’s already there in the database schena?
With a certain amount of discipline and consistency automated forms should be readily available for all tables and relationships!
I created a protocol that I call “formalizing”, which transforms column and relation details into dynamic data entry forms, along with some simple query and reporting tools, for any table in a properly formed database.

It starts with the foundation

I am currently working on a volunteerism initiative, and will use that application as the basis for my examples. The Use Case:
– Event sponsors want to hold an event (a fun run, a food festival, a church service etc).
– Each event has a specific time, date, and location
– Each event has one or more specialized roles (check-in, monitors, cooks, setup, breakdown, security, etc.), which may have prerequsities for volunteering
– Each role will require one or more volunteers.

I am a big fan of orthogonality, making tables as small as possible and linking to other small lookup tables for values. My general rule of thumb is that if a field has only 50% unique data in it, create a new table and link to it. This makes for a lot of tables (though I once created a two-table database, one of the main identifier, and all other “attributes” contained in one table with an “attribute_type” attribute field, but that’s a story for another day..). These tables may themselves require normalizing, requiring links to yet more tables. Example: My “location” table, used by the events table, contains links to “Address One”, “Address Two”, “City”, “State” and “Zip” lookup tables. Overkill? Perhaps, but you get the idea.

I am also a big fan of recursion, calling on a function within itself. Example: To create a new event, the sponsor could start all over, re-entering the name and location, re-creating the role assignments, and re-assigning prior volunteers. What a hassle. Alternatively, the sponsor could just take the expired event and change the date to the next week, at the loss of key data (you lose the ability to look at history). Instead, let’s use cloning to take the current event, and create a brand new one where the sponsor only has to change the date.
When a new event is created, all child tables that use the event id as its parent need to have new records created in their tables, linking to the new parent event id, etc.

People are not computers! Computers love to link things digitally (i.e. by numbers). People think in words. So let’s not make people need to remember the computer stuff, but give people a way to interact with the computer stuff.

So let’s add a few more requirements
– A given table should have the ability to “clone” itself, including cloning of child data
– A simple human-friendly filter should be created to find a given record in the data sea.
– A human-friendly report writer to enable the sponsor to download existing details should be included (also to keep them from contacting me;->).

Stay tuned as I share my experiences incorporating this design into a “one page application” website!

Next: Architect a formalized database