Earlier I was listening to the CFHour Podcast #124 (by the way, this is a great show for anyone wanting to keep up on the ColdFusion community). This particular episode discussed (among other things) databases and naming conventions for columns. The main point that was brought up was prefixing columns with the table name, and when developers should/shouldn't do this. The example given (which is probably the most common scenario) was the primary key "ID" column -- i.e. naming the column "TableNameID", rather than just "ID". The discussion of "why/when to do this?" went back and forth, and it pretty much boiled down to a few things:

1. Personal preference on naming columns -- some people use "ID", some use "TableNameID".
2. It adds to the readability of the code. But WHY? In a complex query, you're probably aliasing the tables anyway, so you'd end up with "TableAlias.ID", which is just as readable (assuming you use an appropriate name for your table alias).
3. It's redundant. If I'm in the Users table, I know that "ID" is referring to the ID of a User.
4. But what about, say, in a CFLoop? If the code contains "#ID#", it's not clear which table ID is being looped over (which is true...but really the problem in this case is, the developer was lazy and didn't scope the variable -- "queryName.ID" being much more readable than "ID").

Personally (unless there is some other shop standard to which I must adhere), I use "TableNamePK" for my primary keys, and "TableNameFK" for my foreign keys. Here's why:

I've run into several projects where (because of business requirements), I'll have tables that look something like so:

tblManufacturing --
ManufacturingID
Name
VendorID
PartnerID
OrderModuleID

Let's say that VendorID, PartnerID are NOT foreign keys to other tables (but OrderModuleID is), they are just columns that meet a business requirement (maybe this is data that's entered manually by an employee). Maybe "VendorID" is an industry standard term for that piece of data, predating our application by years or even decades.

If I rename "VendorID" and "ParnterID" to avoid this confusion (which is only confusing to me, the developer, as the business units will never see the primary keys), it would have slowed down communication among the team members ("when you say VendorID, you actually mean the column I've renamed to VendorAccessCode", etc). So we can't really rename these "ID" columns that are based on business rules, but since the business folks don't really care about the primary key column, we can use a different naming convention for that, without slowing down anyone.

Looking at it from the other side, which "ID columns" represent foreign keys to other tables? Which columns should have constraints applied to them? By just looking at this table, I have no way of answering that. This could slow down development, causing developers and DBAs to go looking for foreign key relationships that don't exist.

But what if the table were named like so:

tblManufacturing --
ManufacturingPK
Name
VendorID
PartnerID
OrderModuleFK

Now, by just looking, which column is the Primary Key? And which ones are Foreign Keys to other tables? And which ones store some sort of 'ID' that is part of a business rule, but isn't actually a foreign key? By using "PK" and "FK", it's much more clear which types of data are stored in the columns, and I've typed the exact same number of characters...so nobody is allowed to complain about this being too much typing. ;)

Whichever convention you pick, you should stick to it consistently throughout the application (unless you have a really good reason for breaking it). I've had to write complex recursive algorithms before that traversed down a hierarchial database of roughly 500 tables, and needed to be able to "predict" the name of the next primary key in the tree. Because I used the same naming convention for every table in the database, the algorithm worked throughout the entire system. If there was no rhyme or reason to how things were named, I would have had to provide some sort of "map", listing each table and its primary key column (yet another thing to maintain, as well as a possible point of failure in the application). Additionally, I was able to write regex patterns to find foreign keys, by searching for any column name ending with "FK", which also wouldn't have worked if I'd stuck with the "ID" convention for everything.

-nolan

I was very pleased to receive an email last week stating that my presentation got approved, and now it's official...I'm speaking at this year's cf.Objective conference in Minneapolis!

I'll be giving my talk on "Intro to Model View Controllers Without A Framework" that I gave at NCDevCon in September (as well as a few local user groups). If you're still not quite sure what to make of CFComoponents, or what all the fuss is about with MVC frameworks like Model-Glue or ColdBox, then this is the place to be. This will be an updated version of my talk, including more info on "Service Layers", and probably some additional sample code too.

