Skip to main content

What’s in a name?

I’ve worked on database projects for over 20 years. I go back to FoxBASE and dBase. I’ve also been through a number of naming conventions in that time. I’ve developed a personal preference but can work with other preferences because I can usually figure them out fairly quickly.

Here’s two rules I use as part of my database designs:

  1. Use a surrogate or artificial key as the primary key to avoid the need for cascading deletes or updates.
  2. Use the natural name for each element so that elements are easily identifiable when in discussions with the user.

I can’t tell you how much time I have saved over the years with rule 2. Users have no idea of your database design (and they shouldn’t!) but they know exactly which piece of information they want. It’s a whole lot easier if they can write “client’s birth date” in a report request and in the Clients table there is a field called BirthDate.

My problem is lately, I’ve noticed that a lot of people seem to be using a convention that drives me nuts - the use of ElementNameID as the primary key. This is the standard in a lot of the Microsoft examples.

What's the big deal? In one of our systems we have a table named Clients that holds…wait for it..a client. That client has an ID that is assigned from a legacy mainframe system and of course it’s called ClientID. This element has become a part of the users culture and is used to identify clients and it’s natural name is Client ID in user discussions.

“So just make it your primary key and you’re fine.” Not so fast. Remember, this is coming from a legacy system, in this case a 25 year old system. In those days disk space was expensive and you squeezed everything you could into a field. Specifically, the first 6 positions are the family group and the last two differentiate between individuals.

My problem here is that since the value has internal meaning, someone, somewhere will want to change this value. For this reason, I always use artificial or surrogate keys (see rule 1). No worries about cascading updates or orphaned rows.

Ok, so now my clients will have two values named ClientID. One the actual key and the other a legacy key. I could use an underscore for one, i.e. Client_ID. I could simply rename the legacy value ClientIDLegacy. Either one means that everyone who looks at my data will have to figure out what I did.

Worse, the natural name and the database name are different, violating rule 2. I could use Client_ID for the key instead but that means I need to change all of my other tables to match or live with the inconsistency (meaning remember to use the underscore on this one table).

My solution? It’s very simple, ClientKey. The element we are describing is a key and not an ID. To me, ID (or identifier) is something to be consumed by humans. Keys are for systems. I really can’t remember something being called “key” in a non-IT environment.

It may seem trivial, but I can think of half a dozen cases of the top of my head where you’ll run into this. It may not get you on this system but how about the next one? Now you have two systems with two different conventions. Now you need to remember what you did in one and not in the other. After 20 years, I’m limiting what I need to remember only to those things I can’t control <g>!


Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)

Get Asp.Net Profile properties from Sql

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository.

First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString.

Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.

You might be thinking …