Database Architecture & RDBMS Best Practices

The most popular database is Excel. Knowledge workers use Excel spreadsheets as simple, non-relational databases more often than as an accounting worksheet. That's OK, because it works. The big boys (software developers) use relational databases, which is like tying together each worksheet in Excel; the tying together (joining) of database tables is the relational part.

In my computer science college classes I was taught that each row in a database requires a primary key. Just like each row on your Excel spreadsheet has a unique number: 1, 2, 3; each row in a relational database needs a way to find that row of data, quickly. In academia – in other words, in theory – I was frequently taught to use something like a person's social security number (SSN) as the primary key for a database table. But, in practice, that doesn't work nicely. In theory, there's no difference between theory and practice. In practice there is.

One of the key tenets of primary keys is that they must never change unless every single reference to that primary key (the relationship joined by a foreign key) is also updated which is not a trivial task

There are a few issues with using a person's SSN as a primary key. One issue is that it's a long number, much longer than a 1 – 5 digit integer typically used as a primary key, so it takes a database engine longer to compare numbers during a lookup.

More importantly, what happens if a person's SSN changes? SSNs can change in domestic violence cases, identity theft, when two people are mistakenly issued the same SSN, etc. You may think, since these are such rare cases, that it's not a big deal for a database; but software systems have to be developed to handle every possible case to work effectively. Good software should reflect a true reality, not a theoretical reality. So, to solve this issue, most databases issue a sequence of integers for each row. In the case where databases are in a cluster, each database might tag a digit on the end representing which database server issued a specific primary key. In other words, instead of 1, 2, 3; database server #4 in a cluster might issue 14, 24, 34, etc. as primary keys to avoid any primary key collisions.

Database Best Practices

Having done a bit of database work at Apple, I've learned some best practices that are worth sharing. A RDBS should start off in third normal form which means only primary/foreign keys are duplicated in separate tables – the rest of the data should be unique to each table. Later, when there's a performance issue, the database can be optimized, as needed, by denormalizing the data.