Two years ago was my first trip to cf.Objective (I couldn't make it last year); it was a great experience. I'm hoping to meet more people in the CF community, and am especially looking forward to the Arm Wrestling BoF Session. :)

See you there!
-Nolan

I'm in need of a "backup developer" to help with overflow work and to split some larger projects. Most of the work can be done from home, however I'd prefer you be on-site at my office in East Sacramento at least 1 day a week (this may go up/down with the complexity of each project, may become a non-issue over time). On average I can keep you busy 15-20 hours a week. Ideally I'm looking for someone with regular amounts of free time, that likes working on a variety of projects, and has the know-how to help solve problems.

Requirements are like so:

• Exceptional communication skills -- This is not boilerplate text; if you're prone to sending 3-word emails (i.e. "i don't know"), then this is not the job for you. I need to know when you're stuck, what you've tried, how I can help, what information you need from the client, etc. You will also need to provide regular updates on the progress: what's done, which roadblocks do you need me to address, etc.
• Be reachable -- To compensate for doing a majority of the work remotely, you need to be reachable semi-regularly. You'll need an email address, cell phone, and ideally an IM client of some sort that you check often.
• Experience working in deadline-driven environments.
• Exceptional attention to detail.
• Ability to "figure it out" -- We get lots of new requests, lots of new technologies need to be researched, solution ideas explored, etc.
• Ability to "hit the ground running" -- Figure out what you need to solve problems, and generally be as productive as possible while working on the projects.
• Ability to see "the big picture" within projects -- If you're changing an item on the back-end, how does it affect things on the front end? Should all of that be tested before the code is handed off to the client?
• Ability to multi-task and be self-sufficient -- An average day here consists of writing code for 1 or 2 medium-to-large sized apps, fixing bugs / maintenance issues in older sites, maybe going into IIS and blocking an IP address for a spammer, and doing R&D on which jQuery plug-in will be most effective in an upcoming feature. The ability to effectively flip between such things is critical.
• 3 to 5 years of ColdFusion development on a currently supported platform (i.e. CF 8 or 9), writing both procedural and object-oriented code (or a similar back-end language, with a go-getter attitude and willingness to learn ColdFusion quickly). Ideally you've used at least one of the current CF frameworks on a "real world" project, and can learn similar frameworks without too much hand-holding. Experience w/ Model-Glue or Fusebox would be preferred.
• You must have your own development machine (preferably a laptop) running ColdFusion 8 or 9, Microsoft SQL Server, MySQL, a Subversion client of some kind, and a Remote Desktop client.
• Extra points if you know PHP, .NET, Flex (especially w/ the Mate Framework), and/or have written Facebook apps before.
• Double-extra points if you've written Adobe AIR apps, or done any mobile development on a current platform (PhoneGap, iPhone, Android, Blackberry, or something similar).
• Experience with all the current practices for building public facing websites -- This would include: writing table-less XHTML/CSS sites with 0 validation errors, writing JavaScript functions, using jQuery, using FireBug, etc. You understand how to test sites in a variety of browsers, you know the current practices for how to support old versions of Internet Explorer, and have dabbled in newer things like HTML5 and CSS3.
• Ability to thoroughly test your own code -- Obscure business rules are one thing, and definitely require help from the clients. Making sure a form passes basic validation, or that the page matches the approved design, etc should all be no-brainers, and need to be done before marking the tasks as "completed".

If this sounds like something you'd be interested in, or you'd like more information, send me an email at "nolan at southofshasta dot com".

Thanks. Nolan

I made it back from CFinNC, late Sunday night, extremely jet lagged, but in one piece, and very happy I made the decision to go. First, I want to thank Dan Wilson, and the rest of the folks that helped put this conference together. Not being able to attend MAX this year, I was very happy there was another ColdFusion-related conference, so soon after MAX. The fact that it was over a weekend made it much easier to schedule. As you may have read on CFCDev, Dan and I exchanged several posts about information on the conference website, what was/wasn't up to date, etc. Eventually, Dan and I took the conversation off-list (and off-email, talking more in person at the conference), and what became very clear to me is how genuinely personal he takes his work in the CF community; putting together this conference was no exception, and I think the quality of the CFinNC conference is proof of that. Everything seemed to run very smoothly -- registration was quick and painless, parking was a snap, wifi was up and accessible throughout the venue, the staff were all very courteous and happy to help. Nothing about the conference seemed out of place, or an obvious indicator that this was a newer project -- I was quite happy with how well everything was organized.

