One Company, Many Users, how to maintain that in the Database

I have a simple one-to-many scenario:

  • One Company
  • Many Users

I have the link set up as Related_Users in the Company table
I want to make sure whenever a user changes company to remove the old link, so that the user is not connected to several companies.

Does this mean that I have to:

  1. Get the company objectId from the Users object (eg. Company[Related_Users].objectId
  2. Delete all relations from the Company object with child objects=Users.objectId
  3. Add a new relation to the Company object with the Users objectId as the single child object

So 3 DB operations in all. Is it possible to do this easier? If so, I would really like to know how… :slight_smile:

so that the user is not connected to several companies.

If you want that User has only one Company – you may just create 1:1 relation from User to Company table.
So, when you need to change the company (the objectId of the company is already known), just set that objectId as a relation and it override the previous value.

I did start out doing just that actually - but when I then want to list company table, and also show number of related users per company, I was having a hard time doing that instead…

But maybe that is still the better approach, and find another way to do the listing of companies with number of users per company and other similar summaries another way?

I went back to this 1:1 solution between Users and Company. I also found I could count number of users by adding a lookup to the Users.objectId in the Where clause, group on a unique field in Company (objectId) and aggregate using count in addition to get the actual user count.

Problem solved :slight_smile: