mardi 7 avril 2020

Design Pattern: In what manner to structure tables in DB to: Restrict access to articles on a website to only subscribed users?

This is my 1st question on stackoverflow. So please forgive if it doesn't meet the standards:

DATABASE USED: PostgreSQL v11.2

I'm creating a react.js app to show show articles to signed-in users. These users can be either:
(i)
those who have subscribed to one of the subscription plans, or,
(ii) simply signed-in and surfing through but not subscribed yet.
(THIS PART IS MANAGED - NO PROBLEM HERE)

An article can belong to different "categories".
(THIS PART IS MANAGED - NO PROBLEM HERE)

Now, for subscribed users, certain 'categories' of articles are to be shown, which normal users can't see.

I want to create a database structure that will handle this visibility based on whether the user is subscribed or not.

  • USER table columns (that matter):
    id, name, is_subscribed (Boolean)

  • CATEGORY table columns (that matter):
    id, cat_name

  • ARTICLE table columns (that matter):
    id, title

  • ARTICLE_CATEGORY table columns:
    id, article_id, category_id

  • SUBSCRIPTION table columns:
    id, plan_name, price, decsription

How to go ahead with it?
I haven't built the structure yet to implement this.

WHAT SHOULD BE A BETTER APPROACH?

(1) Should I do it at the category/article level?
For ex.: "ARTICLE_CATEGORY" and "ARTICLE" tables will contain a Boolean column "subscribers_only".

OR

(2) Should I maintain a separate table linking my subscription plans with categories/articles?

My model for USER looks like this:

const User = sequelize.define("user", {
    full_name: { type: DataTypes.STRING },
    email: { type: DataTypes.STRING },
    password: { type: DataTypes.STRING },
    email_verify_token: { type: DataTypes.STRING },
    is_email_verified: { type: DataTypes.BOOLEAN },
    is_subscribed: { type: DataTypes.BOOLEAN },
    ... <many more columns>
  });


My model for SUBSCRIPTION_PLAN looks like this:

const SubscriptionPlan = sequelize.define("subscription_plan", {
    plan_name: { type: DataTypes.INTEGER },
    price: { type: DataTypes.INTEGER }
    ... <many more columns>
  });


My model for SUBSCRIBERS looks like this:

const Subscriber = sequelize.define("subscriber", {
    user_id: { type: DataTypes.INTEGER },
    subscription_id: { type: DataTypes.INTEGER }
    ... <many more columns>
  });


My model for ARICLE looks like this:

const Article = sequelize.define("article", {
    title: { type: DataTypes.STRING },
    url: { type: DataTypes.STRING },
    display_pic: { type: DataTypes.STRING },
    ... <many more columns>
  });


My model for ARICLE_CATEGORY looks like this:

const ArticleCategory = sequelize.define("article_catgeory", {
    article_id: { type: DataTypes.INTEGER },
    category_id: { type: DataTypes.INTEGER }
    ... <many more columns>
  });

Aucun commentaire:

Enregistrer un commentaire