When assembling a database design, one of the keys for success is consistency. There should be more than just similarity in the way things are named, the manner in which tables or groups of tables are constructed; the manifestation of these elements should follow standards and practices that are documented and understood. If one tries to rely on the idea that individual developers will simply look at existing tables and glean standards via osmosis as they add on or create new tables, then one does not actually have any standards at all.
Standardization
Standardization comprises many simple elements. Here are some examples that might be included in these standards:
- Each column name ends with a class word. The class word is from a specific limited list of class words managed by the data administration group.
- Each table is named in the singular.
- Each table has "audit" columns composed of "created" and "last updated" timestamps and identifiers.
- Each dimension table has a "current row flag" regardless of whether the slowly changing dimension approach is Type 1 or Type 2 currently.
Standardization should include information which addresses when and how natural keys or surrogate keys are used. Practices should lay out the strategy of the most appropriate time to use more abstract table structures versus more concrete components. Later designers adding in new components should have access to the rules used to decide when things are done one way versus another. Armed with knowledge of the approach and rationale used inside the solution, those who follow can make decisions consistent with the overall design without having to re-evaluate the entire application.
Exceptions to the Rule
Certainly, occasional exceptions to the standard practices may arise. The existence of rules does not mean those rules must be followed blindly without exception. As professionals we are expected to apply our powers of thought and our skills to the task at hand. When application of the rules encounter a circumstance where the rules application may cause harm, or generate an excessive amount of needless effort, other options may be considered. However, exceptions should be approached with caution; exceptions should be rare. And exceptions must be documented in a place where everyone knows how to find the reason for the exception. If you are making multiple exceptions, then something is either wrong with the rules, or wrong with the approach being used toward the rules.
Guiding Principles
As you build a design, having a set of guiding principles is important. Consistency in naming, plus the structural patterns, gives you a database design with an attitude. And it is that attitude that allows developers to learn what they can rely on to help standardize their code into proper services or other reusable units of work, which all function to build a successful solution. Many may consider such details of little consequence, but truly the little things do matter. Even if one could save a few minutes by simply ignoring the "standard way," doing the consistent thing is what helps raise a solution to a more solid foundation for later enhancements. It simply demonstrates a lack of consideration for everyone else when tables are developed in such a way that in order to properly recognize an active row across tables, it requires the usage of varying columns and filters. Faster completion can occur if the various pieces are more obvious and intuitive. Consistency and well-known practices enable raising the probabilities for success in every project. Failure to establish and enforce levels of consistency is tantamount to wasting the limited opportunities for success. And why should one purposefully want to make things harder for anyone?