Of course! Thank you
tbl_EnqProviders is my new workaround table. Basically a replica of sub_enqProviders
Is the outcode the first part of the postcode or something custom?
Yeah, thats right. Should always be the first part of the postcode.
But I'm getting the nearby outcodes via the postcodes.io api. So I can then select a subset of the providers that are in the vicinity of the enquiry address
So outcodes come from an api rather than a table?
Outcodes are nearbye postcode areas? or do you use the api to strip the outward part from the full.postcode
Yeah, so I'll try and really simplify to bullet points.
- Enquiry submits their postcode >
- Via external APIs I pull a list of outcodes that neighbour the enquiry postcode >
- I match the outcodes from the APIs to any provider using the outcode field in that table
- I then currently run 1 insert query to add the enquiry to the enquiry table
- I then run a second multi insert to add the provider URNs to a seperate table.
Everything above works as I need it to. However, my only concern is if I should persevere with using a subtable in place of 2 seperate tables (with a linked field). I dont think there is an issue, but am just trying to be sure before I build on top of it. Are there any specific benefits to having a subtable rather than 2 seperate tables with a common field?
Sure, at a basic level
Separate tables are mainly suited to tables which have a 1 to 1 relationship, sub tables are best where there is a one to many relationship.
If you are happy with your solution then we can leave it there.
1 Like
Really appreciate all the advice. Thank you
Actually this video may be a great help as it explains how the json data structures are different when using joins v subtables which should help you make a choice.
2 Likes
Great video Brian, thanks for creating so much wappler content.
I did watch this when I watched your first tutorial last month but hadn't thought to revisit. Cheers