A table contains a collection of rows and columns. Each column in the table is designed to store certain type of information (for example, names, savings details or numbers). The logical design of the database, including the tables and the relationship among them is the core of an optimized, relational database.
A. Normalizing a Database Design:
Optimizing a database design includes the process of normalization. Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables. Having a greater number of narrow tables (with fewer columns) is characteristic of a normalized database; having fewer wide tables (with more columns) is characteristic of a denormalized database. Reasonable normalization often improves the performance. As normalization increases, so do the number and complexity of joins required to retrieve the data.
B. Achieving a Well-Designed Database:
In a relational database design theory, Normalization rules identify certain attributes that must be present or absent in a well designed database.There are few rules that can help you achieve a sound database design.
1. A Table should have an identifier: The fundamental rule of database design theory is that each table should have a unique row identifier, which is a column or set of columns used to distinguish any single record in the table. Each table should have an ID column, and no two records can share the same ID value. The column (columns) that servers as the unique row identifier for a table is the primary key of the table.
2. A Table should store data for only a single type of entity: Attempting to store too much information in a table can prevent the efficient and reliable management of the data in a table.
3. A Table should avoid Nullable columns to the extent possible: Tables can have columns defined to allow null values. A null value indicates that the record has no value for that attribute. Although it can be useful to allow null values in isolated cases it is best to use them sparingly because they require special handling that increases the complexity of data operations.
If you have a table that has several nullable columns and several of the rows have null values in the columns, you should consider placing these columns in another table linked to the primary table.
4. A table should not have repeated values or columns: If you find that you need to store a list of values in a single column or if you have multiple columns for a single piece of data (Author1, Author 2, Author 3 and son on), you should consider placing the duplicated data in another table with a link to the primary table.
C. Entity Relationships:
In a relational database, relationships help to prevent redundant data. A relationship works by matching data in key columns-usually columns that have the same name in both tables. There are three types of relationships between tables: one-to-one, one-to-many; and many-to-many. The type of relationship depends on how you define the related columns.
1. One-to-One Relationship: In a one-to-one relationship, a row in a table A can have no more than one more row in table B (and Vice Versa). A One-to-One relationship is created if both of the related columns are primary keys or have unique constraints.
2. One-to-Many Relationship: A One-to-Many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
3. Many-to-May Relationship: In a Many-to-Many relationship, a row in table A can have many matching rows in table B (Vice Versa)