Business Tech - Computer Memory…the Other Kind

When we say "computer memory" we expect that people are thinking about ROM, RAM, maybe EPROM, or even firmware. That's how we are trained to think about the term; as an aspect of the hardware. I'd like to put a more human spin on the term for this column. I want to talk about what the computer does, and does not, remember.

Show Your Work

In the simple electric calculators, "memory" becomes a content term, not a hardware term. It is the way we hold on to more that one numeric thought at a time. Imagine if we designed our primary business systems to work like a high powered version of the no-frills electric calculator: people key in data all day, but our machine can only retain one train of thought — one equation — plus a few completed values. Once the thought completes, and we add or subtract the remembered values, all we have is a result.

Tomorrow, when the boss asks for the total sales of all divisions over the lifetime of the business, we have the answer. If she asks any other question, we have to start data entry all over again because this sort of simple computer remembers none of the components.

Database, DUH!

In order to answer questions on the fly, we shove everything into a database. This is a database-oriented magazine, the readers are database professionals…. Why'd I even bring it up?

Because I keep finding system after system, using MultiValue and non-MultiValue data storage schemes, where the computer has not been taught to remember enough. Is this a sign of bad intent? No. Sloppy, stupid staff? No. It is a sign of people not building a set of completeness rules into the database design phase. We tend to — especially given the tight deadlines, small staffs, and real-world pressures — design to the current need. Unless we design with an eye toward the future, we have a problem when new questions are asked.

My crystal ball is in the shop. I do not have a sure list of upcoming needs. How do I future-proof my data as much as possible? I make sure it speaks in complete sentences.

Becoming Complete

Mark, our first customer, calls up and orders our bestselling product, the Fribbulator. The result of this one phone call is:

• customer record,
• ship-to record,
• order header record,
• order detail record,
• invoice header record,
• invoice detail record,
• packing slip head record,
• packing slip detail record,
• shipping record,
• A/R record of the payment.

Ten records, just to get this one order done. We must be complete, right? If the business closed today, we'd be 100% right.

However, we might be unfortunate enough to stay in business for another three years. That is when Mark is putting in a second order. As he does, he tells us that his business has moved (i.e they have a new customer address) to a different city, and the ship-to address is now the same as his new business address.

Our system has no time reference for customer data. Therefore the customer address — which has to be correct regardless of whether we are researching the new record or the order from three years ago — will screw up location-based reports whichever way I set it. Why? Because we don't store the shipping data and the billing data on the order, we store just the ID/link to the customer and ship-to <see figure 1>. The more our customers change offices, stores, and distribution centers, the more errors our database accumulates.

Fig. 1

We, hopefully, created a second ship-to record for the second order instead of allowing data entry to change the existing ship-to. That saves us from half the problem; we know where each order went. For the other half — the billing address — we have a shipping record. If we sent the billing address to UPS, we might be able to change our reports from the obvious choice (the customer record has the billing address) to the correct choice (the shipping record has it). That solves the rest of our problem.

We were saved because the computer has a memory of the order record that is accurate. We just weren't using it. Thinking about the completeness of the data makes us think differently about what the computer needs to remember.

Frighteningly enough, this isn't even a future-proofing issue. We didn't get hit with an unexpected question; this was one of the known ones: How many Fribbulators did we sell in a particular city?

You Complete Me

The completeness question which would have forced us to solve this example before it became a problem is: Can we protect our history data from future edits? Wow. That question is bigger than our fix. The complete answer would involve change control, too. It might involve software constraints to prevent certain data changes, such as editing the address on a ship-to.

Should I be allowed to edit the address? Yes, if there are no orders, invoices, or other transactions attached to it. No, if it is in use. Thinking about completeness, on something as (supposedly) dead-simple as an address, created a ripple through our entire design. Imagine the tsunami a full rethink might cause.

Not My Job

I know what you're thinking: Interesting, but I don't design databases or entirely new systems. I just patch the ones we have. This isn't about my job, right? Wait… what if I build a completeness list and use it to test my current system? Instead of using it for design, I can use it to predict where the boat is secretly leaking, or might leak soon. This article is for anyone who designs, codes, or maintains databases.

What sort of completeness questions should we ask to do real future-proofing? Here are a few skeleton questions to get you started. Always think about your specific industry, and the unique rules of your particular company, and build up from there.

• When is it okay to see/change/purge/resell/re-use ______ data?
  Example: Mailing lists
• Who has the authority to see/change/purge/resell/re-use ______ data?
  Example: Payment method data
• What data can be left unencrypted?
  Example: Marketing survey data (The answer might surprise you when you think about it.)
• Where do the needs of the business require us to relax data completeness rules? How do we keep partial data discreet from the complete data?
  Example: Intra-company sales vs. customer sales
• Why do we collect ______ data?
  Example: Archives of product descriptions

Willing to share your design questions? Want to tell us about processes you've had to re-imagine? We'd love to get a peek at how you think. Send your thoughts to editor@intl-spectrum.com . Thanks for the memories.

Featured:

Sep/Oct 2014