Database Design Insights

As I mentioned before I have been spending some time rethinking some of the designs I have done or have seen over my career. I was able to come up with a few insights.

First, not enough companies keep history of who changed records, when, and what the changes were. Keeping this sort of information is critical to reconstructing why a decision was made. You need to be able to tell the state of the database at a given date and time to know why a decision was made the way it was. Another bit of information that is needed for sensitive data is who viewed the data and when. None of the companies I have worked for have ever kept a log of select statements.

Another insight was that most tables fall into basic categories. I usually hate putting things in boxes but had to create these as a way to explain table design to developers. Reference tables contain information that is fairly basic such as units of measure or model years. These tables are rarely updated but often read. Cross reference tables are used to translate one piece of information into another (employee 276 in the old HR system is employee 889 in the new HR system). These tables are again rarely updated and are less commonly read. Master tables contain information about customers, parts, suppliers, etc. These tables are updated fairly often and read fairly often. Transaction tables contain information about invoices, orders, and other event driven processes. These tables are added to constantly and often read.

I spent a lot of time thinking about dates. Dates are often represented with times attached, which is fine if you know precisely when something happened. The aren't as convenient when you want to record a birth that happened in the spring of 1847 or an political movement that occured in the later half of the 1500's. I have found a few ways to represent non-exact dates but they are alien to the database systems we have and therefore not a clean solution. I need to do some more work on this.

While thinking about imprecise dates I considered imprecise data. Suppose you have multiple reports of when an event happened or multiple descriptions of a bank robber? Every system I have dealt with expects one precise record, but that isn't how the real world works. I was able to come up with a system that allows for multiple versions of an event or fact, including a reliability factor. But I need to work on this more and haven't decided how multiple versions of a fact can be accurately reflected on a report.

So, nothing earth-shattering. No cures for cancer. And perhaps I am the only one who cares about such things. Still, here are my thoughts.

Life and stuff

I haven't blogged in quite a while. It has been a rough summer.

I joined a Tai Chi class which I really liked, but had to drop out because of pain. My legs just can't handle the bent knee posture required. Perhaps in another 6 months or a year I will try again, after I have built up my knee and leg strength.

Work has been rough, fortunately I have been able to stay employed although we did have to do a few weeks of furlough. I am hopeful that the worst is over and I can continue to stay employed, but we had to lose a lot of good people and the workload is going to be tough once they start producing cars again.

The only groundbreaking thing I have achieved all summer was in a dream. For the first time in 50 years I saw the sky in a dream. Up until now all of my dreams have been indoors or in heavily wooded areas, but once this summer I dreamed of being outside and actually looked up and saw the sky. Not an earthshaking accomplishment, but it has cleared up an odd omission that has been going on for 50 years.

I have accomplished a few things this summer. Minor things like cleaning up the garage and getting some house repairs done. But even minor things are better than nothing.

I have also done some cool things at work, but can't discuss them due to confidentiality issues. The work led in some interesting directions and gave me some new insights into database design. I have some hopes that I can write about some of the insights in the future.