Naming of ID and code fields

Hi
We have a data warehouse which comes from a number of source systems, and a number of these systems cannot provide us with daily changes but we delta the tables and identify the changes ourselves. Our datawarehouse has 3NF a staging area that holds all source tables (as per the source system), and the changes in data over time. From there we take the data and convert it into our dimensional structures.

My understanding is that in database design you should consistently name a data element throughout the database. As we aquire data from source into our staging area ID and code fields have particular names as defined in the source system. However, once we attempt to move these through to our presentation layer tables the tables are designed around business processes. The impact of this is that quite different (often more specific and business focussed) names are used. When we bring through ID fields (say for the purpose of a degenerate dimension, or to allow data to be traced back to the staging area for testing ) should we retain the same name that is used in the source system, and hence our staging are? Or is is acceptable to rename them?

From a purist database perspective it seems like bad practice, however from an end user perspective it seems preferable. Your thougts on this will be much appreciated.

By all means, rename them. That is standard practice.

A lot of legacy systems, especially mainframe, were developed by programmers. The opportunity you have now is to build the new taxonomy based on current business processes. This is also an excellent use of the data governance team to manage and publish common standards moving forward. A database purist always will define the attribute name that corresponds to the definition.