Subscribe
Share
Share
Embed
Software development process typically follows a series of steps that start with gathering requirements, developing a technical response, architecture, reviews, then construction. Reality slips in surprises and occasionally messes up the order.
"The Soul of an Internet Machine". This show explores the intersection of business and technology and the internet.
2 | DATA TABLES
During the introduction to this project, Dimi, our project manager, informed Stevie and me, that we are stepping into a new application. I loved, and love, this idea. Starting from scratch is my favorite. Then, of course, the truth peaked in. I got handed (we got handed) a robust and massive list of data table structures. Literally hundreds of data table definitions written in Oracle’s SQL syntax. Syntactically, these tables were perfect. And they were done by a person who possessed expertise in Electrotest’s business. He knew and understood the process of doing health/safety inspections in Belgium. And he had decades of experience with Oracle.
Therefore, I got pissed off.
Right, that makes perfect sense. But now I must justify my frustration and discuss it. Discussing it means admitting that I was wrong. The video calls with Dirk became uncomfortable for all participants. Stevie cringed. I behaved badly. From the first instant, I had been impressed with this man’s openness, friendliness, professionalism, and his personal honor.
Let’s look at how software is designed and built. Software is written by people. We invented the entire process. We invented the computer hardware. We invented the programming languages. We invented all of the processes involved with creating. When we human invent stuff, the roots of these inventions get planted deeply into arbitrary and artificial landscape. I do like my rules and my process. I often fail to recognize, what I embrace is both arbitrary and artificial. I am the only one who cares – to clarify, I am the only one who cares about my rules and my process. I don’t live and walk around in Dirk’s skin. What an ass I can be.
Software development follows certain common practices. Software, such as the stuff we write, automates business practices of following rules, tracking money, storing documents, and guiding human beings through various workflows. Our objective is to model the process of business. Typically, we engage while an organization undertakes structural changes. Prior to our arrival people engaged in performing their tasks with a legacy system: paper, Excel, other software, or maybe even nothing. When we are successful, we’ve have brought these business processes to life on pages within a web-based application – we build an app. We build software that models business processes. As we do this, we simultaneously strive to improve process. We desire to bring consistency to the data and to the process. I love transparency with these processes. One of the long-term projects, I worked on with Stevie to reduce the risk of fraud while managing complex government grants, a product called Tempest-GEMS.
How does this happen?
It requires a team, maybe two teams. On one side, we have one or more people expert in the business process. They represent the client. On the other side, we have one or more people expert in building software. We negotiation and plan together. To describe this as a linear checklist it sounds like this:
1. Someone gathers the client’s requirements
2. We examine the requirements finding consistencies, inconsistencies, patterns, and flows.
3. We model this back to the client
4. We all agree this is good enough to start
5. We make a first draft, proof of concept, that the team can evaluate independently
6. We improve it
7. It faces real data and real users
8. We revise
9. Lather, rinse, repeat
Throughout my career, the jargon and process changes like fashion. Waterfall, revised waterfall, iterative, agile, sprints, epics, stories. People get super dogmatic about these processes and names. Those who have achieved mastery take a more pragmatic approach. My friend and colleague, Stevie, spent two years on a project with a large multi-national bank. They embraced the Agile Software Development so tightly, it appeared they invested more time in process management than in software development. At the end of the day, we must produce a working webpage, update data, generate a beautiful and legible report. Nobody gives a hoot about the sprint, the epic, the epoch. Frankly, in the end, nobody gives a rot about the requirements document. The product matters. Did the client get a tool that works? That’s the only measure. No matter how well a team completed tickets in a project management tool such as Jira or others.
The goal must always focus on creating, building, and supporting a product. Some teams get too wrapped up the management of, tracking of, evaluating, and minutiae of the team management aspects. Meetings called “stand-ups” have become so long and so routine that people sit down. We originally called them “stand-ups” to keep them short. Hold a scrum or a huddle share a quick word on immediate goals, then get to work. Standing once reinforced need for brevity.
Instead of getting client requirements in December as a means of starting the project, I got handed hundreds of data table definitions. Oh, what did I just say about pragmatic versus dogmatic. Instead of collecting business requirements, workflows, and business objectives, I got handed some intermediate product out of context. We skipped the normal order of a process I had invested a career in mastering. Furthermore, someone took a job from me I enjoy. The process of designing tables feels like laying the foundation for a new building. We start projects there. Furthermore, I believe designing data structures follows earlier steps of requirements gathering and requirements document. Ok, call them stories, or epics. I got the results of step 2 without any hint of what happened to step 1.
Years ago, long in the early days of my career, I would slip away to the yacht club to race small sail boats about three days a week. I read my sailing magazine and had an office window that just barely let me see the sailboat harbor. One of the sailing magazines had a cartoon drawing of a small pirate boy. Picture a schoolboy with pirate clothing and a parrot. He is standing at the blackboard writing 100 times: Rape, pillage, then burn. Rape, pillage, then burn. That image reminds me that order does mater. You can’t burn the village first. In the army, people occasionally recognize that some bad leaders got things organized in the wrong order: fire, aim, load. It just can’t be done that way.
In my human brain, I believe, based on decades of experience that the design of data tables follows the process of documenting business workflows and understanding the user’s needs. Here I held a series of text documents with the phrase: “create table” written 132 times.
Picture yourself building a chicken coop. You think about the space and what the chickens need. Then someone arrives with all of the materials pre-cut. What I expected to draw and build – you know “my coop” must use these pre-cut bits of wood from someone else’s design. Furthermore, for this project, we got no instructions and no plan. Just the raw materials, just the structure of 132 data tables, the core fabric of a software application.
“This is wrong” screams my brain. First, we get a few requirements, etc. Step A goes before Step B and Step C. I should not admit but earlier in my career I tried saying: “rape, pillage, then burn” in a few meetings to emphasize the value of sequential order. Learning to say: First A, then B, that appears less offensive to others – except for those idiots who skip A, put B first, or don’t even care.
Poor Dirk. He had no idea of the sin he committed. Don’t worry, I made sure he understood by being both rude and inhospitable. The lovely man committed a second sin by failing to read my mind and not know the standards my team had in place. I’ll return to this sin in a bit. Ok, not-a-sin. And Dirk is a good man with good intentions and good ideas. He’s smart and well good looking too, but saying that in a work environment is as inappropriate as my small pirate boy story.
With my experience I could read these data tables and read the data presented in other formats and reverse engineer the process. I could do this while simultaneously seeing flaws in the not-a-design. How?
How can I look at hundreds of tables and do this?
Accounting systems, invoice generation, inventory systems have been around for decades. The architecture remains consistent. They are based on analog processes well established by accounting traditions. The buzz words are ERP and CRM: Enterprise Resource Planning and Customer Resource Management (or Contact Resource Management). These systems date to the earliest days of commercial software. The first real test for “computer” systems – I put computer in quotes followed this progression:
1. Calendar Calculations including celestial positions. These calculations help ocean navigators know their position on the globe. Think: Astrolabe, even Stonehenge. Estimate of eclipses, moon phases, and seasons.
2. Census tabulation – That’s where Herman Hollerith made is mark at the turn of the 20th Century. His thing eventually became IBM.
3. Ballistic Trajectories – how much energy does it take to launch a thingy from here and land there with precision. Hey, it was World War II, we wanted to hit a few targets. This stuff tied into the post-war Space Race. During the space race, we used early computers to aim a rocket at the Moon.
4. Accounting.
Customer have told me that paper ledgers and pen is good enough. I live in an old traditional town in Vermont. I gave up counting the number of times I have heard: “It’s been good enough for three hundred years, not sure why I need to do it differently.” So, except for our ancient volunteer fire chief, most people view balancing a bank account with an abacus as quaint and archaic. Some still use paper-based ledger books (except for our fire department who adopted Quickbooks in 2018 when the fire chief’s ex-wife stopped managing the funds).
I deeply appreciate Dirk’s willingness to listen to me and attempt to adopt our team’s long-held standards. We still clashed and I proved myself chilly and unfriendly about his genuine efforts to incorporate our techniques with his.
This ability to read a data table structure and infer the relationships with other tables is one skill I have. The second is that I can see gaps and holes in the logic. Many of us have this ability within our realm of expertise. I know doctors who can look at person and guess that they have diabetes based on the impact the disease has on the body; or emphysema; or congestive heart failure; or a stroke. My friend Andy who has farmed all of his life and served New England as a professional shearer and herd manager, he can look at a sheep and diagnose issues from a meter away.
We call this expertise. I am expert at seeing data structures and workflows for back-office business functions such as managing funds, managing documents, and managing process. I am terrible at other things. Like Andy and some physicians, the important elements jump forward.
To do that, I possess a mental image for standard or common (or healthy). I compare what I see against what I expect.
Sounds super grandiose and even pompous doesn’t it. I can see benefits and flaws in a data structure by reading 5000 lines of table structure definitions. I would expect an experienced radiologist to see a fractured rib in a millisecond or two. I would expect a tissue pathologist to see cancerous cells on a microscope slide.
I cheat!
Our team’s standards and practices allow me to cheat. We incorporate visual and logical patterns into each effort.
Stand by for a few minutes of geeking…
What is a data table in Oracle.
Data tables are an organization of data arranged typically in columns and rows. We call rows “records” occasionally. Record, or a row, contains a set of data related to a precise element – typically uniquely. We often call columns “fields” and sometimes even page items (as they are data entry items on a web page). Because of the nature of a relational database, the data elements in the table can be subjected to rigorous tests and validation processes. We can specify that a field be not-null, meaning data are required, always. We can specify default values such as Y for yes or 1 or 0 or whatever. For example, most of our tables have a field called “active”. We set the default to ‘Y’ (for yes) and we require some data in there by setting the field/column to “not-null”. We could go further limiting the data values to be either a Y or an N.
Furthermore, one table can relate to another table. I have been discussing invoices since starting this series about Electrotest. When one creates an invoice, the invoice sent to a customer. One customer gets one (or more) invoices. Therefore, we create rules in the invoice table that says:
1) Customer link must be populated, may not be null
2) The customer link must always be connected to or related to an actual customer.
With these rules, we maintain the integrity of the data. Data elements that refer to other data are required by the Oracle (or other databases) to maintain the referential integrity. This power is amazing. This power differentiates our glorious world from spreadsheets such as Excel. Listen, I use spreadsheets. Barely a workday passes without me opening it. Excel is a spreadsheet. Oracle is a database. Oracle has a few spreadsheet-y features. And Excel has a few database-y features. The tools remain distinct. Vive la difference!
I love writing my Oracle and technical blogs. I love training new folks on our team, yet rarely do I get to start with this fundamental discussion of how data are stored. I invested time in learning these concepts. Oracle and other database vendors invested years in improving these features.
What power to we derive from creating a formal link between an invoice and the customer to whom is has been sent? First, the rules ensure that every invoice has a valid customer. You’ll hear in later episodes that we interact with an external system where we have no such confidence. Database rules can be created such that if a user tries to delete a customer, the user is not permitted because an invoice exists. Or the software developer can create a rule that says: If you delete the customer then delete all related invoices.
Imagine an invoice or a sales receipt from your favorite store. Given I live in Vermont the home of Ben & Jerry’s Ice Cream, I’ll make believe I visit their store. I buy ice cream for me, for my husband and our Rachel. My receipt, my invoice, has three lines on it. One line for each of the separate ice cream treats. In this case, my invoice has three lines. We have a relationship between the Invoice now containing three detail lines. Each of the detail lines must store a reference or a pointer to the invoice.
In our lingo, the customer is a parent to the Invoice. The invoice record is a parent to the three invoice lines. With the right rules created in the database, if I delete the customer, the invoice gets deleted as does the three invoice lines. The delete process cascades down through all relationships.
Of course, we can decide to have a rule that says if a customer has an invoice, that customer may never be deleted. We do not permit the cascade delete. We use the rules of the database to reinforce the operational rules within the business. These are simple and common examples. Complexity follows fundamentals.
What are these links between invoice and the parental record or row for the customer? We call them “keys”. The imaginary is perfect. A key is specific and unique. Imagine the customer table. The first field in our table would be the customer’s primary key. It gets assigned automatically. It is unique within that customer table. No other customer can share that unique key. With a unique key, we can then let the users change every or any element of data for a customer. Name change? No problem. Tax identification number change? No problem. As long as that unique key remains the same, the database remembers this customer is always this customer.
We call this unique key for each row: the primary key. It is the number one unique identifier for the customer. It is a unique ID to some, although I tend to not use “ID” as a term. “ID” lacks precision. Know what our team calls a primary key? PK. The first field in the customer table is called “customer_pk”. No matter where I see that phrase in code, in an application, dropped on the floor, stuck on the refrigerator it is always and precisely exactly one thing. All ambiguity has been removed. It is the primary key for the customer. It is unique. The primary key may be ugly, lengthy, a jumble of numbers or numbers plus letters. It is the number one handle for picking up and manipulating the customer data.
The imagery of a key is perfect. If I have the customer_pk, I can immediately find and unlock (in a figurative sense) the customer’s data. It is reliable pointer. The Oracle database, as well as database from other vendors, use rules and tools to ensure the values are unique. We can have them automatically generated. We never have to show the client it exists (although we often do).
Returning to our invoice example, we now know we have a table called Invoice. What sort of fields are in the invoice table? Sure, you know them, don’t you? Just look at any sales receipt or invoice: invoice date, invoice amount, payment terms, due date, purchase order reference, maybe a memorandum or note to the customer. A nice concise summary of the entire invoice. With our team, the very first field in our invoice table is the invoice_pk. Right, I know you already knew that. But do you know the second field in the invoice table? I’ll pause…
The second most important information about the invoice is the link to the customer. Therefore, we put the customer link as the second field. If I am in the customer table, then my unique identifier for the customer is the customer_pk. If I am in the invoice table, then my unique identifier for the invoice is invoice_pk, the primary key. When I want to reference another table’s primary key, then that reference become my foreign key.
To restate that, my invoice table has a foreign key pointing to the customer_pk. We abbreviate foreign key “FK”. When I use the customer’s key in another table, it is called customer_fk. The first field in my invoice table is invoice_pk. My second field in the invoice table is: customer_fk.
If I see customer_fk in code, on a page in an application, stuck to the floor, or it falls on my desk (which of course it cannot do), I instantly know that this data field is formally, permanently and linked to the customer_pk.
When software developers develop standards like this, then we simplify these structures. The rules become visible. Reading 5000 lines of data table definitions, I do not have to memorize anything. When I find a table called “invoice” then I expect to see the primary key first, and the most important foreign key links following neatly and logically.
We start simple then proceed to complicated. What foreign keys should an invoice have in this environment?
Customer Foreign key
Payment terms
Invoice language – what language to print the invoice in?
Invoice method – email, postal service?
The phase or status for the invoice? Draft, finalized, paid, etc.
We create rules within the database that make sure that the users may not generate an invoice for Electrotest in Portuguese. (Or course, we could do them in Portuguese, but the client doesn’t want Portuguese – their clients may get invoices in French or Dutch). So that is also a rule we reinforce with database rules: your choices are limited to French or Dutch – unless of course your development staff speaks and writes in English. We snuck English in for ourselves.
We have tables where every field has a constraint or a rule. This field must be filled in. This field is defaulted to Y or today’s date. This field must reference a foreign key.
In December of 2021, Dirk handed over 5000 lines of table structure definitions. He heard my bit of a rant regarding field naming conventions and table naming conventions, so he tried to superimpose our team’s standards on his work. He did the best he could and came close. Every table had a primary key called PK and it was in the first line. Regrettably, the name did not include the table name: instead of invoice_pk, the field was called “PK”. In the 5000 lines, there were 132 fields named “PK”. I can’t tell one PK from another PK. If “PK” fell on my desk or I saw it in code, I would have to dig just a little bit further to discover who’s PK this is. Whereas invoice_pk is unique. I have a table called invoice and that table has a primary key. That primary key is invoice_pk.
I love that degree of precision. We removed ambiguity.
Additionally, as I read through table structures I develop the web-like structure that connects these data. The invoice details (or invoice lines) are related to their parent invoice with an invoice_fk (invoice foreign key). The invoice data is related to the customer with the customer_fk. And on and on. When done well, anyone can read through a table then know which fields are related to which tables due to the naming convention.
Ok friends, time for an argument.
A huge number of professionals in this industry use “ID” instead of either the phrase “PK” and “FK”. Ultimately, the argument between these standards resembles the sort of arguments nobody enjoys such as which religion is better? Which of the gods is the true god? Who makes the best wine? It is stupid and non-productive argument. If systems work and works well, then who cares! But of course, I am right.
Not true. I am not right.
There is story of Stephen Spielberg building and using a consistent team year over year, decade after decade. Teams develops shorthand for complex concepts. When you reach for something or someone, it is there. The purpose of a team’s rules reinforces consistency therefore quality improves. One of the team may chat at another asking for a “rubber duck”. “Hey Stevie, will you be my rubber duck for a sec?” I am asking her to act as a sounding board while I talk through a process in spoken or written language – so says Wikipedia. Another request maybe for “four-eyes”, a request made when engaging in a complicated, high-risk maneuver that may go horribly wrong. You check with your partner before making each step. Another shorthand phrase is: PST meaning primary, secondary, tertiary. For each evolution, an experienced practioner must have a primary plan, Plan “A”. A backup plan, Plan “B” and some way to completely bail out and recovering, a contingency plan or Plan “C”. We must be free to challenge each other with the question: “What are your PSTs?”
As I write this episode, young Eli struggles with writing code that copies the data related to an inspection. Two tables were created by our project leader, Dimi. He came so, so very close to following the standards, but missed. Field names and table names lack our team’s consistency, that perfect symmetry Stevie, Eli, and I depend on. It slowed Eli by at least one hour. He had to keep all of the table structures visible while writing the code. Two tables had been named with similarly. We forget the difference between the two tables. Second, one table’s primary key was typed differently when it became the foreign key in the other table.
My father, when talking about writing said: Maintain Parallel Construction. Parallel construction techniques lend to punchier writing. Picture a dynamic preacher delivering a sermon with rhythm. You can anticipate; the preacher pulls you along – or so I recall, been a while since I stepped into a church for a sermon.
We create these patterns deep within the buried infrastructure of code for the power it brings later in construction. These techniques improve efficiency. These techniques reduce the risk of errors. You don’t accidently refer to the wrong foreign key, the wrong table.
Let me share one example of these consistencies, or rather inconsistencies. During the years, I implemented several rules. The first rule is that all tables are named in their singular. I totally and completely agree that the invoice table stores multiple invoices. Frankly, it stores all invoices. Therefore, if the table contains multiple invoices, shouldn’t we name the table INVOICES with an “S” and plural. Certainly, go right ahead. As the cool kids say: You do you. The customer table stores all the customers, so call that table CUSTOMERS, with an “S” and plural. Suddenly every table has an “S” on the end. The result is that every table name just added one letter, the same letter, the letter “S”. If we name the table CUSTOMERS, then is the primary key customer_pk or customers_pk. If the table is customers with an “S” then the primary key ought to also have the “S” resulting in customers_pk. Step to a new perspective. Look at the data from one row, from one record. The primary key relates to a single customer. I’d argue that name ought to be customer_pk, in the singular. To maintain parallel construction, call the table CUSTOMER. Why not. Don’t add the “S”. What do you gain? What does it cost? Furthermore, you shortened the typing of hundreds of tables by one letter. That can be more than a ten-percent savings in typing.
Our friends at Oracle introduced me to this trap and these inconsistencies. They gave us a virtual table, or a memory-resident table called an APEX Collection. When you add elements to a row in an APEX Collection, the word collection is always singular. The functions and procedures that manipulate collections use the singular: apex_collection. My fingers get really good at typing these phrases. Typing and spelling long ago passed from a cognitive skill to a muscle memory skill.
Later in the code, I must write a query to extract data from an APEX collection. Suddenly, the word is a plural. I must write select from apex_collections (with an “S”) on the end. I never use an “S” when building the data set. I add data to an apex_collection – singular. I delete data from an apex_collection - singular. But I query from apex_collections for my lonely singular set of data. The phrase become plural. It becomes as simple as how fast we type and how frequently we make mistakes while typing. When I forget the “S” on apex_collections. I compile the code. Oracle blows an error. I click to find the error. I fix the error. Then I compile. It is the same friggin’ thing: apex_collection and apex_collections except for that one letter. That one letter cost the client a minute or two of wasted time.
Maintaining parallel constructions permits folks like me to fly with confidence. I don’t need to look up in some dictionary or reference material to confirm the spelling of a field or the like.
Similarly, we developed a rule with tables and field names: No abbreviations. I implemented that rule when we had a team member from south Asia and one from Canada who is fluent in five languages including Czech. Part of our team spoke Spanish as their primary language at home. Everybody abbreviated differently. Date became DT, DTE, DATE. Someone abbreviated invoice to INVC, removing the vowels. Then another abbreviated invoice to INV. Another person decided not to abbreviate spelling INVOICE. Nothing like stopping to look it up. Oh, gee how did we do it this time? Stop it. It costs time to look up the answer. It costs us emotionally when we face our own mistakes or must re-evaluate the mistakes someone else made that caused us to trip. Stop it.
But INV is shorter than Invoice. Yes, it is. Now use your phone as a stopwatch. Tell me which is faster. I need to find the invoice number. Is invoice abbreviated or written out fully? Let me stop and go look. I confirm. Now I type. It gets more costly, if it the phrase varies frequently between tables: here it is INV and here it is INVOICE and here INVC. When varieties exist, then we provide a pathway for bugs. Bugs are bad. Frustrations while coding are bad. Time remains precious.
There I am ranting about data table structures like a proper geek. Possibly what I just said made no sense and you ran away from me never listening to another episode. Oh well. Ciao pisano! Grazia mille.
Possibly, you are a customer looking for a team. I posit that a modern-day non-technical person ought to be able to recognize good thinking and logical thinking in every step we make, even these hidden fundamental building blocks.
Clear thinking results in clear code. Good thinking results in good code. I expect elegance. I expect symmetry. I expect to see architecture at work. I expect to see the beauty of the Brooklyn Bridge on the page. Our brains love these patterns because they often enforce the strength and resilience of our work. I currently spent hours per week watching crafts people making things on YouTube. A Spanish clog maker, an Irish stone carver, a woman from Colon rebuilding a 120-year-old home. While a timber frame house structure displays its own structural beauty, you ought to find the same thing with well crafted software.
Sorry, Dirk that I got frustrated. Those 5000 lines of data table definitions without the client’s requirements yielded frustration. As my little pirate boy cartoon never wrote: First A then B then C. We are building tools that reinforce process. Our response involved skipping Step A, the first step. Let us all skip ahead to “B”. Here are the tables. Now build a system. Bedankt mifn vriend.
Dirk, let me also apologize for my treatment of the tables. I acknowledge you tried and did well to come close to our standards. I never thanked you properly for that. The unstated component of my frustration resulted from the constraints these 5000 lines of data table definitions placed on us. The document got handed to me with an order: Execute!
I granted myself the authority to bring them closer to our team’s standards. My flexibility was curtailed. I had been told the client approved of these as is. Therefore, you must use them. Step 1 be damned. Context and requirements be damned. Start here and move quickly.
I also secretly knew that the client had spent time and money striving to create custom software to solve their problems in the past. I rapidly, privately, secretly deduced that these “approved” tables resulted from earlier failed efforts at building the custom software. My team and I had been mandated to build a structure on a foundation that I suspected came from past failures. Not my foundation. The tables and structures maybe perfect. Maybe the data structures caused past failures. Maybe the data structures were clones of past failed attempts. I could not know in the first weeks on the project.
I did not know the client and at that point had no access to the client. I am a software developer, a geek, working from a lovely home office in the rural mountains of Vermont.
Stevie and I, as the “pros from Dover” had been called in to take over. We possessed decades of real-world experience in managing billions in financial transactions. We’ve managed terabytes of digital documents with an Oracle database. Suddenly, with this new project we got told to use these structures from the previous team.
Instead of recognizing the conflict and verbalizing it to the growing team, I got all human about it. I snarled and snapped at people. Do you suppose I’ll learn to improve my interpersonal skills some day? I haven’t yet. That’s for damn sure.
In fact, Dirk’s work was good and accurate enough for us to start. We delivered a preliminary model and framework to the client by the first week of January 2022. I might have been rude and horrible, but we delivered a remarkable framework and first pass at the application before the end of the first week of January 2022.
In the next episode, I will explore how we built our framework and created an application based on nearly 100 tables in a few weeks. I attribute that rapid progress to Dirk’s early work and our team’s expertise and skills with Oracle APEX.
See you next time. Be well, do good, and have fun.