Looking for opinions: moving from a one to one, to a one to many account structure. how?

So this thread might die on its arse as it's complicated to explain. I'll try to be succinct.

My app, Songbox is setup on a one to one way. What I mean by this is that when you signup, then all the "songboxes" you create are yours. You, the person who signs up, are the artist.

  • Your profile pic is shows on your pages
  • your bio is shown on your pages
  • all the views and listens are credited to your account

I need to add a business plan where the person who signs up, the account owner, will be able to manage multiple artists.

In a nutshell, creating an account which in turn can create and manage multiple artists.

This is a pain to explain but if anyone can understand my nonsense and can think of an efficient way to do this in terms of database structure, I'm all ears.

  1. 4

    This is something Ive done many times before and is pretty easy to restructure. Essentially you need to take your one-to-one relationship (one user to one songbox) to a many to one (a user can have many song boxes, and (optionally) a songbox could have many users managing it).

    As an ERD, that looks like this:

    Essentially, rather than a songbox entity holding a direct reference to its owning user, we break that out into a join table to associate a user and a songbox. Since this join table could have multiple rows, it gives you the flexibility to allow for a songbox to have multiple users associated with it. If you wanted a user to "own" the songbox and another user to manage some things about it, this is how you would accomplish that (I included a role table in the lower diagram to show how you could start building out a permissions model).

  2. 2

    What you have right now is completely segregated multi-tenant architecture. Firebase excels at this simple first-step MVP-based solution. (If you are using it), the problem as you've figured out, is that you need to allow security in how users share data with each other. Share account data, manage permissions, ownership, etc...

    Database Structure - There are many possible database structures to store the data, it could be as easy as just creating additional rows, and mutating the data when fetching. You can use Foreign Keys (FK) pointing at accounts tables. It can pretty complicated pretty quickly, especially if you start adding in convention based approaches like First user is the admin, what happens if they leave, what happens if you want to make two admins, what if they want to have multiple accounts. Sure you can hack around this...

    But really, you might not need any of that. Of course there is more data to save, and return, but that isn't the hard part. The hard part is getting the permissions right, and that is really what we are talking about here.

    How should I implement permissions?

    Here's an example app and how you might set up permissions to allow different users different access to many resources all within one account:

    Example app with a security implementation

  3. 2

    You don't mention your stack, for some reason.

    If you're using Rails, the JumpStart Pro starter has team (what you describe as one to many) handling built-in.

  4. 1

    Relational databases aside, a common way that SaaS approach implementing a business plan is to use SSO (single sign-on). It's an important feature for larger business. This would require you to implement a protocol like OAuth 2, separating your Identity Provider from your Service Provider, thus allowing one user identity to be linked to several service users.

  5. 1

    I understand your pain. Let me help you to think thru.

    Introduce a concept called Space. Every user will have access to atleast one space

    Each songboxes belong to one Space.

    Users who has access to a space can access all the songboxes under them.

    If users have access to multiple spaces, them they can switch between the spaces.

    Database Structure will be as follows

    Table : Space
    Columns : Id , name , ...

    Table: space_user
    Columns: space_id , user_id

    Table: songboxes
    Columns : Id, name, ...., space_id

    That's it.
    Next, let me know if you have more questions.

  6. 1

    It sounds like you would like to make it possible for users to sign in on their behalf.

    The simple way would be to add a switch somewhere for users that signed in with either the admin or child account. Then add the switch user flow from there. This way an admin will see the same ui, only probably empty. Where they can switch to another account from there.

    I would set this up manually in the db at first, when there are many request for this user type you can add the flow to manage it themselves later.

  7. 1

    Hey, Mick. This is totally doable, but in order for anyone to give you meaningful advice, you'd need to give them a peek behind the curtain so to speak.

    What database are you using?
    What do your existing database models look like?
    Is an artist a different entity than an account?
    Should artist be allowed to manage their own account in addition to the business?

    These are only a few of the questions that need to be explored to do this right.

    My email is on my IndieHackers profile. Feel free to reach out. We can setup some time to discuss this further if you'd like.

  8. 1

    My recommendation in order:

    1. Look if your tech stack has a widely used setup for this.
    2. Implement a database model with users (these are essentially only logins), "songboxes" (where you store songs together), and organizations (these can have multiple users and multiple songboxes and e.g. the billing and permissions). Doing this requires you to have many-to-many links in tables (as a user could be a member of multiple organizations). This requires you to also have a table linking users and organizations (see for many-to-many: https://launchschool.com/books/sql/read/table_relationships).
  9. 1

    One important question is if the business plan account is more like a special artist account that get to create and manage multiple artist profiles for there account (instead of being stuck with just one).

    Or do you need more of a hierarchical account structure where a business plan account has admin privileges for multiple single artist accounts?

    The later is likely much more work and refactoring to implement, but will make your life easier when it comes to extending your business accounts in the future (business plans can have intermediate accounts with control of just a subset of the artists.

    I am guessing your DB likely has just a single account table with columns like email, profile name, bio text, etc. If that is the case, then you probably need to split out data that is just for accounts (email, billing info, etc) and then have a table for artist data (artist name, artist bio, etc).

    Once you have that then you can add a system to manage account access to artists. Simplest approach would be to have a table that link accounts with artists. Things do get more complicated if you want users to be able to manage user accounts and assign custom roles and permissions.

    Hope that helps a little bit (I went off a lot of assumptions)

  10. 1

    If I understand correctly, you can:

    • create a new table, business_user with business_user_id + manages_user_id (thus, for any new user someone manages, just add a new entry here)
    • in users table, add a 'role' to each user (which by default would be 'user', or it can be 'business_plan')

    The rest will be changes in code, to show everything correctly in the UI.

  11. 1

    I just did this for one of my own products.

    You just add a new foreign key to the users table, pointing to a new accounts table.

    The user who has the lowest id in an accounts list of users is the admin.

    Nothing too complex and definitely an easy structure to add more complex logic to.

  12. 1

    Just throwing some ideas around which might help as I don't know the complete schema and type of database and there can be multiple solutions too.
    Thinking of next steps as Access control, Multiple features mapping to users etc,

    1. Non-scalable approach
      Add a column/key with something as reference_id and start adding there as owner reference id which is just one to one mapping to the same table of users. This way you can know who is the owner or a single owner is connected to how many users.

    2. Scalable approach
      Add a complete ACL later where you manage roles and permissions along with the teams feature. A team model would have multiple members which is nothing but a one to many mapping of team and users. A team would have single owner always and that team would have multiple members. This would require more changes but would open up features like access control at various levels and you can do that in phases.

    1. 1

      I'm asking because I don't know - why is 1. a non-scalable approach?

  13. 1

    this might sound crazy... but, multi-tenant / multi-user / member management stuff is complex... but is sometimes easier to build from scratch, depending on how complex your existing system is.


    1. 1

      I just had to do this for an enterprise application I'm building, row-level multi-tenancy works great as an MVP, i.e. just adding an fk to resources that belong to other resources.

Trending on Indie Hackers
Rejected from YC 18 comments 29 days left before 2022 🔥 What do you want to finish & accomplish before the end of the year? 15 comments Bootstrapping a SaaS that uses AI to explain code in plain English 6 comments People found our landing page confusing. 5 comments Another landing page builder ... 3 comments Live Below Your Means for Freedom 2 comments