Improve data model

[dsebastien]

Tag system: https://davelevy.info/implementing-tags-in-a-database/

User profiles

• Tags (Content Create, Expert, Coach, etc)
• Resources (links to resources)
• Picture: #109

Users

• Role (user, knowii admin, etc)
• Followed tags (links to tags)
• Settings
  • Email preferences (e.g., receive weekly digest)
  • Notification preferences (e.g., notify when new relevant content is added, notify when new relevant job listings are added)
  • Wall configuration

Community books, sections and pages

Communities

• Settings (billing, etc)

• User Summaries

  • Text
  • Link
  • Files
  • Author (link to a user profile)
  • Link with a resource

• Conversations

  • Link with to a person
  • Quotes
  • Text
  • Author (link to person)

• Organizations

  • Name
  • Description
  • Size
  • Year founded
  • Job listing(s)
  • Resources
  • Tags

Files:

• file size
• file format

Audio

• duration
• transcription

Scientific articles

• doi
• ...

Books

• isbn10
• isbn13
• pages
• issn

Videos

• duration

Podcasts

• spotify_link
• apple_podcasts_link
• transistor_link
• last_fetched_at

Podcast episodes

• duration
• episode number
• audio_url
• speakers
• timestamps for each speaker
• text for each speaker
• analyzed (bool)

Newsletters

• last_fetched_at

Newsletter editions

• issue_number

X tweets

• ...

X threads

• tweet_count

Quotes

• ...

RSS feeds

• last_fetched_at

Job Listings

• Title
• Description
• Location
• Date added
• Tags
• Added by
• Link to apply
• Organization

[dsebastien]

Schema::create('user_resource_interactions', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();

  // If the user is deleted, then this is deleted as well
  $table->foreignId('user_id')->nullable()->constrained()->cascadeOnDelete();

  // If the global resource is deleted, then this is deleted as well
  $table->foreignId('resource_id')->constrained()->cascadeOnDelete();

  $table->boolean('hidden')->default(false);
  $table->boolean('explored')->default(false);
  $table->boolean('starred')->default(false);
  $table->boolean('upvoted')->default(false);
  $table->boolean('downvoted')->default(false);
  $table->boolean('reported')->default(false);
  $table->timestamps();
  $table->unique(['user_id', 'resource_id']);
});

Schema::dropIfExists('user_resource_interactions');

[dsebastien]

WARNING: the schema must be reviewed to take into account the fact that different communities might tag the same resources differently. A separation might be required between local and global tags.

Schema::create('tags', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();
  $table->string('name')->unique();
  $table->string('slug')->unique()->index();
  $table->timestamps();
});

Schema::dropIfExists('tags');

Schema::create('resource_tag', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();

  // if the global resource is deleted, then this is deleted as well
  $table->foreignId('resource_id')->constrained()->cascadeOnDelete();

  // If the global tag is deleted, then this is deleted as well
  $table->foreignId('tag_id')->constrained()->cascadeOnDelete();

  $table->timestamps();
  $table->unique(['resource_id', 'tag_id']);
});

Schema::dropIfExists('resource_tag');

Related AI chat:

- Define a set of shared tags (shared across all communities): general topics/themes that are relevant to as many communities as possible (e.g., beginner, intermediate, advanced, expert)
- Define community-specific tags: tags that communities create for themselves. Can be as narrow/specific as they want (e.g., productivity hacks for a productivity-focused community)
- Allow setting multiple tags to each resource to classify things in multiple ways
- Provide autocomplete suggestions (for each tag: usages in this community, usages on the platform). Provide visual indication to distinguish between platform-wide tags and community-specific ones
- Create a hierarchical structure for the tags. Broader categories at the top and specific subcategories (e.g., marketing vs social media marketing, content marketing, email marketing, etc)  
- Make tags searchable: Make sure that users can easily search for resources by tag, whether they are searching for shared tags or community-specific tags.
- By creating a tag system with shared and community-specific tags, users can easily classify and find resources based on their relevance to both the broader topic and the specific community.

