Naming Database Columns, When to Violate DRY

DRY = Don’t Repeat Yourself.\n\nFor the most part this is a good principle to follow.  But, there is one particular instance where I feel repeating myself makes things more clear.\n\nIt is very important for us to remember that principles are important, but ultimately they are very strong guidelines, not absolutes.  (It is hard to imagine that I just said that, since I am usually arguing on the merit of principles, but I digress.)\n\n

Traditions

\n\nIn particular, when creating database tables and naming database columns, most people will create a table structure that looks like:\n\n\n\nLooks perfectly normal and good.  But, I don’t like it.  Imagine you have to join these tables together to create a query.  (A pretty common occurence.)\n\n\n\nDid you catch my mistake above?  Probably not.  Most people would overlook it.  With the ER diagram above, and not very many columns on the tables, it is a little bit easier to see.\n\nIt is very easy to mix up which table is the parent and which is the child.  It is very easy to put the .id on the wrong side of the join clause.  The correct SQL should have been:\n\n\n\n

Bucking the trend

\n\nNow take a look at this ER diagram:\n\n\n\nThe corresponding SQL for selecting from this database is:\n\n\n\nIt is a small change, but it makes a huge difference.  Writing SQL to join the tables is much easier and much more difficult to get wrong.  Yes, I am repeating myself here, but I am doing it for a very good reason.  I am making the SQL much more clear and I am making it much easier to talk about these tables.\n\nThink about the last time you had a conversation about database tables.  Did you say id?  Did you then have to qualify which id you were talking about?  If you never have “id”, you eliminate a large amount of ambiguity.\n\nThe major argument against what I am suggesting is that 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 do not disagree with this.  For the most part that principle is sound.  The question though is “which one is easier to read, write and talk about?”