0

I want to create a simple affiliate system that pays affiliates who refer a customer a commission of $100.

I currently have the following tables generated via an ORM.

CREATE TABLE `session` (
    `id` text PRIMARY KEY NOT NULL,
    `user_id` text NOT NULL,
    `expires_at` text NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `user` (
    `id` text PRIMARY KEY NOT NULL,
    `first_name` text NOT NULL,
    `last_name` text NOT NULL,
    `email` text NOT NULL,
    `password` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `session_userId_idx` ON `session` (`user_id`);--> statement-breakpoint
CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);--> statement-breakpoint
CREATE INDEX `user_idx` ON `user` (`id`);

I want my affiliates to have a few fields:

  1. affiliate_link - so they can use ?via=jack on my sites' URL to refer any customer & get commission. bdw, jack will be stored in the affiliate_link.
  2. paypal_email - this is their way of getting paid via paypal. i send these manually twice a month.
  3. referred_users - this should be a list of emails referred by that specific affiliate like ["[email protected]", "[email protected]"]. now, i'm confused whether to store it as json(array) orjsonb(array but on file) or use yet another table like i storedsessionsof auser`. i think the 3rd & final (another table) solution is more efficient.
  4. cashed_out - this should track their cashout date (like Dec 23 at 2 pm or timestamp) & payment (like $500) that they cashed out. i have similar question to this like #3 above. i'm thinking table is better here too as i want to keep track of all their past cashouts. otherwise i would've used 2 fields.

Now, instead of putting more values like paypal_email in user, i'm thinking of storing just auth stuff in user & move the rest of the stuff to another table called affiliates.

CREATE TABLE `affiliates` (
    `id` text PRIMARY KEY NOT NULL,
    `user_id` text NOT NULL,
    `affiliate_link` text UNIQUE NOT NULL,
    `paypal_email` text NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);

Now I'll have friends_of_affiliates table to store all the emails (friends_email) that the affiliates have referred. Point back to the affiliates table using either affiliates.id or affiliates.link as both are unique anyways.

I'll also use commission to denote $100 as an integer without the $ if in future, I change the commission number to $200 to use percentage like 20% or 30%.

CREATE TABLE `friends_of_affiliates` (
    `id` text PRIMARY KEY NOT NULL,
    `friends_email` text NOT NULL,
    `commission` integer NOT NULL,
    `affiliates_id` text NOT NULL,
    FOREIGN KEY (`affiliates_id`) REFERENCES `affiliates`(`id`) ON UPDATE cascade ON DELETE cascade
-- or `affiliates_link` text NOT NULL,
-- or FOREIGN KEY (`affiliates_link`) REFERENCES `affiliates`(`link`) ON UPDATE cascade ON DELETE cascade
);

And finally, cash_out table to store the payment & the date at which an affiliate tries to cash out their money. Or the date at which I send them money.

CREATE TABLE `cash_out` (
    `id` text PRIMARY KEY NOT NULL,
    `payment` text NOT NULL,
    `date_time` integer NOT NULL,
    `friends_of_affiliates_id` text NOT NULL,
    FOREIGN KEY (`friends_of_affiliates_id`) REFERENCES `friends_of_affiliates`(`id`) ON UPDATE cascade ON DELETE cascade
);

Is this DBMS design good enough? I haven't done this in years so Idk if I'm getting something wrong or anything that can be improved.

My goal is to show an affiliate the following:

  1. How much money did they make?
  2. How many customers they referred? Including showing their emails in a tabular format.
  3. Lifetime earnings as well as Monthly earnings?
  4. And when did they last cashed out plus all past cashouts?

I think this is possible from my above schema but still asking to see if there is anything that can be improved or done differently. I guess I could've combined affiliates table into user table & made this a bit cleaner with only 4 tables.

What do ya'll think?

0

1 Answer 1

0

Too convoluted.

There is an easy (kinda) approach to DB design. Draw your table with all the fields and data you need or can think of in a single Excel spreadsheet. Once you know that you can find anything you need in this spreadsheet - do the normalization. All the tables will emerge during the normalization and you would be sure that nothing is lost and data manipulation is convenient enough. If it is not - do normalization correctly next time.

And do not go to json route. Ever. It is for exchange data between databases, not for storing data inside the database.

2
  • yeah, i did find a better way while writing the code for this. i merged the affiliates table into users as it was only 2 fields so no need to reference it using foreign key. but was pretty curious if anything else could be improved other than adding just unique checks, using text instead of integer for date, etc... i have seen some hackers use json but yeah i also had your same conclusion to not use json because that seemed avoidable with an extra table to store an array of friends_email. anyways, i was looking for specific advice on my database schema. Mar 23 at 4:21
  • i did find a mistake in my schema. my cashout table was pointing to friends_of_affiliates but it should've been pointing to affiliates. i guess taking your approach would've been beneficial in this case. I'll have to check it out someday. thanks for that. Mar 23 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.