We've moved discussions to Discord

Join Discord

Database design of a subscription based app

Ivor Padilla •
Hi, I'm looking for advice on how to model a DB for a feature based app. e.g. A store has one subscription to one of the 3 different plans (free, starter, growth) and each plan has a set of features. e.g. starter plan allows the submission of 300 emails, whilst growth permits 500. What would be an efficient way to model this?

Here's more or less what I have in mind, any thoughts?

The current problem I'm facing with this is that a feature can be either a bool, varchar or int and I don't want to create a field that can take any type.

https://dbdiagram.io/d/5f350ebce1246d54aa2d34f1

CREATE TABLE "store" (
  "id" int PRIMARY KEY,
  "host" varchar,
  "name" varchar NOT NULL,
  "description" varchar
);

CREATE TABLE "plans" (
  "id" int PRIMARY KEY,
  "name" varchar NOT NULL,
  "price" int NOT NULL,
  "feature_id" int
);

CREATE TABLE "features" (
  "id" [pk],
  "name" varchar NOT NULL,
  "value" varchar NOT NULL
);

CREATE TABLE "subscriptions" (
  "id" int PRIMARY KEY,
  "plan_id" int,
  "store_id" int
);

CREATE TABLE "subscriptions_plans" (
  "id" int PRIMARY KEY,
  "plan_id" int,
  "subscription_id" int
);

ALTER TABLE "features" ADD FOREIGN KEY ("id") REFERENCES "plans" ("feature_id");

ALTER TABLE "subscriptions" ADD FOREIGN KEY ("plan_id") REFERENCES "plans" ("id");

ALTER TABLE "subscriptions" ADD FOREIGN KEY ("store_id") REFERENCES "store" ("id");

ALTER TABLE "subscriptions_plans" ADD FOREIGN KEY ("plan_id") REFERENCES "plans" ("id");

ALTER TABLE "subscriptions_plans" ADD FOREIGN KEY ("subscription_id") REFERENCES "subscriptions" ("id");

My Discussions Participating All Discussions
Notifications
You’re not receiving notifications from this thread.