When you're defining a <data type> specification, think about whether you really need a numeric <data type> for the expected data. Don't just ask: are the values always bunches of digits? For example, phone numbers are digits but if you define a DECIMAL <data type> for them you might lose a valuable piece of information -- whether a leading zero is significant. Identification numbers are digits but if you define a DECIMAL <data type> for them you might have trouble calculating the check digit, which is usually based on a substring extraction. Instead, consider the question: will I ever need to do standard arithmetic operations on the data? If the answer is "no", use a string <data type> rather than a numeric type.

If the answer is "yes", then consider which numeric type to choose by answering the question: are the values going to be seen by users or by programs written in other computer languages? If the former: it's a lot easier to explain to a user looking at a blank six-position field on a screen: "you can type in a number between -99999 and +9999" instead of "you can type in a number between -32767 and +32767". If the latter: pick the numeric type that's closest to the variable type that the other computer language will use. You can also follow this short decision tree:

IF (numeric values might be huge (> 1 quadrillion) or tiny (< 1 quadrillionth)
  /* you need an approximate numeric <data type> */
  IF (your host program uses C "float" or Delphi "Single")
  AND(7 digit precision is satisfactory)
    /* you need a REAL <data type> */
  IF (your host program uses C or Delphi "double")
  AND(15 digit precision is satisfactory)
    /* you need a DOUBLE PRECISION <data type> */
ELSE (if values are not huge or tiny)
  /* you need an exact numeric <data type> -- the usual case */
  IF (your host program uses C "short int" or Delphi "SmallInt" */
    /* you need a SMALLINT <data type> */
  IF (your host program uses C "int" or Delphi "Longint" */
    /* you need an INTEGER <data type> */
  ELSE
    /* you don't need an exact match with host-language variables */
    IF (you are accustomed to the word NUMERIC because Oracle uses it)
      /* you need a NUMERIC <data type> */
    ELSE
      /* you need a DECIMAL <data type> */

Once you've gone through the decision tree, calculate the required precision and scale by looking at all expected values.

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.

Comments

Comments loading...