Datasheet

Part I ✦ Laying the Foundation

Generalization

Generalization is the grouping of similar entities into a single entity, or table, so that a single

table does more work. The database becomes more flexible because the generalized entities

are more likely able to store a new similar entity.

Generalization depends on the idea that there is no single correct normalized schema. For

any database problem, several correct solutions are possible. The difference between these

solutions is often identifying the problem space’s various entities. One view of the entities

might identify several specific entities that are technically different but similar. A more gener-

alized view would merge the similar entities, producing a simpler and more compact schema.

I can’t overemphasize the importance of generalization. Even more than normalization, this

is the technique I depend upon to design simple but flexible schemas. A recent database

schema I analyzed used about 80 tables. Using generalization to combine five entities (and

associated tables) into one entity, I reduced the schema to 17 tables, and the result was

more flexible and powerful than the original. When a database developer brags that he has

designed a database with a huge number of tables, I assume that he probably developed a

wasteful system.

Security

The final primary database objective based on the Information Architecture Principle is secu-

rity. For any organizational asset, the level of security must be secured depending on its value

and sensitivity. For software, the security begins with the physical security of the data center

and the operating system’s security. Information security includes three additional compo-

nents: restricting access to specific data using the database engine’s security, identifying the

owner of the information, and confirming the veracity of the data by identifying the source,

including updates.

Restricted Access

Any production-grade database includes the capability to limit access to the data to individu-

als or group of individuals. The granularity of restriction may range from simply limiting

access to specifying create, select, update, and delete privileges.

SQL Server 2005 security, which can be complex, is detailed in Chapter 40, “Securing

Databases.”

Information Ownership

The owner of the data is the person or organizational group who required the data. This per-

son is typically not the person or process who entered or created the data. The owner is the

person who is paying the cost of maintaining the data. For some systems, a single owner may

be identified for the entire application. For other systems, each table or row may have an

identified owner. For example, all the data within an inventory system may belong to the vice

president of manufacturing, while the corporate client contact information may belong to the

branch that is serving the contact. Row-level data ownership must be implemented in the

schema by adding a

DataOwner column.

Cross-

Reference

05_542567 ch01.qxp 9/27/06 9:58 PM Page 20