January 25, 2019 at 7:53 am
I have table1 that has a many to many relations to table2
table1(id, data) <--- relations(id1, id2) ---> table2 (id, data)
I have a middleware that asks for a put on table1 and passes a list of ids of table2, so I can update the relations
I can do this in several ways, however I'm wondering if the following is a good one:
- put a field in table1 that contains the relations list in some format (json, comma-sep or xml)
- use a trigger to automatically keep in sync list and relation table
I tend to avoid triggers, so this solution is not among my choices...
Any comment or alternative way to read/update the list with for example a calculated field ?
Thanks in advance.
January 25, 2019 at 8:01 am
Can you explain this part in more detail, please?
I have a middleware that asks for a put on table1 and passes a list of ids of table2, so I can update the relations
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2019 at 8:42 am
What's the update? You can potentially use a proc, a TVP, and update everything at once.
January 25, 2019 at 8:42 am
The example is the classic user / group pattern, a user may appear in more groups.
The middleware calls for a PUT and passes the user data + the list of group IDs the user must appear
something like this: PUT {"id":"usr999", "name":"john", "email":"xxx@yyy.com", "groups":"g1,g2,g3", }
so the middlware provide a list of IDs in some format (json, xml, comma-sep) and I need to turn them into relation table rows
a way, for sure, is to do this in the middleware with an ad-hoc query just after updating the main table, but I'm wondering if there are alternative sql-server solutions like the one described in the original post text:
- put a field in table1 that contains the list in the original format
- use a trigger to automatically keep in sync list and relation table
Hope is more clear.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply