a user is a user is a user – database design fundamentals for marketers and programmers

Even in the mainframe days we had the golden rule that every user, person, student, teacher, whatever, on the system had a record in one (1) location.

Sure there might be descriptive data in another location. It might be a central table as simple as userid-username-password-role with a one to one relationship to a “role” table that then linked to the appropriate table for that role. Kung Fu Master versus Adjunct Professor. You get the idea. In Django this is the auth_users table with a relationship to the profiles table. But it is sooooo tempting to ignore the intention of the designers of Django, to break away from their DRY principles, and come up with a different system to reduce the number of records in auth_users. Despite lessons learned from Discus, the world’s largest Django application.

I followed that law, that a user is a user is a user, and wish I could credit it to the correct professor from my mainframe days on VAX using FORTRAN 4 and then FORTRAN 77.  I was taught as early as the 80s that there was always one (1) place for humans (in a database at least). When Microsoft Access came out and everyone became an amateur DBA they suddenly created tables for “students” and “teachers” and “staff” which required duplication of data all over the place. It became so brittle that if another programmer worked on your file they had to know every field in every table. In other words – it was pathetic. I swore I would never do that again.

But I did forget. My fault. You can’t blame the young guns with two or three years of experience as they haven’t suffered the “mile in shoes over broken glass” of permissions for humans in disparate tables. Which might work, until you hire the next programmer and they go “what the F were you people thinking….?” This one is all on me.

One user. One record….. – so simple and yet so profound. It is the singularity in database design. No exceptions

That rule, “one user, one record” served Schipul.com and our clients well recurring revenue and a nice margin with true database integrity for over 10 years. We all made a profit. And the software? “It just worked.”

As a public post so I don’t do something this stupid again in the future, I am posting these two pages from the book  Direct and Database Marketing by Graeme McCorkell. I used it with Tendenci 1,2,3,4.x+ and it served us well for 10 years. And then I forgot. Uuuuuugh. Double+plus+dumb points for me. And unfortunately this is hurting our clients through delays which is what really kills me.

One user. One record.

A user is a user is a user.

Here is to Tendenci 5.1 being pushed out in a few weeks. Major strides in the right direction. Back towards DRY and simplicity. Simplicity is best.