Learn
A3.2.3 | DATA TYPES IN RELATIONAL DATABASES
01 | WHAT DOES THE VALUE MEAN?
A school database records a student ID, club capacity, membership fee and meeting date. They may all appear as characters on a screen, but the database needs to handle them differently. You calculate with a capacity, compare dates chronologically and preserve the leading zeros in an identifier.
A data type specifies the kind of value a column represents and helps determine its permitted values, representation and supported operations. It is a rule about meaning and handling, not just what a value looks like when displayed.
For example, 00127 can be an identifier rather than a quantity. Storing it numerically may keep the value 127 while losing the original five-character representation. Conversely, storing quantities as text can make arithmetic and ordering harder.
This lesson outlines common relational data types, explains why consistent choices matter and traces the effects of choosing incorrectly. Exact type names, sizes and conversion behaviour vary between DBMSs.
02 | THE MAIN TYPE FAMILIES
| Family | Typical SQL-style examples | Useful purpose |
|---|---|---|
| Whole numbers | INTEGER, SMALLINT, BIGINT | Counts and suitable numeric identifiers |
| Exact decimals | DECIMAL(p,s), NUMERIC(p,s) | Exact decimal amounts and quantities within chosen precision |
| Approximate numbers | REAL, DOUBLE PRECISION | Measurements and scientific calculations accepting approximation |
| Characters/text | CHAR(n), VARCHAR(n), TEXT | Names, codes, contact details and descriptions |
| Logical values | BOOLEAN or a supported equivalent | A true/false fact |
| Dates and times | DATE, TIME, TIMESTAMP or equivalents | Calendar values and events |
| Binary content | BLOB, BYTEA or equivalents | File bytes and other binary values |
These are families, not one universal product specification. A particular system may use different names or have different typing rules. Start with the required meaning, then select a supported implementation.
Types can be combined with constraints. An integer type does not by itself mean a capacity must be positive or that a row identifier must be unique.
03 | INTEGER: WHOLE-NUMBER QUANTITIES
An integer represents a whole number without a fractional component. Club capacity, number of copies and an item count are natural examples when fractional values have no meaning for the requirement.
Integer variants provide different supported ranges. Selecting too small a range can reject future valid values or cause overflow-related problems under the relevant system’s rules. Choosing a very large representation unnecessarily can also have storage or processing implications.
Do not assume a negative number is impossible merely because a type is integer. A separate constraint may require capacity to be at least zero. Nor does an integer column automatically generate unique IDs; identity generation and key constraints are separate choices.
A quantity requiring a fraction should not be forced into an integer without an explicit representation rule, such as deliberately storing money in a defined smallest currency unit.
04 | EXACT DECIMAL: PRECISION AND SCALE
An exact decimal type represents decimal values within its declared precision and scale without binary floating-point approximation of those stored decimal values. It is commonly useful for amounts where decimal rounding must follow specified rules.
In a common DECIMAL(p,s) convention, precision p is the total number of decimal digits and scale s is the number after the decimal point. DECIMAL(8,2) therefore permits six digits before the point and two after it in this conventional example.
The positive maximum in that example is 999999.99. The sign and decimal separator do not count as extra precision digits. This is not the same as allowing eight digits before the point.
Input with more fractional digits may be rounded or rejected according to the DBMS and operation, and arithmetic results need appropriate rounding policy too. Exact decimal storage does not make every financial calculation correct automatically.
05 | APPROXIMATE NUMBERS: REAL AND FLOATING POINT
Floating-point types represent numbers approximately using a finite set of values. They are useful for scientific and measured data where an appropriate approximation and range suit the requirement.
Many decimal fractions have no exact finite binary floating-point representation. An expression involving 0.1 and 0.2 can therefore reveal a tiny representation difference. This is not a claim that every floating-point value is inaccurate: many values are exactly representable.
Use an exact decimal representation, or another deliberately designed exact approach, when exact decimal amounts are required. For sensor measurements, approximate types may be appropriate, with suitable precision and comparison tolerances.
The demonstration uses the browser’s binary floating-point arithmetic, not a query against a particular database. It shows the idea rather than claiming all DBMS result formatting is identical.
Check how representation differs from the intended decimal arithmetic.
06 | CHAR AND VARCHAR: TEXT IS NOT JUST LETTERS
CHAR(n) commonly represents fixed-length character data, with padding behaviour depending on the system. VARCHAR(n) commonly permits variable-length text up to a stated limit. Other text types can support larger descriptions under their own rules.
A name, email, postal code or telephone number can contain digits without being a numeric quantity. Telephone values can also need a plus sign, separators or an extension. A text representation can preserve the required form.
Choose length from the actual requirement and expected data. A short field can reject a valid name; some conversions or systems may truncate excess input, losing information. Do not assume every database silently truncates or every database always raises the same error.
Character support also depends on encoding and collation settings. Text length in characters and storage in bytes are not necessarily identical, especially with Unicode.
07 | PRESERVE THE IDENTIFIER YOU ACTUALLY NEED
If StudentID is defined as exactly five digits and the leading zeros are meaningful, 00127 is a five-character identifier. Storing it as a number ordinarily preserves only 127 as the numeric value. Display formatting can add zeros later, but that requires a known rule and does not preserve every arbitrary original input.
If a future ID can contain letters, a numeric-only representation becomes an even poorer fit. A text type plus an appropriate format constraint can express that requirement.
The activity below uses fixed examples to show the representation difference. It does not assign IDs, edit records or determine uniqueness.
08 | BOOLEAN: A DEFINED TRUE/FALSE FACT
A Boolean type represents logical true/false values. HasParentalConsent can be suitable if the requirement genuinely asks for a binary fact. Accepted literal spellings and storage details vary by DBMS.
A nullable Boolean may also be unknown or absent through NULL. That is different from false: “consent is not recorded” is not necessarily the same state as “consent was refused”. Decide whether the application needs to distinguish them.
Do not use one Boolean for a status with several meaningful states such as Pending, Approved and Withdrawn. A suitable constrained text/status representation or related status table can model that richer requirement.
BOOLEAN does not automatically mean one physical bit of database storage. The logical values and the product’s storage implementation are separate questions.
09 | DATE, TIME AND TIMESTAMP
A DATE represents a calendar date without requiring a time of day. A TIME represents a time of day. A TIMESTAMP or similar datetime type represents a date and time under the system’s supported semantics.
BirthDate is normally a date, while a recorded booking event may need a date/time. Storing an event across time zones also requires a deliberate policy about instants, offsets and display zones; a timestamp type does not universally store the original named zone.
Typed values enable appropriate date operations and checks. Free-form text such as 03/04/2026 can be ambiguous without a format rule. A standardised representation such as 2026-04-03 improves clarity for an example.
A displayed format is not the same as internal storage. Changing a date’s screen format does not necessarily change the stored date value or type.
10 | BINARY DATA AND SPECIALISED TYPES
A binary type stores byte sequences, such as the content of an image or document. Names such as BLOB or BYTEA depend on the DBMS. It is not simply a column containing the text “0101”.
A system may instead store a file reference in a text column and keep the bytes in a separate storage service. These are different designs with different backup, access and consistency responsibilities. A reference is not the image itself.
Some relational systems provide specialised types such as UUIDs, enumerations, spatial values and JSON. JSON is typically semi-structured and needs suitable validation and querying; availability and behaviour vary by product.
For this objective, know the main families and why a type fits a value. You do not need to memorise every vendor’s specialist catalogue.
11 | NULL IS NOT A DATA TYPE OR A DEFAULT ZERO
NULL represents missing or unknown information under database rules. It is not itself the general data type “unknown”, nor automatically the same as zero, false or an empty string.
If Fee is NULL, its amount has not been supplied under that interpretation. If Fee is 0.00, a known zero amount has been recorded. Confusing the two can alter summaries and decisions. SQL expressions and aggregates have particular NULL rules that later query lessons develop.
A NOT NULL constraint requires a value. A default can supply a chosen value when the applicable insertion rules use it, but an inappropriate default may hide genuinely missing information.
Document the meaning of missing values rather than filling every blank with zero just to make the table look complete.
12 | DATA TYPE CONSISTENCY ACROSS RECORDS AND RELATIONSHIPS
A consistent column represents the same kind of fact under compatible rules for every row. Storing one fee as a decimal and another as free-form “about twenty” makes reliable calculation difficult.
Referencing and referenced key columns also need compatible definitions for the DBMS to enforce relationships correctly. Mixing a five-character code with an incompatible numeric reference can lose zeros or require conversion rather than express the intended identity.
Consistency does not mean every column has the same type. StudentName and Capacity should differ. It means each attribute and relationship uses a suitable coherent representation.
Import processes and applications must respect the schema too. Implicit conversion may hide mistakes, reject values or add query cost; do not rely on undocumented coercion to repair inconsistent design.
13 | SORTING CHANGES WHEN MEANING CHANGES
For a numeric quantity, 2 is less than 10, which is less than 100. In a simple character-by-character text ordering, the strings “10” and “100” can appear before “2” because their first character is compared as text. Actual text order depends on collation.
Dates stored as inconsistent day/month strings can likewise sort in a way that does not match calendar order. A consistently formatted ISO-style text representation can have useful ordering properties, but that does not provide every operation and constraint of a date type.
The comparison below states the ordering rule explicitly. It illustrates a type-related mistake without pretending every DBMS collation behaves identically.
14 | TYPES AND VALIDATION ARE COMPLEMENTARY
A type rejects or interprets values according to its own rules, but often does not express the full requirement. INTEGER permits whole numbers; a check constraint may be needed to require Capacity from 1 to 40.
VARCHAR(5) can hold many five-character strings, not just the intended two uppercase letters and three digits. A format constraint or server-side validation adds that requirement. A primary key separately enforces unique non-null identification.
A syntactically valid date can still be the wrong birth date for a person. Verification and domain-specific checks may be needed. Type consistency improves usable structure without proving every fact true.
The next lesson combines type decisions with keys and well-defined relational tables. Keep each rule’s purpose distinct.
15 | CHOOSING TYPES FOR THE CLUB SYSTEM
| Attribute | Suitable starting choice | Reason and additional rule |
|---|---|---|
| StudentID | Text identifier, for example VARCHAR(5) | Preserve required code form; add key/format constraints |
| StudentName | Suitable Unicode-capable character/text type | Allow realistic names; choose length from requirements |
| Capacity | INTEGER | Whole-person count; add permitted range |
| MembershipFee | Exact decimal, for example DECIMAL(8,2) | Chosen decimal range/scale; add currency and rounding policy |
| MeetingDate | DATE | A calendar date with appropriate operations |
| BookedAt | Suitable timestamp/datetime representation | Record event time with an explicit time-zone policy |
| HasConsent | BOOLEAN where binary meaning fits | Decide whether unknown is permitted |
| PhotoContent | Supported binary type, or a designed external-file reference | Distinguish file bytes from a reference and protect both |
There can be more than one valid implementation. For example, deliberately storing amounts as integer minor units can also be exact if the unit is explicit and sufficient. Justify the choice rather than treating one label as mandatory in every system.
16 | EFFECTS OF A POOR TYPE CHOICE
| Poor choice | Possible effect |
|---|---|
| Quantity stored as arbitrary text | Awkward arithmetic, lexical ordering or conversion failures |
| Meaningful zero-prefixed code stored numerically | Original identifier representation can be lost |
| Too-short text or too-small numeric range | Rejected valid data, failed imports or lossy conversion under applicable behaviour |
| Approximate float used where exact decimal rules are required | Unexpected rounding/comparison results |
| Ambiguous free-form date text | Misinterpretation, invalid values or incorrect chronological processing |
| Incompatible related key representations | Failed constraints, conversion problems or incorrect matches |
| Zero used instead of genuinely missing information | Misleading calculations and business decisions |
The exact failure mode depends on the DBMS and operation. A good outline identifies the risk and its cause rather than asserting one universal conversion behaviour.
17 | MATCH THE TYPE TO THE REQUIREMENT
Choose the type family that best fits the stated meaning. The questions specify the requirement so that a justified choice is possible; additional constraints and implementation details would still be needed.
18 | OUTLINE THE TYPE AND ITS PURPOSE
For outline, give a brief account of the main points. State the type family, what it represents and one relevant use. Add the consistency or wrong-choice consequence where the question requires it.
For example: “An integer represents whole-number values such as club capacity. An exact decimal type represents chosen decimal amounts such as a fee, while a floating-point type provides approximate numeric representation suitable for some measurements. Text stores names or identifiers whose written form matters.”
Then add: “Date/time types support calendar and event values, Boolean represents a defined true/false fact, and binary types store bytes. Consistent compatible types support correct queries and relationships; poor choices can lose information, misorder results or require unreliable conversion.”
Keep storage details conditional on the implementation. Type names alone do not supply uniqueness, real-world truth, access control or every validation rule.