Może na prostym, fikcyjnym przykładzie:

Dodajemy wpis w CMS.
Jednym z atrybutów wpisu są TAGI (0..n).
Tagi dodawane są dynamicznie przez pola input (JS).
Dodajemy wpis i kilka tagów my przypisanych.
Tutaj nie ma większego problemu - korzystamy z INSERT w pętli, PS bądź insert batch (akurat teraz pracuje na CodeIgniter).
Na tagi mamy oddzielną tabelę w bazie o strukturze:
[ id ] [ art_id ] [ name ]

I teraz chciałbym edytować listę. W jaki sposób?

1) update na podstawie [id], w porządku ale co zrobić gdy ktoś doda nowy tag? Wtedy też trzeba wykrywać nowo dodane wartości?
2) co zrobić w przypadku usunięcia? znów kolejne zapytania?

Najprostsze rozwiązanie jakie mi przychodzi do głowy to:
- usuwamy wszystko co ma art_id = X
- dodajemy na nowo tagi
Te rozwiązanie jest jednak niewygodne ze względu na szybkie narastanie wartości ID -> np. mając 20 tagów każda edycja (bez względu na to ile się zmieniło) zwiększa najwyższe id o 20. Poza tym te operacje wydają mi się mało wydajne - nawet gdy nie zmieni tagów to wykonają się dwa spore zapytania.

Praktycznie ten problem koncepcyjny wraca do mnie jak bumerang. Za każdym razem mam jakiś pomysł ale żaden mnie nie satysfakcjonuje.

Jakieś sugestie?

Tabela ART
ID
blablabla

Tabela TAG
ID
NAME
blablabla

TABELA ART_TAG
ID_ART
ID_TAG

Zauwaz ze tabela wiazaca nie ma wlasnego ID i nie ma tu mowy ze cos ci bedzie narastac.
Robiac edycje kasujesz wszystko z danego arta i dodajesz na nowo.

Tzn uproszczając chodzi Ci o:

ARTS: id, title...
Tags: id_art, name

Czyli pominięcie ID w tabeli tagu?

Takie rozwiązanie raz zastosowałem i działa w porządku choć nadal nie rozwiązuje kwestii optymalności - duży delete, duży insert.
Te rozwiązanie także ma minus że TAGi nie mają swoich ID co może potem narobić kłopotów.
Twoja struktura problem z ID niweluje ale dodaje tu już trzecią tabelę - kolejne JOINy, troszkę taki przerost formy. Poza tym pierwszy insert nie da się załatwić jednym zapytaniem bo musisz najpierw dodawać wpis do tabeli TAG aby otrzymać id, potrzebne w tabeli ART_TAG.

Mam jeszcze inny pomysł (na dwóch tabelach):
podwójny indeks na id i art_id UNIQ. Wykonanie INSERT / UPDATE ON DUPLICATE na każdym rekordzie.
Potem wszystkie ID wykorzystane lecą do DELETE NOT IN.
Też troszkę zapytań ale na dwóch tabelach i delete, który wywali mniej rekordów.

Jeszcze 3 brzydka ale optymalna opcja.
Na jednej tabeli i dodatkowa kolumna "tags" z tablicą (serializacja).

Kurcze..... widzisz ile ja ci podalem tabel a ile ty twierdzisz ze ja ci podalem? Ja ci podalem 3 tabele, ty to ni z gruszki ni z pietruszki upraszcasz do dwoch tabel.....

Spojrz co ci podalem:
tabela na arty
tabela na tagi (tu tag ma swoje ID)
tabela wiążąca tag z artem - to w tej tabeli wiazesz do jakiego arta nalezy jaki tag i to w tej tabeli kasujesz i dodajesz łączenia.

Wszystko rozumiem, po prostu czytaj do końca posty.

Sorka, to po co wracasz do swojej struktury piszac tak, jakbys pisal o mojej.... zmuszasz ludzic do czytania calego posta


Tak sie robi prawidlowo. Dzieki temu tag o nazwie XYZ jest jeden w bazie. W twoim rozwiazaniu tagow XYZ bedzie tyle, ile razy ludzie przy roznych artach go wpisza. Tak sie nie robi i juz.

Czyli idąc dalej to przy pierwszym dodawaniu wpisu skrypt musiałby najpierw sprawdzać czy dany TAG już istnieje.
Jeżeli nie to dodaje i zwraca ID, jeżeli jest to musiałby dodatkowo pobierać jego ID (nie widzę w Googlach jakiejś ciekawej konstrukcji typu INSERT OR RETURN KEY IF EXISTS).

Dokladnie

jejkus.... przeciez robiac sprawdzanie czy jest czy nie ma to mozesz od razu to ID pobierac. Jak ci pobierze ID, znaczy ze jest, jak ci nie pobierze, znaczy ze nie ma

Czytaj całe zdania, które cytujesz zresztą.

Chodziło mi o załatwienie tego jednym zapytaniem - INSERT nowy TAG, jeżeli BRAK wtedy RETURN ID.
Ale się nie da. Sprawdzasz czy najpierw istnieje (i pobierasz te ID, to oczywiste), jeżeli nie ma to dodajesz i tak w pętli przy każdym tagu.
Liczba zapytań = liczba tagów x 3 (sprawdzenie, insert w tabeli pierwszej, insert w tabeli drugiej).

W każdym razie te rozwiązanie nie jest mi na rękę, przykład z tagami faktycznie może nie był dobrą analogią do mojego aktualnego problemu bo po prostu wartości dodawane z małym prawdopodobieństwem będą się dublować (np. imię i nazwisko, wtedy tym bardziej nie chciałbym aby jedno ID miały dwie różne osoby o takim samym imieniu i nazwisku).

Ps. No i ogólnie teraz jeszcze jak myślę.
Twoje rozwiązanie nie przewiduje literówek.
Dodałem 10 tagów, w jednym była literówka.
Poprawiam literówkę, daje wyślij (nie dodaje nic, nie usuwam).
Skrypt usuwa wszystkie wpisy z TABELA ART_TAG.
Dodaje od nowa w tym jednego tagu nie znajduje.

Dochodzi tutaj obserwowanie czy nie ma jakichś tagów "bękartów" nie używanych nigdzie.

To pisz po ludzku. To kolejny post gdzie piszesz tak jakbys pisal o dwoch roznych rzeczach a potem ze niby nie i ze to niby moje nieumiejetne czytanie....


Wszystko zalezy jak ty te tagi dodajesz/zarzadzasz nimi.
Poprawienie literowki w arcie, polega na tym, ze usuwasz zly tag i dodajesz nowy - wowczas wszystko jest jak nalezy. Nie mozna edytowac nazwy juz dodanych tagow, a nawet jesli to przewidujesz, to edycja nazwy w z automaty usuwa dany tag z arta i edycje czyni nowym tagiem w arcie.