MySQL Table Relationships: One-to-One, One-to-Many, and Many-to-Many

Relationships express how records are connected

Relational database design represents associations between rows using keys. Cardinality describes how many records on one side can relate to records on the other. The three common patterns are one-to-one, one-to-many, and many-to-many. Choosing the correct pattern affects constraints, joins, deletion behavior, and whether attributes belong to an entity or to its association. A clear relationship model reduces duplicated information and makes it possible for MySQL to reject references to nonexistent records.

One-to-one relationships

A one-to-one relationship means one row on one side corresponds to at most one row on the other. It can be modeled with a shared primary key or a foreign key that is also UNIQUE. Separating a profile, sensitive details, or an optional extension from a core user record may make lifecycle or access boundaries clearer. Splitting a table without a meaningful reason can add needless joins, so consider whether the data has different privacy, optionality, or ownership requirements.

CREATE TABLE user_profiles (
  user_id BIGINT UNSIGNED NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  biography TEXT NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_profiles_user
    FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE = InnoDB;

Because user_id is both the primary key and a foreign key, the table can hold at most one profile for each user. A user may have no profile row unless another workflow requires one. If both tables must always exist together, the application and transaction design must enforce creation; a foreign key alone does not require the child row to exist.

One-to-many relationships

In a one-to-many relationship, a parent may have many children, while each child belongs to one parent. A customer can place many orders, so the customer key belongs on the orders table as a foreign key. Putting a list of order IDs into a customer string column would prevent reliable constraints and make queries cumbersome. Foreign keys should use compatible types, and an index on the child reference supports joins and relationship checks.

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (order_id),
  KEY idx_orders_customer (customer_id),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE = InnoDB;

A join from customers to orders returns multiple rows for customers with multiple orders. That multiplicity is the expected shape. If a report needs one row per customer, group the orders or use an existence query rather than hiding repeated rows with DISTINCT.

Many-to-many relationships and junction tables

When each entity can relate to many records on the other side, introduce a junction table. Students can enroll in many courses and each course can have many students. The junction row represents one enrollment and can store facts about that relationship, such as enrollment time, status, or final grade. A composite primary key prevents duplicate pairs if each student can enroll only once in a course; if re-enrollment is allowed, include a term or use a separate enrollment identifier.

CREATE TABLE course_enrollments (
  student_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id),
  CONSTRAINT fk_enrollments_student
    FOREIGN KEY (student_id) REFERENCES students (student_id),
  CONSTRAINT fk_enrollments_course
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
) ENGINE = InnoDB;

Deletion, optionality, and lifecycle

Decide whether each relationship is mandatory and what should happen when a referenced row is removed. NOT NULL on the foreign key requires a child to have a parent; allowing NULL makes the association optional. RESTRICT prevents deletion of a parent with dependent rows, CASCADE removes dependent rows, and SET NULL requires a nullable key. Cascading may be appropriate for short-lived dependent data but dangerous for orders, audit records, or records under retention policies. Test these actions with realistic data.

Database constraints protect referential integrity, while application authorization determines who may create or see relationships. Do not rely on an unguessable ID to authorize access. Validate user permissions in the application and use least-privilege database accounts. See keys and constraints and security practices.

Choose the pattern from the domain

Draw a few sample records and state how many related records are valid on each side. Identify attributes that describe a relationship rather than either entity. Then choose primary keys, foreign keys, unique constraints, and indexes. Test zero, one, and many related rows and run representative joins. This practical check often reveals whether the chosen cardinality matches the way the application will query data.

Relationships make normalized data useful: keys preserve the connections and joins retrieve them. Continue with MySQL joins, normalization, and referential constraints.