انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
منشورBEG-049-L01

الجداول والصفوف والأعمدة

  • الدرس 1 من 6
  • 12 دقيقة قراءة
  • مبتدئ
في هذا الدرس 11 موضوعات

هذا الدرس متاح حاليًا باللغة الإنجليزية فقط، ولم تُنشر نسخته العربية بعد.

Lesson Overview

A relational database stores everything in tables. Before you can write any SQL, you have to decide what the tables are: which kinds of things your application cares about, and which facts you keep about each one. That decision is called data modeling, and it is the first design skill of a backend developer.

In this lesson you will turn real-world things (a book, a member, a loan) into tables. You will learn the vocabulary (entity, table, row, column, type), how to pick a sensible type for each column, and how to name things. Throughout the module we use one analogy: a library card catalog, with one drawer of cards for each kind of thing.

Why this matters: a table designed badly is painful to repair once real data is in it, and every later lesson in this module (keys, relationships, normalization) builds on the tables you draw here. This module is paper-and-pencil modeling: SQL itself starts in a later module, so we write tables in a short text notation.

You should know from BEG-048 what a database, a relational database, and a schema are. We do not use any other earlier concept.

Learning Objectives

By the end of this lesson you will be able to:

  • Define entity, table, row, and column, and say how they relate.
  • Turn a real-world thing into a table by listing the facts you need to keep about it.
  • Choose a sensible column type (text, integer, decimal number, date, boolean) for a given fact.
  • Name tables and columns clearly and consistently.
  • Recognize when a table mixes more than one kind of thing.
  • Read and write the notation books(id, title, isbn, published_year).

Written Explanation

Entities become tables

An entity is a kind of thing your application needs to remember. A library cares about books, members, and loans. A shop cares about customers, products, and orders. Finding the entities is the first modeling step: listen to how people describe the system and write down the nouns that matter.

Each entity becomes one table. A table is a named grid with a fixed set of columns and any number of rows.

  • A row is one instance of the entity: one specific book, one specific member.
  • A column is one fact that you keep about every instance: the title, the email address, the date joined.

In the card-catalog picture, a table is a drawer, a row is a card, and a column is a printed field on the card ("Title:", "ISBN:"). Every card in the drawer has the same fields.

text
books(id, title, isbn, published_year)

This one line is our notation for a table: the table name, then the column names in brackets. Here is the same table with data:

idtitleisbnpublished_year
1Dune97804411727191965
2Emma97801414395871815

Two rows, four columns. The headings are the schema (the structure); the rows are the data. In BEG-048 you met the idea that a schema describes the structure of the data. A table definition is exactly that.

One table, one kind of thing

The most important rule at this stage: a table describes one kind of thing. If you notice a books table that also stores the member who borrowed the book, you have mixed two entities. The loan date, member name, and book title would all be squeezed into one table, and you would repeat the member's details every time they borrow. Later lessons give the formal name for this problem (anomalies); for now, use the rule as a quick test: can I describe each row with one noun? "A book." "A member." "A loan." If the honest answer is "a book-and-the-member-who-has-it", split it.

Columns have types

Each column holds one kind of value, called its type. The database rejects values that do not fit. This protects your data: you cannot store the word "tomorrow" in a column that is meant to hold a date.

Common types, described in general terms. The exact keywords differ between database systems (PostgreSQL, MySQL, and others), and BEG-050 will show the real ones, so treat the names below as the idea rather than exact spelling:

Kind of factGeneral typeExample value
Words of any lengthtextDune
Whole numberinteger1965
Number with a fractional part, exactdecimal12.50
A calendar daydate2025-03-14
A moment in timetimestamp2025-03-14 09:30:00
Yes or nobooleantrue

Guidance for choosing:

  1. Pick the type that matches how the value behaves, not how it looks. A birth date is a date, not text, because you will want to compare dates.
  2. Use decimal types for money. Ordinary floating-point numbers store many fractions only approximately, which is unacceptable when you add up prices. Exact decimal types avoid that.
  3. Identifiers that are not for arithmetic are not numbers. An ISBN or a phone number can start with 0 or contain a plus sign, and you never add two of them. Store them as text. (A generated row id, by contrast, is an integer, as the next lesson shows.)
  4. Prefer a boolean to a text flag. A column is_active holding true or false cannot be mistyped as ture.

Missing values

Sometimes a fact is not known yet. A loan that has not been returned has no return date. Databases represent "no value" with NULL. NULL is not zero and not an empty string; it means "nothing here". When you model a table, decide for each column whether a value is required. A member must have a name; a loan may have no returned_on yet. You will see how to declare this in the SQL module, but you should already note it on your sketch.

Naming

