Identifying Entities and Attributes

Think of the purpose of your database. For a brief example, to create a database that records orders placed by your customers, you might start with a CUSTOMER table. What are the attributes of a customer? Name, physical address, products s/he orders, order information, credit rating, and so on.

An important part of this stage is the selection of proper data types to represent desired attributes. There are a number of trade-offs to be considered while choosing data types, usually concerning performance versus clarity of a logical design. For example, sales amounts or prices might perfectly fit into a numeric data type in Oracle (and Oracle advocates using the NUMBER data type as opposed to specific numeric types like DECIMAL, INTEGER, and so on). Making this data an INTEGER in the Microsoft SQL Server would be a mistake, because the value will be rounded to the nearest dollar; selecting the MONEY or SMALLMONEY data type would be the correct choice. In addition to this, searches are usually performed faster on numeric fields than on character-containing columns. When you need to store more complex information like images or binary files, use data types appropriate for the data the column is supposed to hold.

Cross-References 

See Chapter 3 for more information on data types.

Table C-1 is an example of the flat file design described in Chapter 1 — everything is dumped into one single table. Such a design would hardly be adequate for anything but a kitchen table enterprise. To create a relational database, this raw table must undergo the normalization process.

Table C-1: "One Table" Design

CUST_ NAME	ADDRESS	SALES_ REGION	SALES_ REGION_ID	ORDER_ID	DESCRIP- TION	CREDIT_ RATING
Lone Dove Ent.	1234 Elm Street, Salem, OR 95679	North Pacific	3	NA1234-89	Paper Pulp	Excellent
ACME, Inc.	567 Pine Street, Chicago, IL 07891	Midwest	4	GA4358-92	Sawdust	Average