D.5. Completing the Information Component

We're going to look at the rest of the framework views just so we can trace how critical the business views at the beginning are in guiding Information Technology's efforts to build the right system. In the next step, the IT team turns the entity relationship diagram (ERD) into physical designs.

D.5.1. Builder's View: Physical Data Model

Just as in each preceding view, the IT team started with the entity relationship diagram. The team reviewed for technical performance issues and suggested some modifications to the logical model to support faster updates, retrievals, and sorting. Each modification recommendation was explained to the business team with pros and cons. Exceptions that were approved and implemented were included in the project documentation, along with the explanation of why it was needed. System performance is always improving; there may be a reason to go back to the original design some time in the future. The people who have to make that change need to know why the database looks the way it does.

Next, Information Technology had to create the technical backbone for implementing the relationships on the ERD. This was accomplished by carrying the key from the independent entity in the dependent entity (sometimes called a foreign key). Many customers may live (or work) at a single address. Because we don't want to store all the duplicate data to put the actual full address in each customer record, the unique identifier of the correct address is stored instead. Using the foreign key address identifier, we can easily find and access all the information about the person's address. And because the address itself is captured only once, if it changes, we just need to update it in one place!

The physical data model includes the physical characteristics of the data to be stored such as type of data (numbers, text) and size (how much space needs to be set aside). Fixed-length data characteristics are generally required for transaction processing and most decision support. There are also many excellent products that support storing formatted and variable-length text for quick retrieval, but this was not a requirement of Valencia.

D.5.2. Detailed Representation: Individual Tables, Rows, and Columns

Now the IT team is ready to actually build the database. XYZ's programmers reproduced the physical data model in a database management system by creating one table for each entity. The database table is made up of a series of columns, one for each attribute, as shown in Table D-15.

The database is empty when it is first built, just like a new empty spreadsheet in which you have only the spreadsheet and a list of column headers.

D.5.3. Functioning System: Physical Working Database

Finally, IT will get the system up and running so that data can be loaded and retrieved. Each individual record (set of information about an entity instance) that is added creates a new row in the data table. A specific row is retrieved based on the value of its key. Once accessed, all the data for the entity instance is available whether it is physically in that table or linked by a foreign key.