...
CREATE TABLE communities (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE tags (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  is_shared BOOLEAN NOT NULL DEFAULT false,
  community_id INT,
  FOREIGN KEY (community_id) REFERENCES communities(id)
);

CREATE TABLE resources (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  description TEXT,
  content TEXT,
  source VARCHAR(255),
  publication_date TIMESTAMP,
  community_id INT,
  FOREIGN KEY (community_id) REFERENCES communities(id)
);

CREATE TABLE resource_tags (
  id INT PRIMARY KEY,
  resource_id INT,
  tag_id INT,
  timestamp TIMESTAMP,
  user_id INT,
  relevance_score FLOAT,
  FOREIGN KEY (resource_id) REFERENCES resources(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);
...

The communities table represents the different communities in the system, while the tags table represents the tags themselves. The tags table includes a boolean field is_shared to indicate whether a tag is shared across all communities or specific to a particular community, and a foreign key reference to the communities table to indicate which community a community-specific tag belongs to.

The resources table represents the resources in the system and includes fields for the title, description, content, source, publication date, and community ID (a foreign key reference to the communities table).

Finally, the resource_tags table is a many-to-many join table between resources and tags, allowing for multiple tags to be associated with each resource.

This schema can be customized further based on specific needs and use cases, but it provides a basic framework for representing a tag system with shared and community-specific tags in SQL.

Alternative:

...
model Community {
  id              Int       @id @default(autoincrement())
  communityTags   CommunityTag[]
}

// Define the Resource model
model Resource {
  ...
  globalTags      GlobalTag[]
  communityTags   CommunityTag[]
}

// Define the GlobalTag model
model GlobalTag {
  id              Int       @id @default(autoincrement())
  name            String
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  resources       Resource[]
}

// Define the CommunityTag model
model CommunityTag {
  id              Int       @id @default(autoincrement())
  name            String
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  community       Community
  resources       Resource[]
}
...

[dsebastien]

Schema::create('keywords', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();
  $table->string('name')->unique();
  $table->string('slug')->unique()->index();
  $table->timestamps();
});

Schema::dropIfExists('keywords');

Schema::create('resource_keyword', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();

  // If the global resource is deleted, then this is deleted as well
  $table->foreignId('resource_id')->constrained()->cascadeOnDelete();

  // If the global keyword is deleted, then this is deleted as well
  $table->foreignId('keyword_id')->constrained()->cascadeOnDelete();

  $table->timestamps();
  $table->unique(['resource_id', 'keyword_id']);
});

Schema::dropIfExists('resource_keyword');

[dsebastien]

Schema::create('categories', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();
  $table->string('name')->unique();
  $table->string('slug')->unique();
  $table->text('description')->nullable();
  $table->foreignId('parent_id')->nullable()->constrained('categories')->nullOnDelete();
  $table->timestamps();
});

Schema::dropIfExists('categories');

Schema::create('resource_category', function (Blueprint $table) {
  $table->id();
  $table->string('cuid')->unique()->index();

  // If the global resource is deleted, then this is deleted as well
  $table->foreignId('resource_id')->constrained()->cascadeOnDelete();

  // If the global category is deleted, then this is deleted as well
  $table->foreignId('category_id')->constrained()->cascadeOnDelete();
  $table->timestamps();
  $table->unique(['resource_id', 'category_id']);
});

Schema::dropIfExists('resource_category');

[dsebastien]

Schema::create('resource_user_profile', static function (Blueprint $table) {
      $table->id();
      $table->string('cuid')->unique()->index();

      // If the global resource is deleted, then this is deleted as well
      $table->foreignId('resource_id')->constrained()->cascadeOnDelete();

      // If the user profile is deleted, then this is deleted as well
      $table->foreignId('user_profile_id')->constrained('user_profiles')->cascadeOnDelete();

      $table->boolean('is_curator')->default(false)->index();
      $table->boolean('is_author')->default(false)->index();
      $table->boolean('is_contributor')->default(false)->index();
      $table->boolean('is_editor')->default(false)->index();
      $table->boolean('is_reviewer')->default(false)->index();
      $table->boolean('is_creator')->default(false)->index();

      $table->timestamps();
      $table->unique(['resource_id', 'user_profile_id']);
    });

    Schema::dropIfExists('resource_user_profile');

[dsebastien]

Voting system

id cuid user profile id resourceId Unique pair CreatedAt UpdatedAt