Clear names save hours later. A common convention, and the one this course uses:

  • Lowercase letters, with underscores between words: published_year, not PublishedYear or published year.
  • Table names are plural nouns: books, members, loans. (Some teams prefer singular; consistency matters more than which you choose.)
  • Column names say what the fact is, without repeating the table name: title, not book_title, inside books.
  • Avoid spaces and special characters, because they force quoting in SQL.
  • Name dates ..._on or ..._at so the type is obvious: joined_on, returned_on.

A modeling recipe

List the entities; list the facts the application will actually use for each; choose a type and mark required columns; apply the "one noun per row" test; finally add an identifier column (the next lesson explains why).

Examples

Example 1: the books table (simple)

A library wants to record its books. Entity: book. Facts: title, ISBN, year published.

text
books
  id              integer
  title           text      required
  isbn            text      required
  published_year  integer

A row is one book; for a library with 20,000 books, the table has 20,000 rows and the same four columns. Why is isbn text? An ISBN is an identifier, never added or averaged. Why is published_year an integer rather than a date? We only know the year and will not do date arithmetic on it; a full date would suggest precision we do not have.

Example 2: the members table (practical)

The library registers people who may borrow books.

text
members
  id         integer
  name       text      required
  email      text      required
  phone      text
  joined_on  date      required
  is_active  boolean   required

Decisions worth noticing:

  • phone is optional (not every member gives one) and text (leading zeros, plus signs).
  • joined_on is a date: we can later ask "who joined this year?".
  • is_active lets us keep an old member's record without deleting it.
  • name is one column; splitting it is common, but decide by what the application needs.

Sample rows:

idnameemailphonejoined_onis_active
1Layla Hassanlayla@example.com05012345672024-09-01true
2Omar Saidomar@example.com(no value)2025-01-15true

Omar's phone is NULL: unknown, not an empty text.

Example 3: loans, and a table that mixes two things (realistic)

Now the library wants to track who borrowed what. A beginner's first attempt puts everything in one table:

text
loans_bad(id, member_name, member_email, book_title, book_isbn, loaned_on, due_on, returned_on)

Apply the test: one noun per row? Each row is "a loan, plus the member's details, plus the book's details". The member's name and email repeat every time they borrow; if Layla borrows 30 books, her email is stored 30 times, and if it changes, 30 rows must change (or the data disagrees with itself). A better model separates the entities:

text
members(id, name, email, phone, joined_on, is_active)
books(id, title, isbn, published_year)
loans(id, loaned_on, due_on, returned_on)

Now loans describes only a loan: a date it started, a date it is due, and a date it was returned (NULL until then). The loan still needs to say which member and which book, but without copying their details. Linking tables is the job of foreign keys, which we reach in lesson L03. For now notice the design move: when columns describe a different thing, give that thing its own table.

Backend Insight

In a Java backend, a table usually corresponds to one kind of object in your code. Later in the course you will write classes such as Book and Member whose fields match the columns of the tables, and the types should match too: a date column maps naturally to a date type in Java, a boolean column to boolean, an integer to int or long, and a text column to String. Choosing the right column type now means the code that reads and writes it stays simple.

Changing a schema once an application is live is possible but costly: data must be migrated and every service that uses the table must change. A small mistake in a type (for example, money stored as an imprecise number) can silently corrupt results.

Common Mistakes

Mistake 1: Putting several kinds of things in one table

  • Incorrect: one loans table with member name, member email, and book title in every row.
  • Correct: separate members, books, and loans tables.

Why it happens: a spreadsheet encourages one big sheet. Test each table with "one noun per row".

Mistake 2: Storing a list in one column

  • Incorrect: a column authors containing Herbert, Smith.
  • Correct: one value per cell; model many authors with more rows or another table (lessons L04 and L05).

A cell holds a single value. Lists cannot be searched, counted, or constrained reliably.

Mistake 3: Choosing text for everything

  • Incorrect: joined_on as text with values like 1 Sept 24, 2024/09/01, and last month.
  • Correct: joined_on as a date, so the database rejects values that are not dates and you can sort and compare them.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. In your own words, define entity, table, row, and column, using a members table as the example.

أظهر الحل A1

A1. An entity is a kind of thing the system remembers (a member). A table is the named grid that stores that entity (members). A row is one instance (one specific member, Layla). A column is one fact stored for every instance (email).

A2. Choose a type (text, integer, decimal, date, boolean) for each fact: a book's page count; a book's price; the day a member joined; whether a book is currently available; a member's phone number.

أظهر الحل A2

A2. Page count: integer. Price: decimal (exact). Day a member joined: date. Currently available: boolean. Phone number: text, since it can contain a leading zero or a plus sign and is never used in arithmetic.

A3. Which of these is a better name for a column holding the day a loan is returned: ReturnDate, returned_on, or date of return? Why?

أظهر الحل A3

A3. returned_on: lowercase with underscores (no quoting needed), and the _on suffix signals a date. ReturnDate mixes capital letters and date of return contains spaces, which force quoting in SQL.

Level B: Application