1. Primary keys: Never build intelligence into a primary key – a primary key is simply an artifact of the database and it should represent nothing more than a way to access a row in a database table (i.e., don't use SSN as a primary key). Just like a software developer doesn't care what GUID is assigned to an object in memory, neither should a database designer care much about the contents of a primary key. Creating a primary key that's a simple integer is highly efficient since a computer can quickly find and compare numbers (in the case of integers) much faster than a string of nine characters (in the case of SSNs).

I once consulted with a retried Army first sergeant at DTIC, around 1999, who made her RDBS primary keys positive and negative integers. The primary keys for active personnel were positive integers and the retired personnel primary keys were negative integers. While this sounds smart, it's actually too clever. This was pre-9/11 so it was rare for a retired person to come back on active duty. The reason this was a bad decision is that it can be dangerous to change primary keys, and then update all related tables' foreign keys.

2. Table Names: Database table names should be singular (Employee, Order, Transaction, Statistic, etc); they should be named for what each row in the table represents, not the entire collection. The reason is that, typically, there's a one-to-one mapping between a row in a database table and an object used in code. For example, in code, an instance variable referencing an Employee object should represent a single employee from the database while an instance variable that's plural, such as Employees, should represent a collection of objects such as an array or dictionary.

3. Lookup Tables: A lookup table is a simple static database table that's used to populate a list or collection. For example, a list of countries that your company ships to. Perhaps, your company only ships to the U.S. and Canada. Later, when you start shipping to more countries, how do you update the pop-up list of countries on your website or mobile app? With a lookup table, you simply add another row to the table with the new country that you ship to. Updating the database table is easier than changing your code, recompiling, and deploying. Typically, a look up table also has a column representing a sort order. This is done so the list can be displayed in a specific order with, say, the U.S. listed first, instead of Afghanistan, since most of your customers are located in America.

4. Compound Primary Keys: A database table should have a single primary key for a typical one-to-many relationship to another table (join). Sometimes, you need a many-to-many relationship. For example, a Person table/object joined to an Address. A person might have multiple addresses (homes), and an address might belong to multiple people. In these cases, where a many-to-many relationship is needed then a simple middle table is set up that contains only two columns which contain two primary keys propagated from the two joining tables. One of the primary keys in the middle table is the primary key of the Person and the other is the primary key of the Address. Technically speaking, the two primary keys of the middle table are propagated foreign keys. One of the middle table's primary keys is a foreign key from the Person table and the other is a foreign key from the Address table.

I'm not aware of a practical case where more than two primary keys are needed in a database table. In cases where I've seen three (or more) primary keys in a database table I realized that the database designer didn't have a good understanding of relational databases. What they typically needed was a single primary key and indices created for their other columns to optimize their lookup speeds.

5. Number vs Varchar: 

Do not use a numeric type for defining data fields which won't be used for calculations (i.e. "math"). In other words, credit card numbers, phone numbers, SSNs, etc., should be string types (i.e. varchars) in a database model. One specific problem I've encountered on a production system is when a developer stored the credit card security code (CSC) as a numeric data type. Although this credit card code is always numeric, it can contain an important leading zero. When I saw my CSC repeatedly failing at checkout on an e-commerce web site, I immediately knew the problem and confirmed it by reaching out to the DBA.

Military Misconceptions

I had two misconceptions about military retirement when I was a young buck, probably because I’m not a military retiree.

My first misconception was that my four years attending a federal Service Academy (Annapolis, West Point, Air Force Academy) would be applied to my retirement when I reached 20 years of active service. It turns out that’s not the case. However, my four years on active duty at Annapolis would apply to retirement from a civilian federal job.

My second misconception was that I could retire at 20 years. Although retiring at 20 years (or a lesser amount for medical reasons) is effectively how it works for nearly all veterans, there’s a small nuance that’s often overlooked. The military (especially the Navy and USMC) technically calls “retirement” a paid retainer for a period of time, which means that they can still call you back to active duty, involuntarily. What happens if you don’t return to active duty for the call up? Well… the federal government knows where you live since they’re sending you a “retirement” check so they can simply stop paying you.

As we used to joke at Annapolis, NAVY stands for Never Again Volunteer Yourself.

The Beauty of Binary

Boolean algebra was invented by George Boole in the mid-1800s, long before binary numbers had any practical purpose. While binary, which is base 2, is not the simplest numeral system for humans, it's ideal for computers. It's a simple way to store and transfer information. As a matter of fact, you can think of DNA as binary since it only has two combinations (CG or AT) that store all the genetic information of our makeup.

(The simplest and oldest numeral system for humans is unary, which is base 1. Think: tallying numbers with four ones, 1111, while the fifth tally is a diagonal line striking through the four tallies to make one group of five. Actually, traditional tallying seems more like a cross between base 1 and base 5, but I digress.)

There is an elegant simplicity in binary in that each digit is either a one or a zero. On or off. No room for any gray area, even though fractions and negative numbers can still be represented in binary. Additionally, some numbers that can't truly be expressed in one base, for example, 1/3 in base 10, can be simply written in, say, base 3 as 0.13.

Since computers use binary, some integers operations are child's play to a computer, especially bit shifting. As humans, we can't easily figure out multiplication of large numbers in our head. For example, what is 123 x 45? That will require a pencil and paper or calculator. But, we can easily figure out the answer to 12345 x 100, even though the latter deals with much larger numbers because we simply shift the digits three places. But, for humans, this calculation only works for powers of 10, since we think in base 10. Computers, however, get this luxury when they're multiplying by integers that are a multiple of the base. Multiplying a base 2 number by 2, 4, 8 is as simple as shifting the bits by one, two, three, or four places. For a computer, like a human, this is a much simpler task than working through the traditional arithmetic.

Simple Influences

Simplicity is the ultimate sophistication.
– Leonardo da Vinci

The Marines and Apple have been the biggest influences in my professional life... my way of thinking... my work philosophy.

At the end of the day, they both respect and seek simplicity. Simplicity of design in products. Simplicity of design in tactics. While both organizations are well respected leaders in their fields, they go about solving problems in different ways. One's procedural and the other's artistic. One creates and the other destroys.

But, there's nothing wrong with creating good and destroying bad.

Mental Health in America

How does someone end up on the streets? There are many different reasons such as abuse, drugs, loss of a job, and mental health issues. Over the past few months, I've watched the mental health of a  friend and former coworker from Apple, decline until he ended up living in his office, car, and then ended up on the streets. Help and love from family, friends, his church, and coworkers did no good since he wouldn't accept any aid.

Last time this happened to him, he ended up being arrested and committed. Unfortunately, until someone exhibits some harmful behavior – what authorities refer to as "fitting the profile" – there's not much that can be done by others. Sure, we tried talking to him, in good faith, but when one's brain can't accept reality, logic does no good. From his point of view, the entire world doesn't understand his genius and he things we're all squelching his creativity.

Today, a few of us, including his wife and mother, had to pack up his office, which he had been squatting at, for a couple of months, until his sympathetic landlord had no other choice. We wondered what street he was living on as we boxed up his computers, routers, books, marketing collateral, and training materials.

But, that's life in America. We're free until we harm.

Understanding Trump

Since the election, I've been trying to better understand President-Elect Donald Trump. What I heard as his promises on the campaign trail didn't make sense. It has taken me a little while to realize that much of his forward looking rhetoric seemed to actually be his opening bid in a negotiation rather than campaign promises. You're selling your home for $800,000; I show up and offer $750,000; that's not the end of the deal, only the beginning.

When Trump said, "Build a wall," he was actually pitching an idea. From his point of view, why not throw out ideas and see what sticks? At the end of the day, he got the results he wanted. He accomplished what no other candidate could; he was elected POTUS. People may be protesting the election, but no one is contesting it like the 2000 election. To bring up the point that Trump didn't win the popular vote is like rationalizing today's loss of your favorite baseball team in a close game, say 2 – 1, by arguing that your team won yesterday's game 10 – 1. You can't carry over yesterday's extra eight runs to today.

It's been said that the first person with a crazy idea isn't as crazy as the first follower of that idea.

Build a Wall

Something I couldn't understand, when Trump said he was going to build a wall, was why other people from Mexico and Central America would support him. How could they support that? Yesterday, I got an answer to my question from someone who employs an undocumented worker. It turns out that undocumented workers aren't seeking solidarity with other undocumented workers by supporting Trump. Rather, they're seeking to stop the influx of undocumented workers into the U.S. to limit their competition. Something I didn't understand before today now makes complete sense. This is a small epiphany, but I will continue to try to understand how people think while seeking objective truth.

Failing to understand your friends and enemies is failing to understand people.

Weak Stadium Security at NFL & College Venues

Only clear bags allowed.

Last night, I went to a basketball game at UCLA. Women were prevented from entering the arena with any opaque bag larger than a clutch. Women who showed up at security with a purse were sent outside the arena to leave their purse at check-in. Ladies had the option of transferring the contents of their bags into a clear plastic bag if they wanted to. It turns out this policy mimics the NFL's; the NFL says it has "unanimously" implemented the same poor security practices at their stadiums.

Here's the problem... the security metal detectors can't detect leather. The clear plastic bag policy is trying to mimic TSA security policies in form over substance. Why can't a woman bring an opaque bag into an arena? I don't know.

How can you defeat this security measure? Simply empty the contents of your purse into a clear plastic bag and then hide your empty leather purse anywhere on your body such as in the small of your back. After entering the venue, simply transfer the contents from the clear plastic bag back into your leather purse. This suggestion is a much safer option for women than leaving their purses with some college kid to guard. (Would it be far-fetched for a creepy college kid to go through your purse during a game?)

Security Theatre

What the NFL is accomplishing with this policy is known as security theater. Most people recognize that security is usually a trade off with convenience (although it doesn't necessarily have to be) so, if a policy is implemented at an institutional level that is highly inconvenient then it must be safer, goes the thinking. In other words, it's inconvenient security theater without making the venue safer – if anything, it puts added risk on their fans due to the hassle of standing in line in the rain (which is what happened last night) plus transferring stuff between bags, in the dark, while having a college kid watch your bag, etc.

Let's keep America scared. I think you see my point.

Protesting vs. Complaining

If every single one of your thoughts, posts, and comments are anti, anti-Clinton or anti-Trump, then you are not helping. You’re not even protesting. You’re complaining. You don’t like your lot in life, so you want to bring others down to your level by “informing” them through your biased view points.

You protest a cause to prevent or change it. You protest the war to end the war. You protest higher taxes to lower them. You protest evolution to promulgate the idea that Adam was made from dust and Eve was made from a rib; you do this to get your Creationist beliefs institutionalized.

For protesting to have an effect, it has to be organized as a group; it has to send a clear message that’s actionable. Venting really doesn’t help because it’s not as if you’ve discharged those negative feelings, you’ve simply amplified them.

I swore my life to protest your First Amendment. Never a regret there. But now it's my turn to exercise my freedom of speech; except I'm doing it in a positive way. Find a way to make it work or be miserable – that's your choice.

I'm not saying don't protest or demonstrate. By all means do that if you can make a difference.

An Entrepreneur's First Step

Q: What should be an entrepreneur's first step when creating a business, product, or service?

A: Write a press release (PR) and frequently asked questions (FAQ) document.

Think: Begin with the end in mind.

The PR and FAQ are notional and for internal use only. The PR focuses on your product's benefits and the FAQ answers specific questions regarding features and details. Later, when you're ready to ship your product, you'll publish the actual PR and FAQ for public consumption. In the mean time, the notional PR and FAQ are used to socialize your vision with the team. Sure, you'll tweak the document, slightly, while you're working on your baby, but by using this as a starting point... as your vision document... gets everyone on the same page and it keeps the founders and team from getting distracted.

The PR should be a simple one or two page document describing the benefits of your product and the FAQ can be a few pages. If, later, you find development straying from that notional PR, then you'll either need to update the PR or ignore the distractions.

Presidential Elections and the Press

In the 2000 Presidential Elections, the press reported results in real-time which, some say, may have effected the outcome since people in the western US, AL, and HI may have skipped voting, thinking it was a forgone conclusion.

During the next major Presidential Election, in 2008, it wasn’t a close race (365 [Obama] to 173 [McCain] electoral votes), but none of the major networks called the race until 11 PM ET (8 PM PT), at the exact minute when the polls closed everywhere but Alaska.

I wonder how they'll play it, tonight?

Freedom of the Press Means Capitalism 

Keep in mind that, while freedom of the press is critical, these news companies are for-profit businesses that need to make money. They make money by making news. By making more news, more people tune in. To make more people tune in there needs to be suspense and excitement. A close Presidential Election does exactly that.

Now take a look at the news cycle leading up to today's election. Have you ever noticed that the Presidential debates are hosted by journalists? On the surface, that makes sense since they should be able to interview people without bias. Of course, they do their best – in lines with their employer's desires – to be unbiased. But there are two points where this isn't the case. The first, and most obvious, is the fact we all have slants. I've done video and written journalism and I've seen how simple it is to have a story focus on what I'm most interested in. At best, it's unintentional bias, at worst, it's misleading (which we see, every single day, in politics). One can speak the truth with the intention to deceive.

Second, and this isn't obvious, is there are subtle cues in the news to make it more dramatic. A slow motion scene of a recently deceased Challenger crew; or a studio audience at a Presidential debate.  Between keeping your eye on the news crawler at the bottom of the screen, along with the transition sound effects and breaking news graphics, you are forced to pay attention.

Debate this Debate Idea

Would not the debates be more effective at informing citizens if there was no clapping, cheering, or booing? Of course, the audience is told by the moderators to refrain from making any noise, but that doesn't work. What also doesn't work is telling the candidates not to interrupt each other. The latter issue could be solved by either switching off the mic of the candidate who doesn't have the floor, or giving that candidate the option of overriding the switched off mic while incurring a time penalty.

Television media has a very good idea about how their reporting will affect their viewers. Repetitively showing dramatic events will keep people on edge which keeps them tuning in.

One way I've serendipitously discovered to avoid the news drama is simply by not watching live TV news. But, I am not disciplined enough to do this on my own; instead, years ago, I cut my cable service to nothing but Internet.