Database Structure: multi-tier database configuration

How Do I
#1

Hey guys!

Looking for a little guidance to ensure I’ve got this set up properly (I might not at the moment).

In my model, here’s what I’m after:

Enterprise [Entity] (top level)
Service Company [Entity] (a child of the Enterprise)
Admin [User] (associated with the Enterprise)
Manager [User] (associated with the Service Company)
Advisor [User] associated with the Service Company)

Leads (assigned to an Advisor, created by the Advisor related to their Leads)
Notes (associated to the Leads) (these are “Transactions” in the diagram)

Right now it have it set up like the following:

Screen Shot 2020-11-10 at 10.41.35 PM
Screen Shot 2020-11-10 at 10.41.35 PM1228×237 19 KB

My Users have different “Types”.

  • An Advisor only ever sees Leads assigned to them.
  • A Manager sees all Leads (and related notes) associated to the Service Company they can access.
  • An Admin can see all Leads associated to their Service Companies.

First, is this possible?
Second, does my setup need tweaking?

#2

It looks from the graphic that you’ve set up the relationship the proper way, except maybe the Admin Role.

  1. An advisor only sees leads assigned to them. This is the simplest and available by adding data and selecting to only show Leads assigned to logged in user.

  2. For managers to see all leads associated with their company you must add an additional filter in the data source. For example, add a “Leads” table, then add a Data Source filter and select to filter the Leads based on the “Financial Service Company” then choose “is connected to logged in user” and choose the filed that connects them. Meaning, the field from users table that connects to the service company.

Here’s a screenshot of this option, but with a different database. The point is the same.

image
image1793×851 60.8 KB

Here’s the link to support docs: https://docs.tadabase.io/link/44#bkmrk-there-are-several-ad

  1. This seems the same as Number 2. But in your comment higher up you mention that they can access based on the Enterprise. I don’t see any

I think you might need to add a connection from Users to Enterprise for the Admins. Then show a table with all the Enterprise that the logged in user is associated with. Finally inside the details page you can add the leads that are Related (vs connected) to the enterprise. You won’t be able to add a Leads table on a parent page to filter based on Admin’s > Enterprise > Financial Service Companies since this is already too many steps removed. See this link: https://docs.tadabase.io/link/17#bkmrk-4)-records-related-t (in the section called “Records Related to”).

I hope that makes sense. Let me know if I can clarify further.

#3

You’re right, I think this works.

Basically if an Admin at the Enterprise wants to see “All” leads across the companies, they wouldn’t be able to view them in one page?

I got it working where the user can view a list of ‘Service Companies’ then view the Leads associated to that selected company.

#4

Hi guys,

Following on in this thread, if I have the table filtering correctly, how do I prevent someone from editing the URL to access a child record directly? I.e. a record they shouldn’t have access to, but if they obtain the record ID and edit the URL seem to (in my testing) be able to still access the child record.

Would really appreciate any guidance on security best practices here.

#5

Hi @emileg I believe this is currently tied to a known bug and is unintended behavior. I don’t have a timeline on when this would be fixed but I will keep you posted.

1 Like