Column Names in Data Warehouse

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 12 Mar 2008 14:49 GMT

Hi all,

I'm working on my first data warehouse and I'm not sure how I should
name the columns in the database.

The first phase of the data warehouse is to store a bunch of data from
one third party source. The source contains over 100 pieces of data
and the business user doesn't even know what some of the fields are
but he wants to store everything. The third party refers to the each
field with a somewhat cryptic short name and a longer description.
The short name isn't always cryptic.

My question is am I better off naming my columns the same as the
source system's short name so that I can easily debug problems later?
Should I instead try to shorten their definition into something
meaningful? On a side note, I'm 100% positive that we'll never
populate the tables in questions with data from an additional source.

Thanks!

Reply to this Message

Knowledgy - 14 Mar 2008 03:06 GMT

This is just my opinion (before everybody flames me) but I wouldn't use the
short names. In a data warehouse and as a general practice, it is better to
be descriptive so that people in the future will understand the design of
the system. If possible for your system you might want to try to mimic the
names of the fields that are in the report application. This way DBA and
techies will understand them and also regular business users, if there's
such a need

Signature

Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/

Atlanta's Business Intelligence and Data Warehouse Experts

> Hi all,
>
[quoted text clipped - 15 lines]
>
> Thanks!

Reply to this Message

Mike C# - 16 Mar 2008 03:52 GMT

> Hi all,
>
> I'm working on my first data warehouse and I'm not sure how I should
> name the columns in the database.

<snip/>

> My question is am I better off naming my columns the same as the
> source system's short name so that I can easily debug problems later?
> Should I instead try to shorten their definition into something
> meaningful? On a side note, I'm 100% positive that we'll never
> populate the tables in questions with data from an additional source.

I concur with John K. Use descriptive names, especially when looking down
the road if you're planning to build data marts from your data warehouse. A
lot of client applications expose the column names to business users, so
it's a good idea to keep the column names in the data marts descriptive so
that business users will not be confused. By the same token, since many
client tools expose the column names to users, try not to make the names too
long. I've run into problems with nondescriptive column names and too-long
column names with tools like ProClarity on the front end. From a technical
perspective it makes administration and troubleshooting easier if your
column names are somewhat human-readable.

Reply to this Message