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.

Data Entropy, Part 3 -- Data isn't formatted.

If you use MS Outlook, then you are probably familiar with the way in which Outlook stores phone numbers; that is as separate Country Code, Area Code, Number and Extension.  If you make sure you store these values separately, then Outlook will display the numbers in a standard format.  However, there is nothing in the system that will prevent you from storing the numbers in a non-standard format.  Outlook simply throws the entire thing into the "Number" field and displays it as you entered.  What this illustrates is the fact that formats are and should be kept separate from the data which they display.

When data gets shared, the format in which it is stored can cause confusion or even errors.  Take for example a field that stores the amount of available storage used by files on your harddrive.  Given the wide variance in file sizes, we have grown accustomed to seeing the units appended to the end, like 45GB or 3.5TB.  But if you stored those values "45GB" and "3.5TB" in your database, how would you start adding them up?  How would you be able to total the amount of storage used by .gif files versus the amount used by .mp3s?  The logic required becomes mind numbing and maintaining that code requires considering all of the ways in which a user may enter the value.  The right solution is to decide on a unit of measure for the storage metric, (Personally, I like bytes in this case) and stick to it.  Then you can modify your display algorithms to provide units.  Additionally, you have a standard value by which you can transfer the data to any system without worrying about how the information is formatted.

On a slightly different note:
A group of colleagues and I started reminiscing about backup media and what it took to store all those floppies/CDs/DVDs "back in the day".  So, I decided to take a look back and see what it would take to store 1 terabyte of information using some common media from our past.  Here are the results.

Media Type	Capacity	# to store 1TB
5-1/4 In floppy disk	360 KB	2,982,617
3.5 in HD floppy disk	1.44 MB	728,178
8 in floppy disk	6.2 MB	169,126
SuperDisk - LS120	120 MB	8,739
SuperDisk - LS240	240 MB	4,370
CD-ROM	650 MB	1,614
Zip Disk	750 MB	1,399
Jaz Disk	1 GB	1,024
DVD (2-Layer)	9.4 GB	109

Data Entropy, Part 2 -- Design early and often

"Just build it.  If there are problems, we'll fix it later."

I can't tell  you the number of times I've heard project managers tell me that.  Nobody seems to want to spend time on design. And those who are willing to spend time on application design don't seem to think that designing the data structure is important.  "We're going to handle that in the application." is often the refrain.  If some application coders had their way, the database would consist of a table for each screen, with nothing but variable character columns named col_1, col_2 and so on.

The truth is that data matters.  So why wouldn't you spend time planning how you will store it, manage changes to the structure and account for future growth, not just in size but in structure as well.

Design Early

I start every design with the same, standard, normalized, logical schema.  It consists of 7 top level entity types ("Activity", "Agreement", "Asset", "Data", "Event", "Location", and "Party") and relationships for each intersection of two entities, for example, "Activity_Location".  I have yet to encounter an entity I could not model as a subtype of one of these 7 top level entities or a relationship between them.  This allows for very flexible designs, while maintaining a simple set of data definitions. Additionally, it simplifies the process of extending the data structures down stream, since there is already a general case for any conceivable data element.

Of course, the logical model has to be denormalized to implement an efficient physical database.  Subtyping from the standard schema above, I find the most common denormalization is moving referential columns into one of the associated entity tables for 1:n or 1:1 relationships.  In the normalized schema above, even these relationships are modeled as subtypes of the intersection tables.

... and Often

A common mistake made at this point is to abandon the logical model and focus entirely on the physical database.  The problem is that by doing this you lose all the advantages of having started with a standard model.  More changes will lead to greater variance from the original design and increases inthe real and potential data entropy of the system.

The way this should be handled, is to revisit the logical model for every proposed change to the database.  In this way, the designer can identify the correct entity which needs to be changed, extended, or subtyped and how it is mapped into the physical database.

A trivial example of this exists in my Bird Dog application.  One of the things I am tracking is my relationship to the people in the application.  Logically, this is a relationship between User (a subtype of Asset) and Person (subtype of Party) and would normally be a many to many relationship.  However, I am currently the only user so, I took a shortcut and implemented the reference as a column in the Party table.  I don't even have a User entity.  Eventually, I will need to implement the actual m:n relationship so that the system can support multiple users.  If the developer failed to consult the logical model design, it's likely that the existing "party_current_user_relationship_type_name" be completely ignored.  New tables for User and the intersection between User and Party created.  Initially, the data in the old column will be duplicated in the new table and as time passes the data becomes stale and conflicts with the new intersection table.  If the developers had consulted the logical model, they would have known to use the data in the column to populate the intersection table and to remove the column.  Doing this would have avoided both of the data entropy problems.

On a slightly different note:
I was going to create a SQL script to implement the top level entities and add it to the Sample Code page.  But the fact is until they are subtyped, the attributes are extremely limited.  Instead, here are the definitions I use.
Activity -- Anything that takes place over a period of time.  Examples include tasks and meetings
Agreement -- An accord among two or more Parties.  Examples include contracts and orders.
Asset -- Anything that has value or may be owned.  Examples include bank accounts, equipment, labor categories, user accounts
Datum -- Information that is independent of an entity.  Examples include documents, media files, and category and classification definitions.
Event -- Anything that occurs at a point in time.  Examples include financial transactions, state transitions and status changes.
Location -- Where something may be found, sent or received.  Examples include coordinates, property addresses, geopolitical entities, URLs and file system locations.
Party -- Anything that can take action, enter into an agreement or possess assets.  Examples include people and companies.