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
  3. Fields with the same meaning and different names (in different tables)
  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??

No comments:

Post a Comment