Understanding Relationships in Databases – One-to-One, One-to-Many, Many-to-Many

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
    );

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
    );

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
   );

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

What Is a Relationship in a Database?

In relational databases, a relationship defines how data in one table is connected to data in another. These connections are made using primary keys and foreign keys and are essential for maintaining data integrity, eliminating redundancy, and enabling complex data queries.

Types of Relationships in SQL

There are three main types of relationships:

1. One-to-One (1:1)

Each record in Table A relates to one and only one record in Table B — and vice versa.

Example:

A person has one passport.

Each passport is linked to one person.

Schema:

CREATE TABLE persons (
  person_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE passports (
  passport_id INT PRIMARY KEY,
  person_id INT UNIQUE,
  FOREIGN KEY (person_id) REFERENCES persons(person_id)
);

UNIQUE ensures one-to-one relationship.

2. One-to-Many (1:N)

A record in Table A can be associated with many records in Table B, but a record in Table B refers to only one in Table A.

Example:

One customer can place many orders.

Each order belongs to one customer.

Schema:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Most common relationship in relational databases.

3. Many-to-Many (M:N)

Each record in Table A can relate to many records in Table B, and vice versa. This requires a junction table (also called a join table) to implement.

Example:

Students can enroll in many courses.

Each course can have many students.

Schema:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

The student_courses table links students and courses with a composite primary key.

Why Relationships Matter

• They normalize data and reduce redundancy.
• Make queries more accurate and efficient.
• Enforce referential integrity using foreign keys.
• Allow real-world modeling of complex systems (e.g., orders, users, products).

Relationship Best Practices

• Always define foreign keys to maintain referential integrity.
• Use indexes on foreign keys to improve join performance.
• Choose the correct relationship type based on the data model.