How to manage many-to-many relationships

[skwaler]

I manage a many-to-many relationship we have between some tables.

 

e.g.

Primary tables are “Studies” and “Sites”. 

Have a table to join them, “study_sites”

 

For each Study many sites could be associated. Sites can be associated with many Studies

 

I can’t find anyway to create a UI that can:

• Show me all the sites that are currently associated to a site.
• Allow me to remove an existing association
• Allow me to add a new association, but also ensure the site/study relationship does not exists. (don’t want any duplicates in “study_sites” table)

 

e.g. I can use two data pages.  Top one shows all sites that are currently associated. Bottom submits a form add a new record to  “study_sites” table. The problem is this can allow duplicates.

Is there someway to stop a duplicating from being inserted by triggers?

There some smarter way to do this?

I did try using a table report adding SQL to a calculated field, but it won't render the HTML 

CASE
 WHEN (SELECT COUNT(study_site_id) FROM sr_tbl_study_sites WHERE study_id='GU986E2K34MD' and site_id=target.[@field:site_id]) = 1 THEN 'Exists, click to remove'

 ELSE '<a href=/admin-associate-study-site.html?study_id=GU986E2K34MD>Not associated, click to add</a>'

END

 

I have similar requirement to association many users to many sites.

any advice greatly appreciated.

[skwaler]

I just realized i can create a composite primary key by using a unique formula field and joining the study & site ids. 

So I guess this works, but I still wonder if there some more elegant way to present an interface to manage this scenario?

 

 

[skwaler]

Say we are assigning staff to one or more sites.

Could we use a list string data type in the staff table.

e.g. [site1, site3, site, 4]

Would this be easier to manage than maintain a separate tbl_staff_sites  ?

[NiceDuck]

I would refrain on using List data types. Right now, they have a very limited use as they cannot be used in both triggered action and SQL.  

Im still not sure if I got your inquiry correct, but if I do, what I think you wanted is that a user should only be able to see a site if they are assigned to that site.

What I would do on this case is that, I would create a text255 or text 6400 on the site table. then per site, I will have a comma seperated values of the users ID there which is allowed to view the specific site.

When you then create a datapage, it can be then filtered that if the CSV field contains an ID of the current user, then show the record. Otherwise, dont.

 

[skwaler]

@NiceDuckInteresting idea. How would you create a UI to manage which staff are assigned to the site?

Would you still have a separate table  tbl_site_staff and have a datapage that manages this?

Then have a trigger on tbl_site_staff that updates the CSV field on tbl_sites each time tbl_site_staff changes?

[NiceDuck]

In my idea about assigning a staff to that site, I would have an editable tabular report that dsplays the user. This page should only be accessible by admin and higher and from here, they have a dropdown where they can set which site that user belong to.

I would then have a trigger on the user table that runs on update (or even on insert). This trigger will add or delete the user ID on the Site record depends if they are assigned there or not.