Comments on: Naming Database Columns, When to Violate DRY

By: jsonmez

jsonmez — Mon, 10 Sep 2012 13:39:32 +0000

In reply to Bill. Oh, because id is used as the foreign key, so when you do joins, it is more confusing for multiple id columns.

By: Bill

Bill — Mon, 10 Sep 2012 13:32:21 +0000

Just revisiting this article as a result of some recent comment activity. How come your argument has only been applied to the ID columns and not the Name columns in the Author and Publisher tables?

By: jsonmez

jsonmez — Mon, 10 Sep 2012 13:09:47 +0000

In reply to Baxter Basics. Great points! Thanks, and thanks for the book recommendations.

By: Baxter Basics

Baxter Basics — Fri, 07 Sep 2012 09:14:20 +0000

Hmm. I basically agree with your convention for explicitly naming “surrogate” keys like this. “Natural” keys tend not to exhibit this problem. For the book example, wouldn’t “ISBN” be a better choice of key?
As a general principle, never use a surrogate when a natural key is available.
This brings to light a broader problem: What’s to stop us putting garbage like “1234” or “Fred” in our “ISBN” column? ISBN numbers have a distinct and validatable structure. Lack of effective type (or “domain” in the relational parlance) support in SQL databases is the *real* problem here. I should be able to define an “ISBN” data type and write declarative constraints such that it must take the format “nnn-n-nnn-nnnnn-n”. We can then define a book table with, for example, a title and an ISBN-typed “ISBN” column and place a unique constraint upon that column (actually an ideal candidate for a primary key with nonclustered index). Now, the database will actually prohibit the matching of ISBNs with surrogate author IDs (or whatever).
MS SQL Server does feature a user-defined type which will help solve the problem you cite above, although it is sadly lacking in being able to constrain what data you can place in a type.
It’s worth getting “SQL and Relational Theory” by C.J.Date, and “Practical Issues in Database Management” by Fabian Pascal for true enlightenment in database kung-fu.
All the best

By: Karl

Karl — Sun, 28 Mar 2010 21:53:15 +0000

I agree. Clarity in queries can almost eliminate a lot of thought regarding looking at code. Especially if you are new to a schema, that little prefix helps to identify the behavior the original dev went for.

By: jsonmez

jsonmez — Thu, 25 Mar 2010 14:26:00 +0000

In reply to Bill2. It could be considered a violation of DRY because you are repeating the name of the table in the column, but I agree with you DRY is mostly applied to writing the same code over again.

By: Bill2

Bill2 — Thu, 25 Mar 2010 14:03:12 +0000

I agree, prefix your id columns with the name of the table for clarity… it’s so easy to your JOINs mixed up otherwise.

BTW I’m not sure this is against the spirit of DRY, which is mostly about not writing the same code over and over. Using slighter longer column names for clarity would probably be encouraged.

By: jsonmez

jsonmez — Wed, 24 Mar 2010 21:23:29 +0000

In reply to Bill.

You are correct. I didn’t mean my last paragraph to say that we should do that. I was intending to indicate that some people may argue against what I had said, by saying “you don’t need to name the id for the book, book_id because it is a column on book, so you know that id refers to the id of the book”

I don’t disagree with that logic, but overall I think the option I present here is better.

Thanks for pointing that out though. I wasn’t very clear in what I was trying to say.

By: Bill

Bill — Wed, 24 Mar 2010 21:10:42 +0000

Your article has a reasonably put argument, but then you spoil it in the last paragraph by claiming it would be acceptable to leave the ID of the Book table simply left as ID.

It seems to me you’ve fallen into a very well used trap of failing to consider the future scope of the application.

What if some future requirement necessitates the introduction of another table that has a foreign key to the Book table. That table is then going to suffer from having to join on Book.ID in the same manner as your Author and Publisher did initially.

If you argument is to use fully qualified column names for foriegn keys, it should be applied consistently.