The process for designing a database that supports the operational functions of an organization begins with simple understanding. The data architect or modeler needs to obtain a working knowledge of the language comprising the universe of discourse for the solution. This awareness is gathered through many activities, such as talking with the people currently doing the work, sitting with them and watching how they do their tasks, reading over existing training manuals or standard operation procedures. The designer is best served when figuratively walking a mile in the shoes of the future application users. The more that the designer knows about the user needs and goals, the better able the designer is to definitively craft a data model supporting user tasks. If the proposed endeavor is to upgrade or replace a legacy application, supporting tasks should include reviewing existing data models, dictionaries, and specifications, as well as discussing what things work well or poorly within the current framework. Additional insights can arise via discussion with any visionaries or strategists involved in the project; these individuals can offer directions or detail future functions that will be necessary. From this process of subject emersion, the data modeler starts becoming familiar with the terms, the data items, the values, the interrelationships, and the meanings behind the functions to be supported. One can start identifying the major items, the nouns that are used in describing it all, keep track of the meaning of the terms and their interrelationships, and start weeding out the entities from the attributes using functional dependencies to split and group things.
Along the way, the data modeler must keep an eye out for the demon known as "ambiguity" as uncertainty can lead to fuzzy designs, and vague data models often need complex or even convoluted application logic to make the "magic" happen. Ideally, data structures defined with the proper clarity generally will lead to processes that are also clear and simple. Certainly, complexity arises from the combining of many simple processes, so complexity itself is not a symptom of ambiguity. However, unexpected complexity without simple components underneath it all may suggest a need for developing further understanding. One aspect of ambiguity is evidenced by having a single term that is used in multiple ways. This situation can arise if the term involved is actually an abstraction, a super-type, and the subtypes have never been clearly expressed. Alternately, it could involve undefined states and transitions that are not explicitly articulated for the related object. A modeler should never be afraid to explore "expanding" the language of the solution to help remove multiple meanings and uses from single tables or columns within a design.
In addition to any entity relationship diagrams, a good designer will start fleshing out high-level data flow diagrams (at least a level 0 and level 1 set of descriptions), and state-transition diagrams, for critical objects encompassing the functionality in scope. During the inception of these perspectives, experienced designers will reuse functional patterns that have become familiar over time. These concepts and details should interlay seamlessly within whatever class and application design documentation is created by the application architects. Incompatibles will only lead to problems down the road; therefore data architects must work well with application design personnel in achieving a complete solution.
Always step back and review designs with the subject matter experts available to the project. A database design can be read as a series of imperative statements listed column-by-column and relationship-by-relationship. Make sure your experts agree with each of the imperative statements. Use their feedback to refine the design. Lastly, bring in DBA support to delve into physical aspects for tuning the expected performance.