Unwanted add during add through an n:m relation

  • Resolved Michael

    (@mitfi)


    5 years, 1 month ago

    Hi Peter,

    tanks for helping with the “Error Message during add” topic, solved by patch three days ago.

    Now from that, I have the following issue:

    When editing a Music-Event (base table) I refer to a Songlist (called relation table with multiple songlists inside) through a List-of-Songlists table (called target table, for me the glue, the link between both).

    When I add a song to a Songlist, the plugin attempts to not only add a new song to the Songlist, but in parallel a new List-of-Songlists entry, referring to the new song. I prevented that by making the related List-of-Songlist column unique, which generates the error message you corrected to be properly displayed with the above patch [ERROR: Error create relationship (insert failed)].

    However, the Songlist has properly been amended and everything looks fine, except the error message (i.e., the plugin obviously first adds the song in the target list and then tries to add an entry inside the link (middle) table, which doesn’t work due to ‘unique’.

    Now, when it comes to deleting a song, it will not work, as your plugin comes up with an error message [ERROR: Could not delete row].
    Some of my starting thoughts:
    In an n:m, you can only delete relationships, in 1:n you can delete the item itself.
    This may be one path for better understanding, however, what is then the idea behind ADD instead of ADD EXISTING in an n:m relation?

    Of course, I could add and delete a song by other means, either directly in the Songlist or through an 1:n link in the List-of-Songlists table, but I still do not see the good reason, why the plugin would behave like this with the middle (called target, for me the glue, the linking) table.

    I am probably totally wrong in my assessment?

    Thanks in advance for bringing my mind to the right path…

    Best regards

    Michael

    • This topic was modified 5 years, 1 month ago by Michael.
Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Michael,

    Make sure you understand the difference between 1:n and n:m relationships. Here is overview of the types of relationships:
    https://database.guide/the-3-types-of-relationships-in-database-design/

    In your case there is a music-event and a play-list. Ask yourself what you want:
    (1) Should a play-list belong to one and only one music-event?
    (2) Do want to be able to “copy” a play-list from one event to another?

    (1) This is 1 :n relationship and involves only tables music-event and play-list.
    (2) This is a n:m relationship and involves three tables, music-event and play-list and a table that connects a music-event to a play-list. If you look at the many to many example on the page above, the connection between customers and products is established in table orders. You would need to add a table like event-list that stored the connection between an event and the play lists for that event.

    If you create a n:m relationship for the table music-event, play-list and event-list, two records are created if you ADD a new play list to a music event. One records is added to table play-list and one to table event-list. So there is no unwanted add. This exactly what you want when you use a n:m relationship. If you create new play-list for every event, just create a 1:n relationship. This kind of relationship however, will not allow to copy a play list from one event to another. This is what is meant with ADD EXISTING.

    I hope this helps you Michael. I will see if I can find some material which you and other plugin users to get a better understand of those database design concepts.

    Best regards,
    Peter

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Michael,

    What’s the status of this topic? Did you get it working?

    Best regards,
    Peter

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Michael,

    Hope you succeeded! I’m closing this topic.

    Best regards,
    Peter

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Unwanted add during add through an n:m relation’ is closed to new replies.