Tony's Microsoft Access Blog : Database Design

Building Scalable Databases: Perspectives on the War on Soft Deletes

Tony — Tue, 24 Nov 2009 06:01:54 GMT

This blog posting titled Building Scalable Databases: Perspectives on the War on Soft Deletes is interesting reading describing what to do with deleted data. The author comes to the correct conclusion, because he agrees with my viewpoint <smile>, that you model the business situation and mark records as inactive, canceled, etc. You seldom need or want to truly delete data.

I used to have an inactive flag on the equipment in the Granite Fleet Manager. That worked for quite a while until I had someone tell me that they have reserve equipment that is only used when the California wild fires are rampant. These fire engines are paid for by the state of California and stashed at various fire halls in strategic locations. They are all identical. Thus fire crews from outside the affected crews have already trained on that equipment and know how to use it. So now I have a status combo box which includes Reserve and Out of Service. The Out of Service is meant for the situation where a given unit will be unavailable for a period of time longer than a few days. For example maybe the boiler needs an inspection or the mechanic is waiting for parts to come from overseas.

P.S. I almost always add a general purpose comment memo field to strategic tables such as the equipment and the service order field. And you can search in those fields. Because no matter how many explicit fields you create the users can always think up a few more but don’t have any place to put the random data.

School sends letter to parents of dead teenager complaining about her poor attendance

Tony — Thu, 26 Mar 2009 04:04:09 GMT

School sends letter to parents of dead teenager complaining about her poor attendance

The relevant paragraph is:

He said: "Unknown to the school, Megan's details had remained in a different part of the computer system and were called up when the school did a mail merge letter to the parents of all Year 11 students. The letter called up details of each student's attendance for the whole year to date and because Megan had been on roll in September, she was included.

As heartbreaking as this story is I can see how easily this could happen. Furthermore if a power user created their own query and did their own mail merge that makes this problem even easier to happen.

Mind you that bit about details in a different part of the computer system sounds like bad data normalizing.

I frequently have an Inactive Yes/No field on my tables as appropriate. And frequently it's somewhere near the end of the list of fields so it's easy to miss for someone not thinking about such.

In the Granite Fleet Manager on the equipment table I went a step further. I have a status field linked to a status table. This handles the situation where equipment is temporarily out of service due to lengthy maintenance/rebuilds, loaned out to other organizations or whatever the client desires. That status table has a Yes/No Inactive flag.

"Slacker DBs" Vs. Old-Guard DBs

Tony — Tue, 24 Mar 2009 20:06:52 GMT

A very interesting summary at Slashdot - "Slacker DBs" Vs. Old-Guard DBs on an InfoWorld article Test Center: Slacker databases break all the old rules

I saw this problem from a different perspective in 2000 or so when I purchased my Palm Vx. I was taken aback to only see room for exactly five phone numbers and/or email addresses. But when I thought about it I realized that was a reasonable limitation given that the concept of Joins almost certainly isn't built into the Palm OS and program. I quite liked the Palm desktop interface and so that's where I entered much of my information.

A while later my sister purchased a Palm but was using the contact manager built into Outlook. She occasionally had more than the five contact means in Outlook and was rather ticked that the sixth or seventh wouldn't import.

But getting back to cloud computing.

Microsoft is joining the cloud as well with SQL Server under the name Microsoft SQL Data Services as part of the Azure Services Platform. This is a good thing as I really, really like relational data systems. With all the other utilities and safeguards available when MS comes at this problem from the SQL Server perspective.

The perspective that folks with no relational database experience bring rather concerns me.

An example of this is MS bringing in Dave Cutler to lead the Windows NT 3.1 team with their multi user, multi tasking experience. The DOS based folks at MS would've stumbled badly and, in my not so humble opinion, could've done a better job in the Windows 95, 98 and ME family.

For example, formatting a floppy in Windows 95/98 or ME took over the entire system. You couldn't do anything else. And yet within OS/2, a true multi tasking system, you didn't notice a floppy being formatted and the task manager showed it only took a few percentages of the CPU.

That all said I can understand why Facebook or other huge non critical sites with hundreds of millions of records would deliberately denormalize their data. For example denormalizing the contact info or website's would drastically reduce CPU and disk access times thus improving response times.

On the fourth hand all my apps are going to be properly normalized!

The Manga Guide to Databases

Tony — Fri, 10 Oct 2008 05:32:26 GMT

"Want to learn about databases without the tedium? With its unique combination of Japanese-style comics and serious educational content, The Manga Guide to Databases is just the book for you.

Princess Ruruna is stressed out. With the king and queen away, she has to manage the Kingdom of Kod's humongous fruit-selling empire. Overseas departments, scads of inventory, conflicting prices, and so many customers! It's all such a confusing mess. But a mysterious book and a helpful fairy promise to solve her organizational problems—with the practical magic of databases. "

More at The Manga Guide to Databases (That's a link to a page at Amazon but I did not put in any associate code of any sort.)

<rhetorical comment>But will it teach Access subforms?</rhetorical comment>

No indexes on a 2.2 million record table?

Tony — Tue, 08 Jul 2008 23:46:22 GMT

Hastening an Inevitable Now this is truly ridiculous and very worth of a WTF page.

This page also illustrates how CPU speed isn't that important. 150 Mhz systems. Hard drive speed is where you should be spending the money on a server. RAM is also important but relatively inexpensive. Of course I'm over simplifying as there are many other factors such as network interface, switches and so forth.

I had a 10,000 record table of names and addresses in Access 2.0 on a 486. To lookup a name with no index took 25 seconds. Once I added the index it was so fast I couldn't really time it.

Duplicate names and birth dates

Tony — Sun, 04 May 2008 05:13:33 GMT

Ken Sheridan, long time resident of the Microsoft Access newsgroups, posted a very interesting paragraph in a thread title Duplicates

A year or so ago I witnessed a remarkable coincidence when attending a hospital clinic. Two patients, both female, both with the same date of birth and both with the same names were attending. I happened to overhear the staff talking about it and it appeared that the 'key' used for identifying patients was a combination of name, gender and date of birth, so the staff were having difficulty distinguishing the medical notes of one from the other. One wonders what might have happened if they hadn't spotted the problem!

My brother, who shares my last name Toews, has a very troublesome time whenever stopped by the police, which seldom happens, or whenever he crosses the border into the U.S.A. Turns out he has the same first, middle and last name and birth date with a Canadian who has a lengthy criminal record. My brother now adds an hour to his estimated border crossing times. Although his last crossing they must've had a photo of the bad guy. He was being interrogated in a side room when another US Border guy came in and told them that it was someone else.

Of course then there's George Foreman's five boys named George.

The point to my posting being that you can't use name and birth date as a unique key. This also goes for corporation names. There could conceivably be duplicate names in provinces and states as these are usually provincially/state incorporated. Also if you are dealing with different branches of the same company you will likely want to include city and province in your inquiry screens.