5 Proven Strategy to Ehancing Database Designs That Work Fast
In this article you will gain more in-depth knowledge on enhancing database designs with the use of primary and foreign keys, indexes and normalization techniques. Having a better understanding of designing databases will provide the software application you are using an edge by performing queries more effectively and maintaining data integrity at all times.
In the previous chapters, you have been dealing with the CUSTOMER table and the ORDER Table. What if you have another table called the PRODUCT Table that contains the following fields or columns: Product ID, Product Name and Price per Unit? You will have the following relationship from these three tables:
From the figure above, the CUSTOMER_TBL and the PRODUCT_TBL are the parent tables of the child table ORDER_TBL (this describes a parent-child relationship in database design). As you can see, the fields named CustomerID and ProductID are the primary keys of the parent tables. These two fields are also present in the child table. They now become foreign keys of the ORDER_TBL table. In other words, a foreign key is a column or field present in the child table that references to the primary key of its parent table.
Unlike the primary key, a foreign key does not need to be unique all the time. In addition, the name of the foreign key could be different from the name of the primary key that it references to. Furthermore, the ProductID of the parent table (PRODUCT_TBL) can never have duplicate entries, but not the corresponding ProductID in the child table (ORDER_TBL). However, you should not define and create a foreign key value if there is no matching primary key value.
The storage spaces of an index and the table from which it was created are separate. Such allocated physical space can also increase tremendously, even larger than the table it references. That is why storage requirements are taken into consideration when designing databases. Just like tables and views, indexes can also be created or dropped. When designed correctly, they actually speed up SELECT queries but could slow down DELETE, UPDATE and INSERT statements. For enormous databases, data retrieval will definitely consume so much time. However, such index transactions have no effect on the table’s data.
An index is associated to a particular column when it is created. It then holds the location of the data values of the table that contains that particular indexed column. Whenever new data is added to the table, it will also be added to the index. Let’s say you execute a SELECT statement with a certain condition specified in the WHERE clause that checks the column that is indexed. The first thing that will happen is that the index is first searched and will only return the exact location if the data value is found.
If the ORDER_TBL table is indexed on the CustomerID column, then the records will be arranged in an ascending order based on that column. Thus, the CustomerID index makes it easier for the search process to take place and finally resolves the location of all the data with the matching Customer ID. Once the location is determined, the corresponding rows of data will be retrieved from the ORDER_TBL table. Without the existence of the index, a full scan will be performed, which will not be efficient if the table contains hundreds or even thousands of records.
Assigning Primary and Foreign Keys
It is one of the best practices to assign a primary key when you define a database table. In a relational database, the primary key is a special field or combination of fields that make each record in the table unique. Since the presence of the primary key does not permit the duplication of values on the column to which it was assigned, then data integrity is guaranteed. Also, fields that are designated as primary keys cannot contain null values. Defining a primary key, whether it is explicit or implied, occurs during table creation. Normally, the tables with primary keys are regarded as parent tables, meaning these tables provide information to another table or what is termed as the child table. Consequently, child tables are dependent on the parent table.In the previous chapters, you have been dealing with the CUSTOMER table and the ORDER Table. What if you have another table called the PRODUCT Table that contains the following fields or columns: Product ID, Product Name and Price per Unit? You will have the following relationship from these three tables:
From the figure above, the CUSTOMER_TBL and the PRODUCT_TBL are the parent tables of the child table ORDER_TBL (this describes a parent-child relationship in database design). As you can see, the fields named CustomerID and ProductID are the primary keys of the parent tables. These two fields are also present in the child table. They now become foreign keys of the ORDER_TBL table. In other words, a foreign key is a column or field present in the child table that references to the primary key of its parent table.
Unlike the primary key, a foreign key does not need to be unique all the time. In addition, the name of the foreign key could be different from the name of the primary key that it references to. Furthermore, the ProductID of the parent table (PRODUCT_TBL) can never have duplicate entries, but not the corresponding ProductID in the child table (ORDER_TBL). However, you should not define and create a foreign key value if there is no matching primary key value.
Understanding Indexes
When a database starts to slow down, specifically its SQL queries, you can create and implement indexes to improve its performance. Such indexes are important objects that serve as pointers associated to the data of a particular table. The primary function of an index is to determine the exact physical location of the data when a query is executed to improve its retrieval process. It works like a book’s alphabetically arranged index that helps you find the information you need in a much easier way using its page numbers. Thus, time is saved, since you do not need to scan one row at a time (most especially in extremely large databases) and just go directly to the required record.The storage spaces of an index and the table from which it was created are separate. Such allocated physical space can also increase tremendously, even larger than the table it references. That is why storage requirements are taken into consideration when designing databases. Just like tables and views, indexes can also be created or dropped. When designed correctly, they actually speed up SELECT queries but could slow down DELETE, UPDATE and INSERT statements. For enormous databases, data retrieval will definitely consume so much time. However, such index transactions have no effect on the table’s data.
Creating Indexes
An index is associated to a particular column when it is created. It then holds the location of the data values of the table that contains that particular indexed column. Whenever new data is added to the table, it will also be added to the index. Let’s say you execute a SELECT statement with a certain condition specified in the WHERE clause that checks the column that is indexed. The first thing that will happen is that the index is first searched and will only return the exact location if the data value is found.
If the ORDER_TBL table is indexed on the CustomerID column, then the records will be arranged in an ascending order based on that column. Thus, the CustomerID index makes it easier for the search process to take place and finally resolves the location of all the data with the matching Customer ID. Once the location is determined, the corresponding rows of data will be retrieved from the ORDER_TBL table. Without the existence of the index, a full scan will be performed, which will not be efficient if the table contains hundreds or even thousands of records.
About Unknown
No comments