Home

ER Diagram and Relational Model

Address

• Store the addresses of the users (Will also be used in Filter Page).

User

• Store the information of the users.

Pet

• Store the information about the pets of a particular user.
• Age is a derived attribute since it will be calculated using birth_datetime.
• Is_profile_img indicates that this image is the profile image of the specific pet.
• One pet can have many images.
• Number_of_like will be used on the Ranking page.

Breed

• Store the breeds of the pets (Will also be used in Filter page).

Block_List

• Store the transactions of blockings between two pets.

Request_List

• Store the transactions of requests between two pets.

Status

• Store the status of the requests/blocks

Session

• Act as a chat room between two pets.
• Will be automatically generated once the pets accept their requests.

Message

• Store the messages in a particular session.
• is_system_message indicates that this message is auto-generated.

Relational Model (For the illustration purpose)

Normalization

Address Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• address_id -> name

User Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• user_id -> address_id, username, password, phone_number

PetImage Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• Image_id -> pet_id, image_url, is_profile
• Breed Table
• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• breed_id -> name

Pet Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• pet_id -> user_id, breed_id, name, gender, birth_datetime, description, number_of_like, is_current

BlockList Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• (pet_id, block_pet_id) is secondary key
• block_list_id -> pet_id, blocked_pet_id, block_datetime
• pet_id, block_pet_id -> block_datetime

RequestList Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• (pet_id, requested_pet_id) is secondary key.
• request_id -> pet_id, requested_pet_id, request_datetime, status_id

Session Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• session -> request_id, create_datetime

Message Table

• BCNF already (since A relation is in BCNF if and only if every determinant is a candidate key)
• send_datetime is secondary key.
• message_id -> sender_pet_id, session_id, send_datetime, is_unsent, is_system_message, unsent_datetime, message
• send_datetime -> sender_pet_id, session_id, is_unsent, is_system_message, unsent_datetime, message