In this chapter
We'll see why sorting prices as text breaks in painfully obvious ways — and match GreenMart's data (prices, phone numbers, dates) to the data type each one actually needs.
The Problem in Real Life
Sarah wants a quick answer: which products cost under $10? She sorts the Products table by price — and the results make no sense at all. $22 sits above $3. $40 sits right next to $4.
Every price in the table was typed in as plain text, the same as a product's name. To the database, "22" and "3" were never numbers being compared by size — they were just two words, compared letter by letter, the exact same way it would compare "apple" and "banana".
| Product | Price | In Stock |
|---|---|---|
| Rice (5kg) | 22 | 30 |
| Milk | 3 | 22 |
| Apples | 4 | 40 |
| Family Pack | 40 | 10 |
Sorted "by price" — except 22 comes before 3, and 4 sits right next to 40. As text, "22" really does come before "3": the database is comparing characters one at a time, not sizes.
Since when is 22 less than 3?
Sarah
Looks Like a Number vs. Is a Number
Sorting breaks
"22" comes before "3" once everything is compared as plain text.
Formatting silently vanishes
A phone number stored as a number can lose its leading zero or dash for good.
Dates can't be trusted
"Aug 2" and "8/9" can't be reliably compared or filtered by month.
Simple questions get hard to answer
"Which products are under $10?" needs a real number to answer correctly.
What Are Data Types, Really?
Every value Sarah has typed into a table so far has technically been fine to save — a table doesn't ask what kind of thing a value is unless something tells it to. Left alone, it defaults to treating everything the way text always behaves: compared character by character, sorted alphabetically, never added or subtracted. That's exactly what just happened to Price.
The fix is to tell the table, column by column, what kind of value actually belongs there — its data type. A data type isn't really about what a value looks like on screen. It's about how the database is allowed to compare it, sort it, and calculate with it.
GreenMart's tables need a handful of different types, each answering a different question about a column:
- Numeric — for values you do math with and sort by size: Price, Quantity, how many are in stock.
- Character (text) — for values that are really just labels, even when they happen to be made entirely of digits: a Phone number, a Customer's name, a Product's name.
- Date/Time — for values the database needs to compare chronologically: was this sale before or after that one — not just which text string comes first alphabetically.
- Boolean — for a plain true-or-false fact, like whether a product is currently active for sale.
| Product | Price | In Stock |
|---|---|---|
| Milk | 3 | 22 |
| Apples | 4 | 40 |
| Rice (5kg) | 22 | 30 |
| Family Pack | 40 | 10 |
Same four products, same four prices — now compared by size instead of by character. 3, 4, 22, 40: an order that finally makes sense.
| Customer | Phone |
|---|---|
| Wendy | 020-5551234 |
Phone looks like a number, but nobody at GreenMart ever adds two phone numbers together or sorts customers by phone size. Stored as a number, the leading 0 and the dash would both silently vanish. Stored as text, the exact number stays intact.
The test that actually matters isn't "is this made of digits" — it's "would GreenMart ever do math with this, or sort it by size?" Price and Quantity: yes, always. Phone and a Customer ID: never — they just happen to be written using digits.
Dates deserve the exact same care as prices. A sale date typed as free text — "Aug 2" one day, "8/9" the next — can't reliably be compared or filtered at all. A real date type lets GreenMart ask "which sales happened in August" and get a real answer, the same way a numeric Price now lets it ask "which products cost under $10."
Key Takeaway
A data type isn't about what a value looks like — it's about how the database is allowed to compare, sort, and calculate with it.
Why This Matters
This closes a loop from two chapters ago: a CHECK constraint like "price must be greater than 0" only makes real sense once Price is actually a number — as text, "−2" being "less than" 0 isn't even a well-defined question. Choosing the right type for every column is also exactly what the next chapter's ER diagram has to capture on paper, and it's what makes every later query, sort, and calculation in this course behave the way a reader expects instead of silently misbehaving like today's price column did.
GreenMart's tables now know their entities, their keys, their links to each other, their rules, and the right kind of value for every column. The next chapter puts all of it down on paper, as a single diagram, before a single line of SQL gets written.