- 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 type | Holds | Example field |
|---|---|---|
| Text (string, CHAR/VARCHAR) | Letters, digits and symbols treated as text | Name, address, phone number |
| Integer | Whole numbers | Number of copies, age |
| Decimal / Float | Numbers with a fractional part | Price, percentage |
| Date / Time | Calendar dates and times | Date of birth, date of issue |
| Boolean (Yes/No) | Only two values: true or false | Fees paid? |
| Currency | Money amounts with fixed decimals | Fee amount |
| AutoNumber | A number the system increases by itself | Record 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.
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. Which data type suits a phone number best?
- Boolean
- Date
- Text
- Decimal
Show answer
Answer: C. Phone numbers are identifiers, not quantities, and may begin with zero, so text is the safer type.
-
2. A field holding only 'Yes' or 'No' should use the
- Integer (whole number)
- Date and time
- Currency amount
- Boolean (Yes/No)
Show answer
Answer: D. A field with only two possible values is a Boolean.
-
3. A validation rule 'Marks between 0 and 100' will
- Accept any number
- Delete old records
- Reject a mark of 120
- Change the key
Show answer
Answer: C. Entries that break the rule are refused, so 120 is rejected.
-
4. NULL in a field means
- No value, unknown
- The number zero
- A blank space
- The word 'null'
Show answer
Answer: A. NULL marks a missing or unknown value. It is different from zero or an empty space.
-
5. A default value is
- The largest value permitted
- A value filled in if none given
- A secret password for entry
- The key that links tables
Show answer
Answer: B. A default value is filled in automatically when the user does not enter one.
Sign in to keep track of the lessons you finish.