Meme Database Schema

1. Users Table

Table Name: users
Primary Key: id
Description: This table stores user information, including their username, email, avatar URL, and various metrics related to their meme activity.
Relationships:

One-to-Many with Memes: Each user can create multiple memes. This is represented by the user_id foreign key in the memes table.

2. Memes Table

Table Name: memes
Primary Key: id
Description: This table stores the memes, including the image URL, caption, and metadata such as total score and view count.
Relationships:

Many-to-One with Users: Each meme is associated with one user (the creator). This is represented by the user_id foreign key that references the id in the users table.
Many-to-Many with Tags: Memes can have multiple tags, and tags can be associated with multiple memes. This relationship is managed through the meme_tags junction table.
One-to-Many with Votes: Each meme can receive multiple votes from users. This is represented by the meme_id foreign key in the votes table.
One-to-Many with Comments: Each meme can have multiple comments. This is represented by the meme_id foreign key in the comments table.
Many-to-Many with Collections: Memes can belong to multiple collections, and collections can contain multiple memes. This relationship is managed through the collection_memes junction table.

3. Tags Table

Table Name: tags
Primary Key: id
Description: This table stores tags that can be associated with memes for categorization.
Relationships:

Many-to-Many with Memes: Each tag can be associated with multiple memes, and each meme can have multiple tags. This relationship is managed through the meme_tags junction table.

4. Meme_Tags Junction Table

Table Name: meme_tags
Primary Key: Composite key of meme_id and tag_id
Description: This table serves as a junction table to manage the many-to-many relationship between memes and tags.
Relationships:

Many-to-One with Memes: Each entry in this table references a meme through the meme_id foreign key.
Many-to-One with Tags: Each entry in this table references a tag through the tag_id foreign key.

5. Votes Table

Table Name: votes
Primary Key: id
Description: This table stores votes for memes, allowing users to upvote or downvote memes.
Relationships:

Many-to-One with Memes: Each vote is associated with one meme through the meme_id foreign key.
Many-to-One with Users: Each vote is cast by one user through the user_id foreign key.

6. Comments Table

Table Name: comments
Primary Key: id
Description: This table stores comments made by users on memes.
Relationships:

Many-to-One with Memes: Each comment is associated with one meme through the meme_id foreign key.
Many-to-One with Users: Each comment is made by one user through the user_id foreign key.

7. Collections Table

Table Name: collections
Primary Key: id
Description: This table stores collections created by users to group memes.
Relationships:

Many-to-One with Users: Each collection is created by one user through the user_id foreign key.
Many-to-Many with Memes: Collections can contain multiple memes, and memes can belong to multiple collections. This relationship is managed through the collection_memes junction table.

8. Collection_Memes Junction Table

Table Name: collection_memes
Primary Key: Composite key of collection_id and meme_id
Description: This table serves as a junction table to manage the many-to-many relationship between collections and memes.
Relationships:

Many-to-One with Collections: Each entry in this table references a collection through the collection_id foreign key.
Many-to-One with Memes: Each entry in this table references a meme through the meme_id foreign key.

Summary of Relationships

Users can create multiple Memes.
Memes can have multiple Tags (via meme_tags).
Memes can receive multiple Votes and Comments.
Collections can contain multiple Memes (via collection_memes).
Tags can be associated with multiple Memes (via meme_tags).

7etsuo/description.md