Importance of Good Database design
...ed as being atomic (Poolet 1999). In addition, designers need to determine the data type for each field. In order to ensure that a database structure is well organized, efficient and accurate, designers employ a technique called normalization. This also ensures the proper entity relationships (ER) are formed between the tables. This technique can be quite powerful in ensuring the ER’s between tables are accurate and won’t break down over time. Database Normalization The process of organizing data in the database by creating tables and establishing the relationships between them is called normalization. Database normalization includes eliminating redundancy and inconsistent dependency in table designs. It also increases and ensures data integrity. Redundant data takes up additional disk space and causes inefficiencies. It also causes maintenance problems requiring consistent updating of the same data in more than one place. Errors in redundant data entry result in inconsistent data reporting. Normalization of data occurs most frequently in Online Transactional Processing (OLTP) environments. Good database design allows quick, random and efficient modifications to the data being stored. Data in Online Analytical Processing (OLAP) environments usually contains summarized, denormalized data (Poolet 1999). Basic guidelines for normalization of data are called "normal forms". There are several form levels working from the general to more specific; however, the third normal form is the highest level utilized for most applications: § Rules for the first normal form (1NF) include eliminating repeating groups, creating tables for each set of related data and identifying a primary key (link to description) for each table. § Data that is in the second normal form (2NF) has been further organized into sets of data that apply to multiple records, which are related by a foreign key. § The Third normal form (3NF) eliminates fields that are not dependent on the primary key. Table Relationships Database designers use relationships to link data from one table to another. Properly designed databases integrate data by utilizing relationships effectively. There are three types of relationships between entities: § A one-to-one relationship means that each record in a table has only one related record in another table. § The one-to-many is the most common type of relationship. This means that one record in a table can have a relationship to many records in other tables. This type of relationship is often called parent-child. § The many-to-many relationship means that that many records in one table can have a relationship with many records in another table. Many-to-many relationships result in redundant data and must be resolved by using an intermediate table made up of the primary keys of both tables. This converts the many-to-many relationship to two, one-to-many relationships. Constraints Designers maintain data integrity by implementing rules and constraints. There are three types of constraint categories: § Domain Constraints apply to columns. The constraint is applied when individual rows are inserted or updated to ensure that the data is appropriate for the relevant column. § Entity Constraints applies to individual rows. This constraint ensures that each row is unique and is not a duplicate row. A primary key is an entity constraint. § Referential Integrity Constraints means that a value in one column matches a value in another column, typically in a different table. Primary and Foreign Keys Keys are used in relational databases to uniquely identify each row in a table. The primary key is composed of unique values. This will ensure that no row values will be the same and must never be null. This enables the database engine to individually select a row by its primary key and allow data modification in that row without compromising the data in any other row. Therefore, the choice of the primary key value for each table is one of the most important design elements in a database (Poolet 1999). Any column in a table containing a unique value is called a candidate key, which the primary key may be chosen from. After the primary key is determined, the other candidate keys become alternate keys. In addition, a primary key can consist of a combination of two or more columns; however, the most efficient primary keys consist of one column. (Poolet 1999) This is described as a composite key. When choosing the primary key, database designers should identify the unique column or columns that contain the simplest value that is most unlikely to change over time. Integer data types are best suited for primary keys in SQL Server as character data types must be converted to ASCII equivalent vales prior to processing, which is an additional step in processing (Poolet 1999). This will speed access in retrieval of data. A foreign key assists in maintaining data integrity, and also implements relationships between tables. It is a table value that points to a related row in a different table. In order to enforce referential integrity, a foreign key must never be invalid (Garrick 1997). Data Types There are many data types, including numeric, integer, character and variable character for example. Choosing an appropriate data type is an important design issue. Proper choice of data type not only ensures proper display of information for the end user, but also assists in ensuring integrity and good database performance. Indexes Database designers utilize indexes as a met...