• Foundation
  • About 12 min
  • 5 practice questions

Fields and data types: choosing the right type

Choose suitable data types and field settings, and understand NULL, default values, field size and validation.

If something looks wrong, please tell us.

When you design a table, you must decide what kind of value each field will hold. A wrong choice causes errors, wasted space or lost information. Choosing data types is one of the first practical skills in database work.

Common data types

Data types
Data typeHoldsExample field
Text (string, CHAR/VARCHAR)Letters, digits and symbols treated as textName, address, phone number
IntegerWhole numbersNumber of copies, age
Decimal / FloatNumbers with a fractional partPrice, percentage
Date / TimeCalendar dates and timesDate of birth, date of issue
Boolean (Yes/No)Only two values: true or falseFees paid?
CurrencyMoney amounts with fixed decimalsFee amount
AutoNumberA number the system increases by itselfRecord number

Rules of thumb for choosing a type

  • Use a number type only if you will do arithmetic on the field, such as adding fees or finding an average.
  • Use text for codes and identifiers that are never added up, even if they look like numbers: phone numbers, PIN codes and admission numbers with leading zeros. A number type would drop a leading zero.
  • Use date type for dates so that the software can sort them properly and find differences between them.
  • Use Yes/No for any question with just two answers.

Field settings

  • Field size: the maximum length of text or the range of a number.
  • Default value: a value filled in automatically if none is entered, such as 'India' for Country.
  • Required: the field cannot be left empty.
  • NULL: a special mark meaning 'no value' or 'unknown'. It is not the same as zero or a blank space.
  • Validation rule: a condition the entry must meet, for example Marks between 0 and 100.
  • Primary key: a field whose values are unique and never NULL.

Worked examples

Example 1. Choose a data type for each field of a STUDENT table: RollNo, Name, DateOfBirth, Marks (out of 100, may have decimals), PhoneNo, FeesPaid. RollNo: integer (or AutoNumber). Name: text. DateOfBirth: date. Marks: decimal. PhoneNo: text, because we never add phone numbers and a leading zero must be kept. FeesPaid: Yes/No.

Example 2. A clerk types a mark of 150 into a Marks field that has the validation rule 'between 0 and 100'. What happens? The database rejects the entry and shows a message, which protects the accuracy of the data.

Example 3. A student's email address is not known yet. What should be stored? NULL, meaning unknown. Storing 0 or a space would pretend that a value exists. Also, a query counting emails would skip NULLs correctly.

Example 4. A Country field has the default value 'India'. A clerk adds ten students and types nothing in Country for any of them. Every one of the ten records will show India, which saves time and avoids mistakes.

Number type only for values you calculate with. Codes and phone numbers are text.

— Remember

Key takeaways

  • Match the data type to the kind of value and the operations you need.
  • NULL means unknown, not zero.
  • Validation rules and defaults keep data accurate and save effort.
  • A primary key must be unique and not NULL.

Check your understanding

  1. 1. Which data type suits a phone number best?

    1. Boolean
    2. Date
    3. Text
    4. Decimal
    Show answer

    Answer: C. Phone numbers are identifiers, not quantities, and may begin with zero, so text is the safer type.

  2. 2. A field holding only 'Yes' or 'No' should use the

    1. Integer (whole number)
    2. Date and time
    3. Currency amount
    4. Boolean (Yes/No)
    Show answer

    Answer: D. A field with only two possible values is a Boolean.

  3. 3. A validation rule 'Marks between 0 and 100' will

    1. Accept any number
    2. Delete old records
    3. Reject a mark of 120
    4. Change the key
    Show answer

    Answer: C. Entries that break the rule are refused, so 120 is rejected.

  4. 4. NULL in a field means

    1. No value, unknown
    2. The number zero
    3. A blank space
    4. The word 'null'
    Show answer

    Answer: A. NULL marks a missing or unknown value. It is different from zero or an empty space.

  5. 5. A default value is

    1. The largest value permitted
    2. A value filled in if none given
    3. A secret password for entry
    4. The key that links tables
    Show answer

    Answer: B. A default value is filled in automatically when the user does not enter one.

Privacy · Terms · Refunds · Delivery