Application DB methodology

A methodology is an ordered set of principles and practices used to describe the functions, activities, jobs and steps of a business and the elements required to support that business.

Rows contain fields, the values of which, describe organizations, persons, activities, locations or things. Each row also contains a special field, referred to as the primary key, that uniquely identifies each row in the table. The primary key is used by the database management system (DBMS) to access a particular row or group of rows in a table. The primary key is a proper key when it is a unique number.

There are two languages and two terminologies used in the analysis and design of databases. One set describes the logical business elements during the analysis (business) phase and one set describes the physical database elements during the design (technical) phase. The architect uses the language of the business to talk to the customer and uses the language of the DBMS when speaking to the database administrator (DBA).

• An entity is discovered during analysis and becomes a table during design.

• An attribute is discovered during analysis and becomes a field during design.

• An identifier is proposed during analysis and becomes a key during design.

The reason for two sets of language is that the language of analysis may be used to specify databases in architectures that don't have tables or fields. Also, if the architect/database analyst references design elements as tables, keys, and fields during the analysis phase, we are subconsciously making design decisions prematurely. This is particularly true of keys, since database design is identifying and assigning key. If keys are mentioned during analysis, they are always referred to as "candidate keys". They don't become keys until design.

Application databases have been around a long time in support of automated systems. The earliest ones were on 80-column punched cards. Over the years, these application databases have migrated from punched cards to magnetic tape, to disk packs and on to mass storage devices. New techniques were introduced and added to the database design knowledgebase during these migrations but much of today's design technique still has it's roots firmly embedded in these earlier technologies. Some of these techniques do more harm than good but remain in the literature and are still taught as gospel.

There are organizations that try to build databases on the cheap by having a single individual perform both the requirements and design phases. This is an absolute mistake and everybody in the enterprise will pay a huge price for this decision down the line.

Transition from Requirements to Design provides an opportuinity for a quality check when a data architect and DBA collaborate. It is a built in peer review at the correct place in the lifecycle.

If the enterprise tasks the data architect with the design and buid, you will get a crappy design that will perform badly, simply because the data architect is business oriented, not technically oriented.

Conversly, if a DBA performs both the business analysis and database design you will get a poor requirements deliverable, mainly because the DBA thinks in terms of tables, items and keys. There is nothing more offputting to the user community than having to talk to technicians. Their most common complaint is, "the techie doesn't listen to me."

It's the way it is. You can not change human nature.