[Database Setup] Schema Design

Objective

Design the database schemas for core entities in the application, such as articles, users, categories, tags, comments, and subscriptions. These schemas will define the structure of the data stored in the database, relationships between entities, and ensure efficient data retrieval.

1. Article Schema

The Article schema stores information about articles including title, content, category, tags, publication date, status, and related fields.

Schema:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const articleSchema = new Schema({
  title: {
    type: String,
    required: true,
    trim: true,
  },
  content: {
    type: String,
    required: true,
  },
  summary: {
    type: String,
    required: true,
  },
  category: {
    type: Schema.Types.ObjectId,
    ref: 'Category',
    required: true,
  },
  tags: [{
    type: Schema.Types.ObjectId,
    ref: 'Tag',
  }],
  status: {
    type: String,
    enum: ['draft', 'published', 'rejected'],
    default: 'draft',
  },
  publishDate: {
    type: Date,
    default: null,
  },
  author: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true,
  },
  image: {
    type: String, // URL or path to the image
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
  updatedAt: {
    type: Date,
    default: Date.now,
  },
});

module.exports = mongoose.model('Article', articleSchema);

2. User Schema

The User schema stores information about the users, including their roles (e.g., writer, editor, subscriber), authentication details, and personal information.

Schema:

const mongoose = require('mongoose');
const bcrypt = require('bcrypt');
const Schema = mongoose.Schema;

const userSchema = new Schema({
  firstName: {
    type: String,
    required: true,
    trim: true,
  },
  lastName: {
    type: String,
    required: true,
    trim: true,
  },
  email: {
    type: String,
    required: true,
    unique: true,
    lowercase: true,
  },
  password: {
    type: String,
    required: true,
  },
  role: {
    type: String,
    enum: ['writer', 'editor', 'subscriber'],
    default: 'subscriber',
  },
  status: {
    type: String,
    enum: ['active', 'inactive'],
    default: 'active',
  },
  profileImage: {
    type: String, // URL or path to the profile image
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
  updatedAt: {
    type: Date,
    default: Date.now,
  },
});

// Encrypt password before saving
userSchema.pre('save', async function (next) {
  if (!this.isModified('password')) return next();
  this.password = await bcrypt.hash(this.password, 10);
  next();
});

module.exports = mongoose.model('User', userSchema);

3. Category Schema

The Category schema stores information about different article categories.

Schema:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const categorySchema = new Schema({
  name: {
    type: String,
    required: true,
    unique: true,
    trim: true,
  },
  description: {
    type: String,
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
  updatedAt: {
    type: Date,
    default: Date.now,
  },
});

module.exports = mongoose.model('Category', categorySchema);

4. Tag Schema

The Tag schema stores information about tags that can be associated with articles.

Schema:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const tagSchema = new Schema({
  name: {
    type: String,
    required: true,
    unique: true,
    trim: true,
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
  updatedAt: {
    type: Date,
    default: Date.now,
  },
});

module.exports = mongoose.model('Tag', tagSchema);

5. Comment Schema

The Comment schema stores user comments on articles, including comment content, author, and related article.

Schema:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const commentSchema = new Schema({
  content: {
    type: String,
    required: true,
  },
  article: {
    type: Schema.Types.ObjectId,
    ref: 'Article',
    required: true,
  },
  author: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true,
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
  updatedAt: {
    type: Date,
    default: Date.now,
  },
});

module.exports = mongoose.model('Comment', commentSchema);

6. Subscription Schema

The Subscription schema stores information about subscribers, including subscription type, expiration date, and payment details.

Schema:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const subscriptionSchema = new Schema({
  user: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true,
  },
  plan: {
    type: String,
    enum: ['free', 'premium'],
    default: 'free',
  },
  startDate: {
    type: Date,
    default: Date.now,
  },
  endDate: {
    type: Date,
    required: true,
  },
  paymentDetails: {
    type: Schema.Types.Mixed, // Store payment details like transaction ID, amount, etc.
  },
  status: {
    type: String,
    enum: ['active', 'expired', 'cancelled'],
    default: 'active',
  },
});

module.exports = mongoose.model('Subscription', subscriptionSchema);

Relationships and Indexing

Articles and Categories: Articles belong to a category, so the category field in the Article schema is a reference (ref) to the Category model.
Articles and Tags: Articles can have multiple tags, so the tags field in the Article schema is an array of references to the Tag model.
Articles and Comments: Each article can have multiple comments, so the article field in the Comment schema is a reference to the Article model.
Users and Comments: Each comment has an author, so the author field in the Comment schema is a reference to the User model.
Users and Subscriptions: Each user has one subscription, so the user field in the Subscription schema is a reference to the User model.

Indexes

For performance optimization, especially for searching, we can add indexes to frequently queried fields, such as email in the User schema, name in the Category and Tag schemas, and publishDate in the Article schema.

Example:

// Add index to email in User schema for quick lookups
userSchema.index({ email: 1 });

// Add index to publishDate in Article schema for fast sorting
articleSchema.index({ publishDate: -1 });

Deliverables

Article Schema: For storing article data with relationships to categories, tags, and users.
User Schema: For storing user data with encryption for passwords and role management.
Category Schema: For managing article categories.
Tag Schema: For managing article tags.
Comment Schema: For storing comments on articles.
Subscription Schema: For managing subscriptions, including premium status.

Acceptance Criteria

Database schemas are defined and connected via Mongoose in Node.js.
Relationships between entities (Articles, Categories, Tags, Users, Comments, Subscriptions) are clearly established using references.
All schemas include proper validation and default values.
Indexes are applied to optimize search and sorting operations.

nban22 / Newspaper