Database Relationship Help

I’m building an ERP for Construction companies, and you can create a user account for an employee at one of your clients companies, so that they can log on and view things, interact with you etc.

To link this User account with the Client, should I have: A) A field on the User: Client (Type Client). or B) A Field on the Client: Employees (Type User). Different Organisations using my app might have the same client, but these will ne separate database entries, So Company A might have ‘Apple’ as a client, and Company B might also have ‘Apple’ as a client, but these will be two separate items in the database. Both companies may want to have an employee from Apple have access to a portal on the app.

This user account would then be linked with two different clients, Company A’s Apple and Company B’s apple, if that makes sense? So how should this relationship be structured?

So I work for a construction company and use your ERP system. One of my clients is Apple and I need to invite an Apple rep to the app so they can log in and view certain information, correct?

Now let’s say I work at Apple and am invited to this app by one of my vendors. Other Apple employees are already using the app. What should I see when I log in? All information related to Apple? Or just information related to my relationship with this one vendor?

Thank you for the response.

No, you would not see your information related to Apple, the only relevant connection is between you and the vendor, and you would just access information that had been assigned to you by the Vendor related to specific projects.

Following on to my previous reply, if you were an employee at Apple, and there were 200 Organisations signed up to my app which had Apple as a client, There would be 200 database entries for Apple, all different though with different Unique Id’s.

Would it be better for the Apple Employee to have a list of ‘Clients’ that they are associated with, or would it be better to have a list of Users on each Client, and link the employee to the Client in this way.

I hope I have explained this well, I appreciate your help :slight_smile:

If I understand your case correctly then I’d create a separate datatype to track the relationship between vendors and their client reps. As an example:

Vendor Management Datatype
Field 1: Construction Company
Field 2: Apple Rep (type = user)

So there would be three data types:

  1. Client [Apple]
  2. User [Apple Rep would be saved as a user]
  3. Relationship [A field storing ‘Apple’ and a field storing the User/Rep?

I’m a bit confused. I thought you were asking about how to store the relationship between construction companies and their respective client reps. But in your last comment it you’re referencing the relationship between client and client rep. Which one are you asking about?

So an organisation using my app may have Apple as a Client.

They may create an account for an Apple Representative so that they can share files etc on the app.

So lets say there are 10 organisations signed up to my app, all 10 have Apple as a client.

There would therefore be 10 different 'Apple’s in the database, all with a different unique ID

But all 10 organisations will be dealing with a specific rep, say Tim Cook.

A user account is made for Tim Cook, which can be accessed by him to interact with all of these Users of my app.

The Users of my app may want to access a list of their Contacts for a specific client of theirs.

Therefore, there needs to be a relationship between Tim Cook (And other reps at Apple), and each Client in the database.

Is it best to have a Field on each Client called ‘Representatives’, and store a list of the Representatives. (So All 10 ‘Apple’ entries in the database will store a list of representatives, including Tim Cook)

Or, should each ‘Representative’ have a field storing the list of Clients they are being associated with, so Tim Cook would have 10 entries, Apple.

Hi Keiran, I’m not sure if you’re new to Bubble or have been at this for a while but I suspect that there’s no need for having multiple Apple instances and structuring your data in the way you’ve described. Generally this wouldn’t be a good practice but I don’t know the details of your specific requirements.

From what I understand I’d say a general approach you could look at is to have a datatype for:

  • Construction Company
  • Client
  • User
  • Company/Client
  • Company/Rep

A client rep can simply be denoted by a field on the user type. Depending on your needs, they could also require a separate datatype if you’re storing a lot of information about these types of users which doesn’t apply to other types of users. For example, if reps have a profile page with a bio, banner image, tags, etc

So Tim Cook would simply be an instance of the user datatype with role=client rep & employer = Apple. There would only be one instance of Apple in the database.

To track company/client relationships you’d need another datatype which is noted above as Company/Client. If you have 10 construction companies using your app and 8 of them have Apple as a client, there would be 8 instances of this datatype. When a user from Joe’s Construction goes to their dashboard and you want to display a list of that company’s clients, you could search this datatype to pull that up.

Now, to show the specific Apple reps that work with Joe’s Construction, you would search the Company/Rep datatype which matches a company with a user of type client rep. In this example we’d find an instance representing the relationship between Joe’s Construction and Tim Cook from Apple as well as any other Apple rep which also works with Joe’s Construction.

I think that addresses your question if i understood it correctly. Keep in mind that this is a general suggestion based on my rough understanding of the requirements you posted. There are often multiple valid ways to represent interactions/relationships in a data structure. When considering how to structure your data, you should always design with privacy rules in mind and anticipate what data you need for which pages and how that will be accessed.

As for your specific question about lists, I recommend you have a look on the forum and read about the pros/cons of using lists and the use cases it can support well and those that it cant. The Ultimate Guide to Bubble Performance is also a great resource to learn about this and more.

Hi Alex,

Thank you this is a big help :slight_smile: