22 July 2014

Seven things I really hate in database design

  1. Common prexif in all table names
    1. es: TXXX, TYYY, TZZZ, VAAA, VBBB - T stays for Table, V stays for View
    2. es: APPXXX, APPYYY, APPZZZ - APP is an application name
  2.  Common prefix in all field names in every table
         es: APPXXX.XXX_FIELD_A, APPXXX.XXX_FIELD_B, APPXXX.XXX_FIELD_C
  3. Fields with the same meaning and different names (in different tables)
         es: TABLE_A.BANK_ID, TABLE_B.BK_CODE
  4. Fields with the same logical type and different physical types
          es: TABLE_A.MONEY_AMOUNT NUMBER(20,2)
          TABLE_B.MONEY_AMOUNT NUMBER(20,0) -- value * 100
          TABLE_B.MONEY_AMOUNT VARCHAR(20) --value * 100 as char
  5. No foreign-keys nor integrity constraints at all - by design
  6. Date (or generally structured data type) representation with generic and not specific types
    1. es: TABLE_A.START_DATE NUMBER(8,0) -- yyyyddmm as int
    2. es: TABLE_B.START_DATE VARCHAR(8) -- yyyyddmm as char
  7.  (possible only in presenceof 6.) Special values for semantic corner-cases which are syntactically invalid
          es: EXPIRY_DATE = 99999999 -- represents "never expires case",
       but... IT'S NOT A VALID DATE!!! - why not 99991231??