Post reply

Update relation table

  • January 25, 2019 at 7:53 am

    #381265

    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

    #2020114

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 25, 2019 at 8:42 am

    #2020119

    What's the update? You can potentially use a proc, a TVP, and update everything at once.

  • January 25, 2019 at 8:42 am

    #2020120

    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