Tables, Rows, Columns, and Data Types

Lesson 2 of 8 · Database Fundamentals · Beginner

Every relational database stores data in tables. Understanding how tables are structured — and why data types matter — is the foundation for everything else in this path.

Tables

A table is a collection of related data organized into rows and columns. Each table in a database represents one type of entity — for example, a programs table holds information about programs, and a participants table holds information about participants.

A well-designed database has one table per entity type. Mixing unrelated information into a single table creates confusion and makes querying harder.

Rows and Records

Each row in a table represents one instance of that entity — one program, one participant, one location. A row is also called a record. Every row in a table should represent exactly one thing, and no two rows should represent the same thing.

Columns and Fields

Each column represents one attribute of the entity — the program name, the start date, the capacity. A column is also called a field. Every row in the table has a value (or a deliberate absence of a value) for each column.

Schema

The schema is the definition of a table's structure — its column names, data types, and constraints. The schema is set when the table is created and controls what data the table will accept. Changing a schema after data has been entered requires care.

Data Types

Every column in a relational database has a data type that defines what kind of value it stores. Common data types include:

• Text / VARCHAR. Stores character strings — names, descriptions, codes. Length limits are often specified.
• Integer / INT. Stores whole numbers — counts, identifiers, quantities.
• Decimal / NUMERIC. Stores numbers with fractional parts — prices, measurements, percentages.
• Date. Stores calendar dates — 2024-03-15. Stored as a date value, not as text.
• Timestamp / DATETIME. Stores a date and time together — 2024-03-15 14:30:00.
• Boolean. Stores true or false values — active/inactive, yes/no.

Why Data Types Matter

Choosing the correct data type is not just a technical detail — it affects how data behaves.

• Dates stored as text sort alphabetically, not chronologically. "2024-10-01" sorts after "2024-09-30" correctly, but "October 1" and "Sept 30" do not sort in any reliable order.
• Numbers stored as text sort as strings: "10" comes before "9" because "1" comes before "9" alphabetically.
• Calculations on text fail. You cannot sum a column of numbers if they are stored as text.
• Date arithmetic requires date types. Calculating the number of days between two dates only works if both are stored as dates.

Null Values

A null value means the value is unknown or not applicable — it is not the same as zero, an empty string, or the word "none". A null in a capacity column means the capacity is unknown. A zero means the capacity is zero. These are different facts.

Columns can be defined as NOT NULL, which means the database will reject any attempt to insert a row without a value for that column. This is a useful constraint for required fields.

Key Takeaways

• Tables hold rows (records) and columns (fields) for one entity type.
• The schema defines column names, data types, and constraints.
• Correct data types enable sorting, filtering, calculation, and validation.
• Null means unknown — it is not the same as zero or empty string.
• NOT NULL constraints enforce required fields at the database level.