Best Practices: Designing a new DB Table

Following these simple guidelines would help achieve better performance 

  • Define a column as NOT NULL when possible
    • When column value could be missing consider using dummy data instead of allowing nulls
  • Use DATE & DATETIME field types for date and time values. See Data Type Formats
  • Text Fields
    • Choose between Varchar & Nvarchar field types for ascii vs. multi-language support, considering functionality differences 
    • Analyze values to identify the minimum possible column width and use it as column size definition (e.g.: VARCHAR(20))
    • Use numeric field types for ID fields as much as possible
      • Create mapping tables to resolve numeric ID into original text values
    • Use numeric field types for a fixed set of domain values (such as Status Descriptions or Country Names)
      • Create Dimension tables to map keys to their values. See Normalization
  • Numeric Fields
    • To avoid the need for explicit cast use INT/BIGINT when possible
    • Note that when the numeric type reaches its datatype size limitation (tinyint 256, smallint 32767 etc.) SQream will return an overflow message
      • Cast large results to Int/BigInt to avoid overflows
  • Break values to logical units and place in separate columns. E.g.:
    • A telco might have full phone number in a single value such as +972-3-5444871. This number could be broken to three different logical units: country code (972), area code (3) and local number (5444871).
    • In this case, designing the table with 3 different columns for the 3 logical units would allow for better compression, easy sorting and most importantly, speed up query times by allowing for better filter conditions. It could also help converting text fields to numeric fields by saving the need to store separator characters. 
  • 128
  • 17-May-2017
  • 674 Views