Share this Page URL

Chapter 2. The Basics of Database Design > Planning and Designing the HelloWorl... - Pg. 55

The Basics of Database Design 55 Defining Fields After you have your initial tables and fields defined, you can start refining the list of fields that each table will contain. Do you have all the information you need to record for each product, for example? What data besides the product name do you need? Do you need to know who supplies each product to you and how much your supplier charges you? Do you sell each product in the same quantities? Are some products sold in bulk and others as single units? You might need fields to manage in- ventory--one field to store the quantity you have on hand and another to record how much has been ordered. Each bit of information like this might have its own field in a table. A field can be classified as an object that stores a straightforward fact (a name, for example, which is defined by a source outside the database), as an object that categorizes a record (an employee's title, for example), as an object that's used as a record identifier (often a primary key field), or as a field used to create a relationship between two tables. As you identify and define fields, here are some guidelines to work with: · In general, store information in the smallest logical parts. For example, don't use a single Name field; store names in fields such as First Name and Last Name. Consider also whether you need a field for a person's title (Dr., Mr., Ms.) or a middle initial. · Address fields, like fields used to store names, have particular design considerations. You should usually divide addresses into separate fields for street address, apartment number, city, state or region, postal code, and country. When you're working with an address field, consider what information you'll store in the database. Are all the addresses in a single city or state, or do you expect to store addresses from countries around the world? · Avoid storing the same information in multiple tables (unless you are adding a foreign key field to a table to create a relationship). · Don't include a field in a table if it will result in duplicate information appearing in more than one