A database schema for a Tinder-clone dating app using either MySQL, SQLite or PostgreSQL as primary database

mid_budget_online_dating_app_design_database.sql

-- MySQL v8.0.17
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci;  -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;
SET GLOBAL time_zone = '+00:00'; -- I don't want MySQL to convert to current database session timezone

CREATE TABLE user_details (
    id bigint NOT NULL,
    email varchar(90) UNIQUE NOT NULL,
    password_hash varchar(80) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE user_accounts (
    id bigint NOT NULL, 
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    bio text,
    date_of_birth date NOT NULL,
    gender enum('male', 'female', 'non-binary') NOT NULL,
    location_country char(2),
    is_active tinyint(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- : Store links to user photos
CREATE TABLE user_photos (
    id bigint NOT NULL,
    user_id bigint NOT NULL ,
    photo_url varchar(255) NOT NULL,
    is_profile_pic tinyint(1) NOT NULL DEFAULT 0,
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- : Master list of all possible interests
CREATE TABLE user_interests (
    id bigint NOT NULL,
    interest_name varchar(50) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

-- : Many-to-many relationship between users and interests
CREATE TABLE user_with_interests (
    user_id bigint NOT NULL ,
    interest_id bigint NOT NULL,
    PRIMARY KEY (user_id, interest_id),
    FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (interest_id) REFERENCES user_interests(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- : Track user interactions (likes/dislikes)
CREATE TABLE swipes (
    id bigint NOT NULL,
    swiper_id bigint NOT NULL,
    swipee_id bigint NOT NULL,
    swipe_type enum('like', 'dislike') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (swiper_id, swipee_id), -- Ensures a user can only swipe on another user once
    PRIMARY KEY (id),
    FOREIGN KEY (swiper_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (swipee_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- : Store successful matches (both users swiped 'like')
CREATE TABLE matches (
    id bigint NOT NULL,
    matcher_user_id bigint NOT NULL,
    matchee_user_id bigint NOT NULL,
  	match_hash VARCHAR(100) GENERATED ALWAYS AS (
        CONCAT(
            LEAST(matcher_user_id, matchee_user_id), 
            '-', 
            GREATEST(matcher_user_id, matchee_user_id)
        )
    ) STORED UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Enforces that a match only exists once, regardless of user order
    PRIMARY KEY (id),
    FOREIGN KEY (matcher_user_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
    FOREIGN KEY (matchee_user_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
  CONSTRAINT unq_match_pair CHECK (matcher_user_id != matchee_user_id)
);

-- : In-app messaging
CREATE TABLE messages (
    id bigint NOT NULL,
    match_id bigint NOT NULL,
    sender_id bigint NOT NULL ,
    recipient_id bigint NOT NULL,
    message text NOT NULL,
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_read tinyint(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
    FOREIGN KEY (sender_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
    FOREIGN KEY (recipient_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE RESTRICT
);









INSERT INTO user_details (id, email, password_hash, created_at, last_login) VALUES
(101, 'alex.jones@gmail.com', '$2y$12$e0MYzXyZ...hashforalex', '2026-01-15 08:30:00', '2026-06-07 14:22:18'),
(102, 'sarah.smith@yahoo.com', '$2y$12$k8NxYwZ...hashforsarah', '2026-01-18 11:15:00', '2026-06-07 22:45:10'),
(103, 'david.bekham@yahooo.com', '$2y$12$p9LxQwX...hashfordavid', '2026-02-01 09:00:00', '2026-06-06 18:12:45'),
(104, 'elena.rodriguez@gmail.com', '$2y$12$m2WzRxP...hashforelena', '2026-02-14 16:45:00', '2026-06-07 23:11:02');

INSERT INTO user_accounts (id, first_name, last_name, bio, date_of_birth, gender, location_country, is_active) VALUES
(101, 'Alex', 'Jones', 'Software engineer by day, amateur chef by night.', '1994-05-12', 'male', 'US', 1),
(102, 'Sarah', 'Smith', 'Travel enthusiast and coffee connoisseur. Let us explore!', '1996-09-23', 'female', 'CA', 1),
(103, 'David', 'Bekham', 'Fitness instructor, football lover, and dog dad.', '1992-11-04', 'male', 'UK', 1),
(104, 'Elena', 'Rodriguez', 'Artist and bookworm. Always looking for new galleries.', '1995-07-19', 'female', 'ES', 1);

INSERT INTO user_photos (id, user_id, photo_url, is_profile_pic, uploaded_at) VALUES
(1, 101, 'https://cdn.example.com/photos/alex_main.jpg', 1, '2026-01-15 08:45:00'),
(2, 101, 'https://cdn.example.com/photos/alex_cooking.jpg', 0, '2026-01-20 19:30:00'),
(3, 102, 'https://cdn.example.com/photos/sarah_main.jpg', 1, '2026-01-18 11:30:00'),
(4, 103, 'https://cdn.example.com/photos/david_main.jpg', 1, '2026-02-01 09:15:00'),
(5, 104, 'https://cdn.example.com/photos/elena_main.jpg', 1, '2026-02-14 17:00:00');

INSERT INTO user_interests (id, interest_name) VALUES
(1, 'Cooking'),
(2, 'Hiking'),
(3, 'Photography'),
(4, 'Football'),
(5, 'Fine Arts'),
(6, 'Coding');

INSERT INTO user_with_interests (user_id, interest_id) VALUES
(101, 1), -- Alex loves Cooking
(101, 6), -- Alex loves Coding
(102, 1), -- Sarah also loves Cooking
(102, 2), -- Sarah loves Hiking
(103, 2), -- David loves Hiking
(103, 4), -- David loves Football
(104, 3), -- Elena loves Photography
(104, 5); -- Elena loves Fine Arts

INSERT INTO swipes (id, swiper_id, swipee_id, swipe_type, created_at) VALUES
(501, 101, 102, 'like', '2026-06-01 10:00:00'),    -- Alex likes Sarah
(502, 102, 101, 'like', '2026-06-01 12:30:00'),    -- Sarah likes Alex back (Creates Match)
(503, 101, 104, 'dislike', '2026-06-02 14:00:00'), -- Alex dislikes Elena
(504, 103, 102, 'like', '2026-06-03 09:15:00'),    -- David likes Sarah
(505, 102, 103, 'dislike', '2026-06-03 18:22:00'), -- Sarah dislikes David
(506, 103, 104, 'like', '2026-06-04 20:05:00'),    -- David likes Elena
(507, 104, 103, 'like', '2026-06-04 21:40:00');    -- Elena likes David back (Creates Match)

-- Note: 'match_hash' is skipped here because it is a VIRTUAL GENERATED ALWAYS column in your schema. MySQL builds it automatically on insertion.
INSERT INTO matches (id, matcher_user_id, matchee_user_id, created_at) VALUES
(1001, 101, 102, '2026-06-01 12:30:00'), -- Match between Alex and Sarah
(1002, 103, 104, '2026-06-04 21:40:00'); -- Match between David and Elena

INSERT INTO messages (id, match_id, sender_id, recipient_id, message, sent_at, is_read) VALUES
(9001, 1001, 101, 102, 'Hey Sarah! Loved your bio. Where is the best coffee spot you have found?', '2026-06-01 13:00:00', 1),
(9002, 1001, 102, 101, 'Hi Alex! Definitely Coffee Central downtown. We should grab a cup sometime!', '2026-06-01 13:15:00', 1),
(9003, 1001, 101, 102, 'Sounds like a plan! Are you free this Thursday evening?', '2026-06-01 13:20:00', 0),
(9004, 1002, 103, 104, 'Hey Elena, your art photography looks amazing!', '2026-06-05 08:00:00', 1);

QUERIES

Show one non-profile photo of any users that have not been swiped on ever by a user with id = 2

SELECT photo_url FROM user_photos 
WHERE user_id NOT IN (SELECT swipee_id FROM swipes WHERE swiper_id = 2)
AND is_profile_pic = 0 
AND user_id <> 2
ORDER BY uploaded_at DESC
LIMIT 10

SQL Fiddle: https://www.db-fiddle.com/f/bnVp19L3kTczrSFgji4sbR/5