A Little More About Design

When designing a database model, the intention is to refine database model structure for reasons of maintaining data integrity, good performance, and adaptations to specific types of applications, such as a data warehouse. A data warehouse database model requires a different basic table structure than an OLTP database model, mostly for reasons of acceptable reporting performance.
Performance is not exclusively a programming or implementation construction or even a testing or "wait until it's in production" activity. Performance should be included at the outset in both analysis and design stages. This is particularly important for database model analysis and design for two reasons:
Redesigning a database model after applications are written will change applications (a little like a rewrite — a complete waste of time and money).
There is simply no earthly reason or excuse to not build for performance right from the beginning of the development process. If this is not possible, you might want additional expertise in the form of specialized personnel. Hiring a short-term consultant at the outset of a development project could save enormous amounts of maintenance, rewriting, redevelopment costs, and time in the future.
Business events and operations discovered in the analysis stage should be utilized to drive the design process, which consists of refining table pictures and ERDs already drawn. For larger projects, the design stage can also consist of detailed technical specifications. Technical specifications are used by programmers and administrators to create databases and programming code.
Essentially, the beginning of the design process marks the point where the thought processes of analysts and programmers begin to mix. In the analysis stage, the approach was one of a business operation (a business-wide view) of a company. In the design stage, it starts to get more technical.
When designing a database model, you should begin thinking about a database model from the perspective of how applications will use that database model. In other words, when considering how to build fact and dimensional table structures in a data warehouse database model, consider how reports will be structured. Consider how long those reports will take to run. For a data warehouse, not only are the table contents and relationships important, but factors such as reconstruction of data using materialized views and alternate indexing can help with the building and performance of data warehouse reporting.
Relational database model design includes the following:
Refine Database Models — At this stage of the game, most of this is about normalization and denormalization.
Finalization and Approval — This includes finalization (and most especially, approval) of business and technical design issues. You need to get it signed off for two reasons:
Software development involves large amounts of investment in both money and time. Management, and probably even executive management approval and responsibility, is required. A designer does not need this level of worry, but may need some powerful clout to back up the development process. Other departments and managers getting in the way of the development process could throw your schedule and budget for a complete loop.
Note
Every project needs a sponsor and champion; otherwise, there is no point in progressing.
You need to cover your back. You also need to be sure that you are going in the right direction because you may very well not be. There is usually a good reason why your boss is your boss, and it usually has a lot do with him or her having a lot more experience. Experience is always valuable. It is extremely likely that this person will help you get things moving when you need, such as when you need information and help from other departments.
Note
So, it's not really about passing the buck up the ladder. It's really about getting a job done. The stronger approval and support for a project, the better the chance of success — unless, of course, it's your money. In that case, it's your problem! So, pinch those pennies. Of course, some entrepreneurs say that the secrets to making money are all about cash flow, spending it, and not stashing it in the bank for a rainy day. Be warned, however, that software development is a very expensive and risky venture!
So, the design stage is the next stage following the analysis stage. Design is a process of figuring out how to implement what was discovered during the analysis stage. As described in Chapter 9, analysis is about what needs to be done. Design is about how it should be done. The design stage deals with the following aspects of database model creation:
More precise tables, including practical application of normalization.
Establishment of primary and foreign key fields.
Enforcement of referential integrity by establishing and quantifying precise relationships between tables, using primary and foreign key fields.
Denormalization in the design stage (the sooner the better), particularly in the case of data warehouse table structures.
Alternate and extra indexing in addition to that of referential integrity, primary and foreign keys; however, alternate indexing is more advanced (detailed) design
Advanced database structures, such as materialized views, and some specialized types of indexing. Similar to alternate indexing, this is also more advanced (detailed) design