Hi there,
I am building an app and looking for advice regarding the database structure?
I want to build a SaaS for referral marketing, users can sign up and run a really simple referral marketing campaign for their clients.
As such I imagine I’ll have something like the following:
-
Users (customers who signed up to my service)
- will contain standard fields to identify user / payment status etc
- list of clients
-
Clients (the actual clients of my users - we’ll use their email as the ID)
- standard fields to identify each client
- owner (the user(s) the client belongs to)
- fields relating to referral status, who referred them, who they referred, how many referrals they made and how much money they are owed etc
(Note: I am avoiding discussing the referral code system here, as it is outside the scope of my DB problem)
Where I get really confused, is that there is a case where users could share the same client. Therefore it seems to me that for each referral I need to record the clients and the user whose business it relates to. What is the best way to do this in the DB?
My assumption is to create a new type for referrals and log them all?
For example something like this:
- Referral
- User (which business the referral applies to)
- Who referred (person who made the referral)
- Who was referred (person who received the referral)
Is there another method? This seems a bit wasteful to me? If I go this way then I assume when I want to see how many referrals a client made for a specific user, it will basically be a search of all referrals that match that client and user ID? Is that efficient? If there is a better method I’d love to know.
Finally, if anyone happens to knows of any online guides that can teach you how to approach DB structure for more complicated use cases, would you mind to share it ? I’ve seen a few on youtube, but they are pretty light and they were not much help in planning a SaaS db structure. I’ve already done all the official tutorials.
Thanks very much for any help you can give!