A system needs to find and address different objects efficiently and accurately while remaining easy to understand, develop and keep up.

Names are primarily a handle for people, as systems do not value the difference between OBJ1234 and CUST_NM. So, good names instantly convey meaning that otherwise is only available through tacit knowledge or locked away in a document somewhere.

A naming convention that changes names when implementation changes demands that everyone referring to it dances to its tune. It creates unnecessary coupling and makes the object name a “kitchen sink” of different meanings. This includes less obvious values like _DIM and _FACT suffixes as Jeff Kanel commented in the TDWI BI and DW Discussion group on LinkedIn. For example: Customer is hardly a fact, as Sales Order is hardly a dimension. Sales Order Status? Clearly a dimension.

If _KEY means surrogate key, that indicates content; if _KEY means primary key, that indicates function. A surrogate key has as its sole purpose to substitute a primary key, hence content follows function and the separation becomes difficult to see.

If namespaces do not give enough context to properly name the object, it might become necessary to add the role to the name, not the function. Imagine an employee table with an EMPLOYEE_KEY and you need to have a self-referencing foreign key to document the organizational structure. Name it MANAGER_EMPLOYEE_KEY to show its role, not EMPLOYEE_FKEY to show its function nor MANAGER_KEY where the manager role replaces the employee meaning.

As a last comment, consistency and good naming patterns allow logic that can make decisions based on the object name. Your solutions can become more flexible at the cost of a more disciplined naming convention.