Efficiency in Database Design

  • Filter
  • Time
  • Show
Clear All
new posts

    Efficiency in Database Design

    After designing your good normalized tables, you do not just go straight ahead and start coding the tables into the computer. There is still one more stage before you can start coding. You have to address the problem of the efficiency of operation of tables in a practical computer, before you start coding.

    Efficiency involves what is known as Referential Integrity, Table Indexes and Data Dictionary.

    Referential Integrity
    In a one-to-many relationship between two tables, the relationship between the rows must be respected: You cannot have rows in the "many" table without the corresponding row in the "one" table. The occurrence of this situation is senseless. Avoiding this situation is maintaining referential integrity. Referential integrity is achieved by doing some coding in the computer when creating the database, but you have to address the problem first, before you code.

    When referential integrity is maintained, you cannot add a row in the "many" table without the existence of the corresponding row in the "one" table; you cannot delete a row in the "one" table without deleting all the corresponding rows in the "many" table; you cannot update a primary key value in the "one" table without making the corresponding changes in the "many" table. You will see the details of referential integrity later.

    Table Indexes
    When you retrieve data from a table, you do so based on a column (sometimes based on more than one column). The data may be retrieved in a sorted order, alphabetically, for example. In practical computers, you have to declare the columns on which the retrieve is based on as, indexed. In this way, the retrieve will be faster than if the column (or columns) where not indexed. You do this by coding in the computer; but before you code, you have to first address the problem. There are other reasons for indexing columns; you will see the details and all that later.

    Data Dictionary
    It is true that the database in the computer has data, but the data is kept in an organized fashion; the data is not kept arbitrarily. Different tables in the database have different features and tables are interlinked differently in different databases. The organization of data in the database has to be recorded in a notebook. You can also record that in a computer if you want to. The notes in the notebook is called, data dictionary. The notes form data about data (in the database). Metadata means, data about data. So the notes form metadata. The data dictionary enables you to maintain the organization of the data and to update the organization in future, if the need arises. You will see details on data dictionary later.

    As you can see from all the above, the stage before coding of the database in the production process of a database, is to address the operation efficiency of the database in a practical computer. After that, you can code.

    I have prepared a tutorial series on the Efficiency in Database Design. The tutorials have been written in a step-by-step fashion. The tutorials are well formatted with no missing special character. Click the following hyperlink to start the series:

    Efficiency in Database Design