Multiple Column Primary Key & Foreign Key Reference

[jweststrate]

Hello!

I would like to share my approach to a multiple column primary key table and see if this is considered a best practice or if there is a better way to do this.

My data is stored in a Cloud SQL PostgreSQL instance.

TABLE A

• Column1 (PK in the source database, ref to another table)
• Column2 (PK in the source database, text field)
• other columns...
• Column3 (Virtual column created by AppSheet as the PK for this table)
  • formula:  CONCATENATE([Column1],": ",[Column2])

TABLE B (includes a FK reference to TABLE A)

• Column4 (PK of this table)
• Column5 (Text value)
  • VALID_IF:    SORT(TableA[Column1])
  • Note: I use SORT so the values are displayed in alphabetic order in the dropdown
• Column6 (Text Value)
  
  • VALID_IF:   IN([_THIS],SELECT(TableA[Column2],[_THISROW].[Column5]=[Column1]))
  • Note: I do this to filter the dropdown to dependent values only. AppSheet would automatically filter the dropdown, however, when I apply the SORT on Column5 above, it no longer filters  this dropdown so I need to use this formula.
  • Note: I do not know how to apply the SORT or ORDERBY to this syntax.
• other columns...
• Column7 (Virtual Column I created as the FK reference to Column3 in TABLE A above)
  • Column Formula:  CONCATENATE([Column5],": ",[Column6])

I feel like the simpler and more performant approach would be to change my source database by using a surrogate key instead of a multiple column key, but I wanted to give this a try to see what is possible in AppSheet.

Thanks!

[TeeSee1]

I certainly do not know the best practice...

The way I would model your case is Not use Col 5 nor 6 in Table B and use Col 7 as a non VC but as a normal col referencing Col 3 in Table A.

This seems cleaner and more readable. I have no idea how this would convert to the underlying Postgres SQL though. You may have to manually tweak it from a performance optimization point of view. But I do not think join operations are performed in the background engine and probably does not matter too much.

I would also certainly be interested to know what other community members think here and give insight into how the modeling in AppSheet affects performance depending on the underlying Data Store or anything else.

[jweststrate]

Thanks TeeSee1. That is most likely the best way to approach this; change the underlying PostgreSQL database from a multiple column (composite) Primary Key to a surrogate Primary Key.

When researching for this question I found this post, however, no solution was presented so I'm not sure if the problem was solved in a different way.

[Marc_Dillon]

I would 100% always use a single column surrogate key. Unless there was some largely beneficial reason to use a multiple column composite key. And I have not encountered any situation that would benefit from such, nor can I envision one.

I also can't really tell what your goal is, from your description. It seems like a lot of details for the simple question of "surrogate vs composite", but I don't understand what all those extra details are for. Is there some larger issue that you're trying to account for?

[jweststrate]

Thanks Marc - starting from the AppSheet perspective, I completely agree. 

From strictly a database perspective there are times where a natural composite key makes sense, which is the scenario I currently have. I was trying to 'bend' AppSheet to work with that, however, to your point, not sure this is a good practice especially when it comes to performance and maintenance.

Fortunately, I have the opportunity to modify the source database structure so that's what I'll do. I'll change the table to a single column surrogate key. For those who can't change the underlying database structure, know that it will still work with a little bit of gymnastics in AppSheet.