Share this Page URL

Chapter 2. The Basics of Database Design > Normalization: Achieving a Well-Desi... - Pg. 57

The Basics of Database Design 57 This table includes a field named Products in which a list of each of the products provided by that supplier is stored. It also contains a single field indicating whether a product is in stock. If you designed a table such as this, how would you find information about a single product? How do you know whether all of a supplier's products are in stock or just one or two? If you created a separate record in this table for each product you get from a supplier, you would need to repeat the supplier's name, and that creates redundant data. The solution here is to create another table in which to store supplier information and relate the Suppliers table with the Products table. · Second normal form For a table to be in second normal form, it must meet the conditions of first normal form and the criterion that all nonprimary key fields depend on the entire primary key for their identity. (Remember that a primary key can consist of more than one field.) Here's an example: In this table, the primary key is a combination of the product name and the company name. However, the company's phone number does not depend on the combination product name/ company name. It depends only on the company name. In this situation, you should create two tables, one to store product information and another to store information about suppliers. The table setup would look like this: