Database data types

Computer Science · Databases

Set up the club-membership table

You're building the table for a school club. Each field (a column) gets ONE data type. Pick a field, then pick the type you'd give it, and read why.

6 of 6 still to sort.

Commit to a type for each field first. The reason shows up after.

Predict, then check

Same club table. One member, one telephone number, one wrong choice.

A member's telephone number is 07700900123, and the telephone field has been set to integer. What does the database actually hold?

What you need to know

• A data type says what kind of value a field holds, and so what you can do with it.
• Integer holds whole numbers only. Real holds numbers that have a decimal part.
• Have a goA field stores each pupil's mean quiz score, which could come out as 7.5. Integer or real?

  Real.

  A mean can have a decimal part, and integer holds whole numbers only.

• Boolean holds only true or false, which suits a yes-or-no field like 'fee paid'.
• Character holds a single letter or symbol; a string holds text, like a surname.
• Have a goThe Spooky Society's database has a field 'is a vampire?' that only ever holds true or false. Which data type should it be?

  Boolean.

  Boolean is the type that holds just true or false; a string is for text.

• You pick the type from the data the field will hold and how it will be used.
• Numbers you will calculate with suit integer or real, because calculations cannot be performed on strings.
• Digits you never calculate with, such as a telephone number, are stored as a string so a leading zero is kept.
• Have a goZane says his bus pass number, 0238, must be an integer because it's all digits. What do you tell him?

  Make it a string.

  Digits don't make a number type; he will never calculate with it, and a string keeps the leading zero.

• Each field gets one data type, set once, and every record in the table follows it.
• Before you choose, ask: what will this field hold, and will I calculate with it?

The big picture

Every field in a table has one data type (integer, real, Boolean, character or string), chosen from the data it holds and how that data will be used, not from how it looks.

⚠ Watch out

Choosing a number type for anything made of digits. A telephone number is digits, but it is never calculated with, so it belongs in a string. Ask how the value will be used, not what it looks like.

Memory hook

Sums or no sums? If you'll calculate with it, use integer or real. If it's just digits to look at, like a telephone number, use a string.

Check yourself

Name all five data types. Then pick a type for 'detentions this term' and for 'a swimmer's best time, e.g. 31.07'. What goes wrong if a field you plan to total is a string?