databaseLessons

I sent out a new article today. First one in months. I have the same 24 hours as everyone else, but …

No, I am not going to take the easy way out with an excuse.

Anyhow. Received 2 comments. One via the website and one via email. I appreciate both comments, even though they both were against me (at least a little!). The website comment was in regards to this posting about php and mysql. If you visit the posting you can read the comment.

My response? What can I say? I let my subscribers down. I will do my best to write another in the series, but not until after my daughter’s wedding. (hmmm, not good enough; only gives me 9 days grace!)

The email was about primary keys.

Hi Richard

Good to hear from you (been a while since you’ve uploaded a new article), and hopefully everything has been going well with you.

Just wanted to mention that your statement “Every table must have its own primary key” is a little misleading from our experience over the years. Lookup tables do not always need a primary key, whereby master tables should be since they’re the ones being ‘queried’ on a repetitive basis. The problem with Lookup tables just ordinarily being given a primary key is when the data is not meant to be ‘in order’. For example, a list of Job Titles in a single field table. Over the years a couple of clients have jumped on us for listing them alphabetically (naturally happens with a Primary Key on that field), when they should be listed in a hierarchical manner, e.g. CEO, CIO, President, 2nd VP, etc.

Just thought we’d add our two cents.

Keep up the good work,

My response?

I appreciate your comments.

Life always has exceptions, but I’ll stand by my preference this time.

Actually, if you read the fine print, I ALWAYS use autonumber fields for primary keys. I never want user generated data as a primary key. Some would consider me picky, but that’s okay, because your database is yours and ….

My need for a primary key is to provide for 1-to-many relationships, primarily. Now if you ponder that, why do I have them on the many sided table if it is a “dead end”? The answer to that comes via the style of unbound form that I use so often (which is another personal style some do not like, but, that’s okay cause …)

As to your example … in the hierarchical manner, e.g. CEO, CIO, President, 2nd VP, etc, what solution did you come up with, or have thought about, to allow simple sorting?

Dialogue is great, even when the other party disagrees, or tells you off!

Now I want to write more on both topics, and get some more dialogue going.

This is fun, even when it hurts!

I was reminded today why I avoid activeX components. Someone was having trouble installing a small Access database on to their computer. (I did NOT create the database) We quickly traced the problem to an older version of an OCX file on their computer. After 3 attempts to unregister/re-install/re-register the ocx file we finally got it working. Fortunately, this computer was walking distance from my desk. What if it had been in an office in another city?

What was the offending activeX control? A calendar. If you have not already done so, see my series about a calendar control that is pure Access (no activeX) starting at this web page.

Today I was working on some code that I have been asked to enhance. I came across the following bit of code.

if a = b then
   do something that does NOT affect a or b
   do something else that does NOT affect a or b
   if a = b then
      do a lot of stuff
   endif
endif

Why are there 2 identical if statements, with no code inbetween that affects the comparison test?

This reminds me of the article I wrote, called Working with Other Developers. Please spend at least a little bit of time on the “look-and-feel” of your VBA code. It makes it much easier for someone else (maybe even you) to read and understand your logic.

p.s. another problem with the system I am working on is that it has a lot of orphaned code. That is, code that is no longer attached to an object, and will never run. Some of this code causes the Access compiler to choke and sputter, and I depend on the compiler to help me find errors. Hence, I had to spend time getting rid of the orphaned stuff.