perkthim 2

Normal Forms In database design there are a number of forms (or properties, or constraints) that a table scheme may possess. A table may assume a specific form to achieve certain design goals such as minimizing dependency. These forms are called normal forms. The following normal forms are discussed: • First Normal Form 1NF • Second Normal Form - 2NF • Third Normal Form - 3NF Each of these forms includes its predecessor. For example, a 2NF table is also a 1NF table, and a 3NF table is also a 2NF table, which is also a 1NF table. By definition, a table is 1NF. While it is generally desirable for database tables to have a high normal form, the situation is not as simple as it seems. Forcing all table schemes to be in a particular normal form may require some compromises. The following table summarizes the most common normalization rules that describe how to put entity types into a series of increasing levels of normalization.

The important thing to remember is that you want to store data in one place and one place only. With respect to terminology, a database schema is at the level of normalization of its least normalizes entity types. For example, if all entity types are at 2NF or higher, then the database schema is at 2NF First Normal Form 1NF In table Order0NF you can see that there are several repeating attributes - the ordered item information repeats nine times, and the contact information is repeated twice (once for shipping and once for billing). But what happens if an order has more than nine items? Do you create additional records for them? What about the majority of orders that have one or two items? Do you want to write redundant code to process the nine copies of order information? The following figure represents a worked data schema where the [Order] schema is put into first normal form:

Table [OrderItem1NF] allows us to have as many order items as we like. This increases the flexibility of the schema while reducing storage requirements for small orders. Table [ContactInformation1NF] also offers a similar benefit when the billing and shipping addresses are the same. Table [OrderPayment1NF] was introduced to allow customers to make 0 or more payments against an order from [Order1NF] as table [Order0NF] presented previously could not accept more than two payments. An important thing to notice here is the application of primary and foreign keys in the new schema. When a new table is introduced into the schema as the result of first normalization efforts, it is common to use the primary key of the original table as part of the primary key of the new table. For example, [OrderItem1NF] also includes the OrderID column within its schema. But because, many order items can have the same OrderID, ItemSequence was added to form a composite primary key for table [OrderItem1NF]. A different approach was taken with table [ContactInformation1NF]. The column ContactID, a surrogate column that has no business meaning, was made the primary key, while OrderID was needed as a foreign key to maintain the relationship back to table [Order1NF]. A good rule of thumb is that if two tables are strongly related (i.e., [Order1NF] and [OrderItem1NF]) then it makes sense to include the primary key of the original table as part of the primary key of the new table. If the two tables are not as strongly related (i.e., [Order1NF] and [ContactInformation1NF]), then a surrogate key makes more sense.