This tutorial describes the need for effective data type selection and advises some best practices for the same

Problem Description


With a number of tables in the database, size of the tables and hence the database as well as performance in terms of retrieval of data is an important consideration. Some of the concerns that need to be addressed while creating the tables are :

  1. Inappropriate usage of datatypes causes redundant unused memory leading to wastage of storage space
  2. Query performance is dependent on the type of data being queried


Operation


Selection of appropriate datatypes is an important consideration when designing a physical database.

Using abbreviations for longer values is one of the ways that can improve the storage capacity.

From the query processing perspective, numeric values can be processed more efficiently than character values, especially when joining values. Therefore, using a numeric datatype can provide a slight benefit.

The following guidelines can be followed while creating tables and associating the datatype of the columns :

  1. Use numeric datatype wherever possible and especially for the Primary Key taking the following considerations into account. This ensures quicker access for frequently accessed columns.
  2. Store the definition of the values in a table in a database, where the definitions can be joined to the value to provide context, such as "text name" or "description"
  3. For variable length strings use varchar instead of having large char length


Solution


Create the tables following the above guidelines as much as possible :

  1. The primary keys for all tables are created with a numeric datatype
  2. The 'order_status' column of the 'order_master' table stores Boolean values ( 0 refers to pending delivery and 1 refers to delivered ). The 'order_status' table serves as a reference table that stores the value description
  3. The 'product_ID' column of the table stores codes for representing some information. The codes are integer numbers and the 'product' tables serves as a reference table storing the description
  4. The VARCHAR columns specified provide appropriate character specification to avoid wastage of space

Result


The tables for the store are created with appropriate datatypes.