I have wanted to blog about a simple DB Design approach that can make or break facilitation of coding, readability, maintenance, and ultimately warehousing approaches.
It is the "id"/"name" debate. For instance you have a "Status" table. In this table you have a list of status names and their respective surrogate keys in the id field driven by a sequence or identity field (Oracle/Sql Server).
Best practice in this situation is to label your id field status_id or statusID (Oracle/SQL Server). Due to upper casing in oracle an underscore is used for readability and has developed into a standard for Oracle DB naming, SQL Server allows mixed case which parlays nicely into your object model where the C-based syntax world has standardized on camel case for class properties and doesn't require aliasing in SQL code.
This very issue while on a project with a Microsoft Centric Dev working for the first time with an Oracle DB was a huge bone of contention. He had to alias all his sql due to the underscores as this made it easier for us when copy/pasting sql return assignments. Made good sense and I felt for him being a Oracle newb. This is one thing SQL Server has over Oracle when it comes to readability.
But back to the topic... The reason behind prefixing the id field with table name is for readability when joining. Imagine if all tables had id/name fields. This would be confusing rather quickly now you'd have to prefix at the very least in the table where the foreign key was assigned and I've seen worse. A child (foreign key) table that has a label that doesn't necessarily relate to the parent. For instance stat or statpkey... Acckkk! If the parent only had statusID and in turn the child table also had statusID, the developer, sys arch., dba will readily understand the relationship.
When it comes to the name field it is my opinion that this field should be prefixed too, because you run into name being everywhere and pending on whether you had a good SQL coder or not you are going to have to alias that field every time you use it which is a pain when many IDEs out there generate sql code for you.
Lastly when it comes to the name field you have to think about whether or not data mining will be used in the future against this data. Hopefully in a data warehouse. When it comes time to building the warehouse from the transaction db solution, generating the SQL and DDL to do so is much more straightforward and will facilitate flattening of the tables as the table in the originating transaction db will be prevalent in both the id and name fields. I look forward to your comments.