Skip to main content

Having / Storing extra information in the link between 2 tables

  • February 24, 2021
  • 4 replies
  • 51 views
G

Hi everyone,

I have 2 tables one for People and one for Businesses

The 2 tables are linked so a Person can be linked to multiple Businesses and vice versa.

I am trying to figure out if there is a better way to store the kind of relationship a Person has with a Business. For example a person could be volunteer for a business or an owner etc.

At the moment in the People table I have multiple columns that are all linked to the business table with each column expressing the relationship ship to the business. I am finding the number of columns I need to show the relationships is becoming a burden and would love a way to describe/store the relationship (volunteer, owner etc) in a better way. If a person could be linked to only one other business it seems quite easy but I cant think of another way to handle this with multiple business relationships.

I’d really appreciate any words of wisdom people could offer here to help with this.

Much appreciated
Glen

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • February 24, 2021

You will need to setup a 3rd table as a “junction table” to keep track of the relationships, and create a many-to-many relationship between your 3 tables. Here is information on how to set that up:

kuovonne
G
  • Glen_Saberton
  • Author
  • Participating Frequently
  • February 28, 2021

You will need to setup a 3rd table as a “junction table” to keep track of the relationships, and create a many-to-many relationship between your 3 tables. Here is information on how to set that up:


Thanks for sending the link. It made a lot of sense but think I’m still missing something or may need to make more tables.

I created a junction table which my ‘people’ and ‘business’ tables are both linked to that also has a column for the persons relationship with the business. The primary field is a function that makes:

‘Felicity flower shop-Volunteer’

So when I look at the ‘People’ table there is a column showing the above (the business and how the person is related to it) …it works pretty well.

I’m having trouble though understanding how to have a similar column in the Business Table that shows the person and how they are related to each business and still have it as a proper entity rather than a string so I can enter data properly in either the ‘business’ or ‘people’ table

Not sure if this makes sense its quite hard to describe, I’m starting to think I’ll need to make more tables but hopefully I can get some words of wisdom before I disappear down a rabbit hole of linked tables.

Thanks for the help ScottWorld :slightly_smiling_face:
Glen

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • February 28, 2021

Thanks for sending the link. It made a lot of sense but think I’m still missing something or may need to make more tables.

I created a junction table which my ‘people’ and ‘business’ tables are both linked to that also has a column for the persons relationship with the business. The primary field is a function that makes:

‘Felicity flower shop-Volunteer’

So when I look at the ‘People’ table there is a column showing the above (the business and how the person is related to it) …it works pretty well.

I’m having trouble though understanding how to have a similar column in the Business Table that shows the person and how they are related to each business and still have it as a proper entity rather than a string so I can enter data properly in either the ‘business’ or ‘people’ table

Not sure if this makes sense its quite hard to describe, I’m starting to think I’ll need to make more tables but hopefully I can get some words of wisdom before I disappear down a rabbit hole of linked tables.

Thanks for the help ScottWorld :slightly_smiling_face:
Glen


It sounds like you will want to use “lookup fields“, which display information from linked tables:

G
  • Glen_Saberton
  • Author
  • Participating Frequently
  • March 1, 2021

It sounds like you will want to use “lookup fields“, which display information from linked tables:


Thanks again for your help.

Alas I did try Lookup fields by ‘looking up’ a function field in the Junction table to create a string showing the person and their relationship with the business. (the primary field is also a function but this concatenates the business name and the persons relationship with the Business)

The look-up field in the Business table shows the info (person and relationship with the business) but its a string not an ‘entity’ so I obviously can’t connect people to a Business directly in the business table.

Sorry its hard to describe.

Glen

Terms & ConditionsAccessibility statement