Relationships and Related List question

[Dan Johnson1]

I've got two tables:

1> Person - this stores details of a person (e.g. name, address etc).

2> Venues - this stores details of a venue (e.g. address, purpose etc).

A person can be situation at multiple venues and a venue can have many people situated at it. So far it's a simple many-to-many relationship.... however there's a twist

When person 1 is at venue 1 they are charged at a particular fee

My first thought was to add a new table PersonVenue which stores a reference to the expert and a reference to the location and setup relationships for this. This seemed to work ok in that I could perform what I needed HOWEVER the problem came when I started to add related lists to the Person and Venues form. 

On the Person form I want the related list to show the addresses of the venues they are based at

On the Venues form I want the related list to show the names of the persons that are based there.

So my question is which is the right way to go?

There seemed to be many drawback of the many-to-many (e.g. I could create duplicate person and venue records) and I couldn't store things like fees.

The 'bridging' table (PersonVenue) worked best but the related list options hampered the UX (if there is a way to fix this then this would be my preferred method)

Thanks in advance

[Kalaiarasan Pus]

Basically what you are looking for is a m2m table.

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/table-administration/task/t_CreateAManyToManyRelationship.html

task_ci table is an example of what you are trying to do. A task can have multiple impacted CI and a CI can impact multiple tasks. Check the table and how the relationship works/shown on both the side and that should help you.

[Dan Johnson1]

@Kalaiarasan Pushpanathan when I tried that I hit a couple of problems:

1> I can't associate a fee for one specific person and one specific venue

2> The user actually creates a new venue record, rather than 'linking' to an existing one.

Ideally I would use the venue table as a master source for all 'approved' venues

[Kalaiarasan Pus]

1 - You can create additional fields on m2m table and that should be able to take care of this.

2 - Since Venue will be a reference field on m2m table, they can create a new venue if the record does not exist from the same form by clicking 'New' button in reference pop up window.

[Kalaiarasan Pus]

1 - You can create additional fields on m2m table and that should be able to take care of this.

2 - Since Venue will be a reference field on m2m table, they can create a new venue if the record does not exist from the same form by clicking 'New' button in reference pop up window.