Session highlights for me included:

Mike Brunt -- 10 Steps To A High Performing ColdFusion Application, Clustering ColdFusion Great talk on performance testing, and all kinds of related items. Mike came with a real world demo that required not one, not two, but THREE laptops to get everything running (so it looked like it does in the real world) -- talk about putting a serious amount of effort into your lecture! My only regret is meeting Mike just minutes before I had to leave for my flight home; I was unable to say much more than "nice presentation" before I had to go. He was wearing a Beatles / Sgt Pepper shirt; I suspect we would have gotten along quite well. :)

Jason Dean -- Intro to Securing CFML Applications I met Jason at cf.Objective(), and caught his other talk on security. This one is the precursor to said cf.Objective() talk. Both are worth checking out; not too much overlap, you'll learn something new at each one. And you can play the drinking game "take 2 sips every time Jason says 'validation'". :)

Brian Kotek -- Intro to the Swiz Framework Nice first look at Swiz, with some notes on how it compares/contrasts to Caringorm, what Swiz is/isn't, how it can help w/ event handling, auto-wiring and tons more. For anyone new to Swiz, this was a great introduction.

Andy Matthews -- jQuery & AIR: Desktop Development for the Front End Designer While in NC, I was staying with my friend Rob who is typically known as a .NET developer. Half way through this presentation, I was tempted to call Rob and have him come watch -- I'm continually impressed by AIR/JavaScript fun that can be had in projects. Andy did a great job with this; very well put together presentation. Definitely worth checking out.

Joe Rinehart -- CF Inspirational Session I was not sure what to expect from the "Inspirational ColdFusion" session. Joe gave a great talk about what he's been up to for the last year-ish, how he'd moved away from CF, and how he's coming back into the fold. His presentation was hilarious, and it was one of the more comical yet compelling presentations I've ever seen. Joe missed his calling as a sitcom writer. :)

Rick Root -- Using Java in ColdFusion Applications Rick gave a great introduction to using Java classes in CF. This is a topic that I often see on lists/blogs, but it's always done as the answer to some other question, so the context of said blog posts often makes it hard for people to see that "first look" at how you can dip directly into Java from ColdFusion, and utilize both to improve your applications. That's where Rick's presentation came into play; don't go if you're already doing big crazy things like writing your own Hibernate wrapper for CF7, but if you want to see just how the CF data types are tied to Java data types under the hood (and how to utilize the power of both), then Rick's talk is worth checking out.

Bob Silverberg -- CF9 ORM - Part 1 and 2 Admittedly, I've been kind of dismissing the Hibernate integration in CF9 up until now. Partially because none of my clients have upgraded yet (tho 1 is talking about doing so), and partially because between Transfer and/or some homegrown things, I haven't really had a need to learn much more about it. Bob's talk was so good that I have now officially started "drinking the Kool-Aid", and want to start using CF/Hibernate whenever possible. I may very well (ahem) "borrow" content from his preso and give a similar demo for Saccfug later in the year. I would like to have seen a code sample in action, but the code presented in the slides was clear and "real world enough", that maybe that's not necessary.

It was also good seeing the familiar faces that I met at cf.Objective() -- Ben Nadel, Jason Dean, and others I'm forgetting -- as well as catching up with my friend Rob who moved to NC several years ago. North Carolina is a really pretty area -- trees everywhere, decent weather (and this is coming from a spoiled California punk *g*), and EVERYone I met was extremely nice and polite all weekend. Even the grits I had for dinner were pretty good! :)

Nice job, CFinNC, I hope you do it again next year. -nolan