B1. A school wants to record its teachers. Write a table in our notation with at least five columns, giving a type for each and marking which are required.

أظهر الحل B1

B1. One possible answer:

text
teachers
  id          integer
  name        text      required
  email       text      required
  hired_on    date      required
  subject     text
  is_active   boolean   required

Any sensible table with consistent names and types is fine. Check that optional facts are not marked required.

B2. This table is meant to hold orders for an online shop: orders(id, customer_name, customer_address, product_name, product_price, quantity, ordered_on). Which kinds of things are mixed in each row? Which separate tables would you propose (names and columns, no keys yet)?

أظهر الحل B2

B2. Each row mixes a customer (name, address), a product (name, price), and an order (quantity, date). Proposal: customers(name, address), products(name, price), orders(quantity, ordered_on). Customer details would otherwise repeat on every order, and a product's price would be copied into every order row. (Linking the tables comes in lesson L03.)

B3. A column price in a products table currently has the type "ordinary floating-point number". Explain why you would change it, and to what.

أظهر الحل B3

B3. Ordinary floating-point types store many decimal fractions only approximately, so sums of prices can be off by tiny amounts. Use an exact decimal type, which keeps values such as 12.50 precise.

Level C: Challenge

C1. Model a small gym. The gym has people who sign up, classes that are offered at certain times, and rooms. Choose the entities, give each a table with columns and types, and state for each table the sentence "each row is one ...". Do not link the tables yet.

أظهر الحل C1

C1. One possible answer:

text
members(id, name, email, joined_on)        each row is one person who signed up
classes(id, title, starts_at, capacity)    each row is one class offering
rooms(id, name, capacity)                  each row is one room

Types: ids integer; name, email, title text; joined_on date; starts_at timestamp; capacity integer. Other designs are fine if every table passes the one-noun test.

C2. A teammate proposes one table library(id, kind, name, detail) where kind is book or member and detail holds either an ISBN or an email. List at least three problems with this design.

أظهر الحل C2

C2. Examples: the detail column holds two kinds of facts, so it cannot have one type; a date or a boolean for members has nowhere to live; the database cannot check that an ISBN looks like an ISBN or that an email looks like an email; queries must always filter by kind first; columns specific to one kind (such as published_year or joined_on) have no home. Separate books and members tables fix all of these.

Mini Challenge

Pick a small system you know: a bike-rental stand, a school canteen, or a clinic. List its entities. Choose one entity and write its table in our notation with at least six columns, a type for each, and a mark for required columns. Then write one sentence saying what each row represents, and give two sample rows, including one with a NULL in an optional column.

Check your work: names follow the convention, no column holds a list, and the table passes the "one noun per row" test.

Summary & Next Lesson

Key takeaways

  • An entity (a kind of thing) becomes a table; each instance is a row; each fact is a column with a type.
  • One table describes one kind of thing. If rows describe two things at once, split them.
  • Choose types by behavior: dates for dates, exact decimals for money, text for identifiers you never calculate with, booleans for yes or no.
  • NULL means "no value"; decide which columns are required.
  • Use lowercase names with underscores and be consistent.

Next lesson

We have tables, but nothing yet says how to tell one row from another when two members share a name. The next lesson, "Primary keys", gives each row a unique, stable identity.

Self-check

  1. What does one row of a books table represent?
  2. Why is a phone number stored as text rather than an integer?
  3. Which type suits "the day a member joined"?
  4. What does NULL mean?
  5. A loans table also stores the member's name and email in every row. What is wrong?
أظهر الإجابات
  1. One book (one instance of the entity).
  2. It is an identifier, not something you calculate with, and it may start with 0 or contain a plus sign, which an integer would lose.
  3. A date.
  4. "No value": the fact is unknown or not applicable. It is not zero and not an empty text.
  5. The table mixes two kinds of things; the member's details repeat on every loan and can become inconsistent. Members should have their own table.

References

  • PostgreSQL documentation, Data Types. Lists integer, numeric (also called decimal), text, date, timestamp, and boolean among the built-in types; other systems name types differently.
  • PostgreSQL documentation, Numeric Types. States that numeric is exact and recommended for monetary amounts, while floating-point types are inexact.
  • Microsoft Learn, Database normalization description. Supports the idea that each set of related data belongs in its own table and that redundancy causes maintenance problems.
  • PostgreSQL documentation, Constraints. Documents NOT NULL and the key rules used in later lessons. The Java type mapping and naming convention are course conventions, not taken from these pages.

التطبيق

طبّق هذا الدرس بمختبر الوحدة وتمارينها واختبارها.

المختبر والتمارين الاختبار

تعليم الدرس كمكتمل اختياري ويمكنك التراجع عنه في أي وقت. يبقى تقدمك محفوظًا في هذا المتصفح.

تابع إلى الدرس التالي

اكتب للبحث في المستويات والمراحل والوحدات والدروس.