Pawsitively Social¶

Try me¶

Problem definition¶

Design and draw a database model for a hypothetical social media platform called “Pawsitively Social”. The main feature of the platform is connecting pet owners and helping them find new friends for their pets. Your task is to design a database model that can store information about users, their pets, and the connections between them. The model should be able to support the following functionality: • Users can create a profile for themselves and for each of their pets. A user’s profile should include their name, age, location, and a profile picture. A pet’s profile should include their name, breed, age, and a profile picture. (You can use a VARCHAR type to store the location of the profile picture) • Users can search for other users and their pets based on various criteria, such as location, breed, and age. • Users can send and receive friend requests to and from other users. If a friend request is accepted, the two users are considered friends and their pets are also considered friends. • Users can post updates, photos, and videos to their profile and their pets’ profiles. These updates can be liked and commented on by other users. Some possible entities in this database model are: • User: Stores information about a user, such as their name, age, location, and profile picture. • Pet: Stores information about a pet, such as their name, breed, age, and profile picture. • Friendship: Stores information about a friendship between two users. • Update: Stores information about an update (e.g. a post, photo, or video) made by a user or pet. • Comment: Stores information about a comment made on an update made by a user. • Like: Stores information about a like on an update made by a user.

Solution¶

The diagram below shows a possible solution for Pawsitively Social:

The entities in the model are those listed in the problem definition. The main entities in the model are users and pets, which are related using a 1-N relationship (assuming that a user can create profiles for a pet, a user can have more than one pet). Then, the implementation is rather straightforward. The relationship table is just a junction table used to implement the (N-N) relationships among users. Users publish updates of pets, therefore there is 1-N relationship between users and updates (a user can create many updates, and many updates can refer to a pet), and finally, comments and likes are related to updates (1-N) relationships.

Try it yourself¶

The MySQL code snippet below implements the database in MySQL:

-- Create the pawsitively_social database
CREATE DATABASE pawsitively_social;

-- Use the pawsitively_social database
USE pawsitively_social;

-- Create the users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    location VARCHAR(255) NOT NULL,
    profile_picture VARCHAR(255) NOT NULL
);

-- Create the pets table
CREATE TABLE pets (
    pet_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    breed VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    profile_picture VARCHAR(255) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Create the friendship table
CREATE TABLE friendships (
    friendship_id INT AUTO_INCREMENT PRIMARY KEY,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    FOREIGN KEY (user1_id) REFERENCES users(user_id),
    FOREIGN KEY (user2_id) REFERENCES users(user_id)
);

-- Create the updates table
CREATE TABLE updates (
    update_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    pet_id INT,
    update_text TEXT NOT NULL,
    update_picture VARCHAR(255),
    update_video VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (pet_id) REFERENCES pets(pet_id)
);

-- Create the comments table
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    update_id INT NOT NULL,
    comment_text TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (update_id) REFERENCES updates(update_id)
);

-- Create the likes table
CREATE TABLE likes (
    like_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    update_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (update_id) REFERENCES updates(update_id)
);