Data Entropy, Part 4 -- Oil and water don't mix, and neither do data types

Have you ever tried to find the sum of "A", 23 and an artichoke?  Would you be able to find it's average? Max? Min?  Ask a database program and it's likely to return an answer.  But is it the answer that you would expect?  Many newbie coders (and even a few experienced coders) default every attribute of their data structures to varchar and float.  The problem with this approach is when it comes time to put data together you have to find the data dictionary in order to avoid making mistakes that will corrupt the output.  Even more egregious is the need to add code to format or transform data in order to make sure that errors won't occur.  The Data Entropy in these systems is incredibly high.  A lot of work must be put in to get useful information out.

I actually saw this table definition once:

CREATE TABLE personnel
     ( id         INT           PRIMARY KEY
     , pers_col1  VARCHAR(100)
     , pers_col2  VARCHAR(25)
     );

This is simplified from the actual implementation (there were more columns), but you get the gist. I'm convinced that the developer simply used the default wizard and increased the column size when it didn't work.

Granted, a detailed data dictionary that documents every column, it's usage and definition can be tremendously valuable.  However, I propose that you start by defining a set of functional attribute types, map these to a default set of database column types and use the names of these attribute types (or a standard abbreviation) as the suffix of the type.

Here are the ones I always use with the default MySQL Column definition:

Attribute Type	Abbreviation	Definition	MySQL Column Type
identifier	id	An abstract value that uniquely identifies an entity.	int(11)
globally unique identifier	gd	Special case of an Identifier, where the value is a 32 character hexadecimal value designed to be unique in every context.	char(32)
name	nm	The name ascribed to an entity which is normally used to identify it.	varchar(100)
number	nb	A human readable identifying value.  (e.g., Part Numbers or Social Security Number)	varchar(50)
code	cd	A short value identifying a classification or category.  These are human readable and should be avoided unless the implementation requires coding according to a standards guide.  For example, "CAT5" or "CAT6" are codes.	varchar(25)
date	da	Contains only the calendar date	date
time	tm	Contains only the time of day	datetime
quantity	qt	A numeric measure of something which uses some unit of merit, such as pounds, meters, or liters.	float
count	ct	The number of things in the identified set.	int
amount	am	A financial quantity, expressed in some defined currency.	decimal(30,15)
description	ds	A long text which is used to describe the entity to the user	varchar(500)
sequence	sq	A value used to order items within a group	int
timestamp	ts	An audit value indicating the system time at which something occurred.	datetime
flag	fg	A boolean value.  This is used when an entity is or isn't.  I prefer the term flag, over "boolean" because that's what these columns do, they "flag" something about the entity.	boolean

By having a clearly defined function based set of attribute types, you've set a foundation for building a system that is as self-documenting as is possible. From this point, it's a matter of consistent usage and maintaining the definitions over time.

On a slightly different note:
When naming columns also beware of homonyms and synonyms.  They can bite you in the behind if you're not careful to avoid them whenever possible and document every time you can't avoid it.

MySQL for Excel Append Error (and a solution)

I use the MySQL for Excel add-in in order to move data between my MySQL database and an Excel workbook I depend on.  Recently, I encountered this error when I tried to append data to a table I had successfully used.  This is my experience and the solution I found.



The source data had nearly 2,000 rows and over 50 columns being appended.  Initially, I created the table using the MySQL for Excel Add-in, then tested it by performing a second append.  I moved to a different set of data and found that about half the columns did not exist in the second data set.  Since much of it was duplication or data validation, I decided to shorten the table by removing the unused columns.

Once the columns were removed, the error above appeared.  I dropped and recreated the table, stopped and started Excel, logged out of the connection and back in, but nothing corrected the problem.  I found the same problem asked on Stackoverflow, but there was no answer.  I couldn't find a reference to the problem anywhere else.

The next day, it occurred to me that the mapping stored within the add-in may be failing because it couldn't find the columns I had removed from the table and didn't know how to adapt.  Sure enough, I restored the dropped columns and the append worked exactly as expected.

Introduction

I have been writing code of one variety or another for the past 30+ years.  Ninety percent of my knowledge is self taught, often because I had a problem that had to be solved.

I would consider myself an expert when it comes to SQL based languages.  I started playing with DBase 3 in college and have progressed through MS Access and Excel to Oracle, SQL Server and MySQL.  There isn't much I can't make a relational database do and I know where the tricks and traps are in all of the products I listed.

My latest project involves creating a desktop task management application using Java and MySQL.  Other systems exist, but I have particular tastes and had been using a custom built MS Access application for several years.  But being tied to a licensed product has it's drawbacks, so I decided to start on an open-source based replacement.  Enter MySQL and the Netbeans IDE.

Since my skills are heavy on the database side, the data model is complete and robust.  Unfortunately, the Java side is progressing much more slowly.

Since nearly all my free coding time is spent on this application, the focus of most of my posts will be on the problems and solutions I've found.

I hope you check back often and comment.  I could certainly use the help.

One final note.  One of my favorite projects and my first professional "database" application was built using the mail merge functionality in WordPerfect 5.1.  I used it to manage the trouble tickets that my division handled.  The entire thing was stored on a 3.5HD floppy.  Although it was crude, it saved me time and the reports I created impressed my bosses at the time.