Dashboard / MySQL Datatypes

Common MySQL Datatypes

A reference for moving from spreadsheets to typed relational tables — MIS 370, Nathaniel Hobbs

Download Handout (.docx) Download Syllabus (.docx)
Main idea. A spreadsheet can hold data, but a relational database also describes and enforces what kind of data belongs in each column. ER diagrams model entities and relationships; SQL CREATE TABLE statements turn that model into a working system by naming columns, assigning datatypes, and adding constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE.

1. Why datatypes matter

In Excel, a column may look like a field, but the spreadsheet usually does not enforce one clean rule for every cell. One row might contain a number, another text, another a date formatted as text, another nothing at all. That flexibility is useful for quick analysis but risky for shared systems.

In a relational database, every column has a datatype. The datatype tells MySQL:

  • what values are allowed,
  • how values should be stored,
  • how sorting and comparison work, and
  • which operations are meaningful.

It also surfaces errors earlier. A badly formatted date, a text value in a numeric column, or a missing required value can be rejected instead of silently mixed into the dataset.

ER diagrams describe entities, attributes, and relationships.
CREATE TABLE statements implement those designs as actual tables.
Datatypes and constraints help enforce the design so that later queries, joins, summaries, and dashboards are more reliable.

2. A column definition is a promise

When you write a column definition, you are making a promise about the values in that column:

column_name  DATA_TYPE  optional_constraints

For example:

order_total     DECIMAL(10,2)  NOT NULL
order_date      DATE           NOT NULL
customer_email  VARCHAR(255)   UNIQUE

The datatype handles the kind of value. The constraints handle additional rules: whether the value is required, whether it must be unique, whether it is a primary key, or whether it must match a value in another table.

3. Common MySQL datatypes

The table below emphasizes types that appear frequently in MIS 370 and in business databases.

Type Best for Examples Notes
INT Whole numbers IDs, counts, quantities, number of employees Use for most integer fields. Often used for synthetic primary keys.
BIGINT Very large whole numbers Large transaction IDs, large row counts Use when INT might eventually be too small.
DECIMAL(p,s) Exact decimal numbers Money, prices, rates needing exact cents Use DECIMAL(10,2) for most money fields. Avoid FLOAT for money.
FLOAT / DOUBLE Approximate decimal numbers Measurements, scientific values, model scores Approximate — small rounding differences are normal.
VARCHAR(n) Variable-length text Names, emails, addresses, product names Good default for short text. Choose n based on realistic max length.
CHAR(n) Fixed-length text US state code, country code, fixed product code Useful when values are always the same length.
TEXT Long text Comments, descriptions, notes Use when text may be long and not a good fixed length.
DATE Calendar date only Birth date, order date, flight date Format is YYYY-MM-DD.
DATETIME Date and time Reservation time, transaction time, appointment time Stores date and time together. Good default for business event times.
TIMESTAMP Date and time with timestamp behavior Created-at and updated-at audit fields Useful for logs/audit fields; be aware of timezone and range behavior.
TIME Time of day or duration Class start time, elapsed time Use only when no calendar date is needed.
BOOLEAN / TINYINT(1) True/false flag is_cancelled, is_active, shipped_flag In MySQL, BOOLEAN is a synonym for TINYINT(1): 0 = false; nonzero = true.
ENUM(...) Small fixed list of text values 'pending', 'paid', 'cancelled' OK for small, stable categories. For categories that may change, prefer a lookup table + foreign key.
JSON Semi-structured data Raw API response, flexible settings object Useful for irregular data. Do not use JSON to avoid proper relational design.
BLOB Binary data Images, documents, uploaded files Often business systems store a file path or URL instead of putting the file itself in MySQL.

4. Rules of thumb to remember

  • Do not store dates as text. Use DATE or DATETIME so MySQL can sort, compare, filter, and group by time correctly.
  • Do not store money as FLOAT. Use DECIMAL so cents are represented exactly.
  • Do not store ZIP codes or phone numbers as numbers. Use VARCHAR — these are identifiers, not quantities; leading zeros and formatting matter.
  • Match foreign key datatypes exactly. If customer.customer_id is INT, then orders.customer_id should also be INT.
  • Do not put many values in one cell. A field like products = "A, B, C" is a spreadsheet habit. Repeated values usually belong in another table.
  • Avoid VARCHAR(255) everywhere. Choose lengths that reflect the business meaning when practical.
  • Use NOT NULL when a value is required. If every order needs an order date, declare order_date DATE NOT NULL.

5. Example: from spreadsheet columns to relational tables

Suppose a spreadsheet has one row per order, with columns for customer name, customer email, order date, order total, and order status. A better relational design separates customers from orders and enforces the relationship with a foreign key:

CREATE TABLE customers (
    customer_id     INT          PRIMARY KEY,
    first_name      VARCHAR(50)  NOT NULL,
    last_name       VARCHAR(50)  NOT NULL,
    email           VARCHAR(255) UNIQUE,
    signup_datetime DATETIME     NOT NULL
);

CREATE TABLE orders (
    order_id    BIGINT           PRIMARY KEY,
    customer_id INT              NOT NULL,
    order_date  DATE             NOT NULL,
    order_total DECIMAL(10,2)   NOT NULL,
    status      ENUM('pending','paid','shipped','cancelled') NOT NULL,
    shipped_flag BOOLEAN         NOT NULL DEFAULT FALSE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What this enforces that a spreadsheet does not:

  • customer_id in orders must refer to a real customer_id in customers.
  • order_total must be a number with two decimal places of precision.
  • order_date must be a date, not free-form text.
  • status must be one of the listed values.
  • Required columns cannot be silently left blank.

6. Check for understanding

  1. A column stores product prices such as 19.99 and 249.50. What datatype should you prefer, and why?
  2. A column stores ZIP codes. Why is VARCHAR usually better than INT?
  3. A spreadsheet has one cell containing several product categories separated by commas. What relational design problem does this suggest?
  4. A table has customer_id INT as its primary key. What datatype should another table use for customer_id as a foreign key?

References & Documentation