[MySQL] User - group relatie, normalisatie?

[Knopsje]

Ik ben momenteel bezig met de ontwikkeling van een platform gebouwd op Yii, een PHP framework. Gebruikers kunnen zich registreren voor het platform en kunnen lid worden van bestaande groepen of zelf nieuwe groepen aanmaken. Om dit af te handelen maak ik gebruik van de Yii User Management (YUM) module. Dit werkt allemaal prima maar ik heb een vraag over de opzet van de database.

De modele gebruikt namelijk (o.a.) 2 tabellen, een User tabel en een Groups tabel. In de User tabel hebben alle users een uniek ID, gebruikersnaam, password, salt, etc. De Groups tabel ziet er als volgt uit:

code:

CREATE TABLE IF NOT EXISTS `usergroup` (
  `id` int(11) NOT NULL auto_increment,
  `owner_id` int(11) NOT NULL,
  `participants` text,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

De User ID's van de participants (dus de leden van een groep) worden (zoals hierboven te zien) als tekst opgeslagen in een door komma's gescheiden lijst. Nu lijkt mij dit eigenlijk een many-to-many relatie waarvoor je vanwege normalisatie een koppeltabel zou moeten gebruiken.
Op internet lees ik dat het soms sneller kan zijn om het wel op deze manier te doen maar daar durf ik zelf met mijn basiskennis SQL geen uitspraak over te doen. Ik kan me voorstellen dat het bij het openen van een groep sneller zou kunnen zijn omdat je dan alle ID's uit die cel kunt halen maar bij het bekijken van het profiel van een user en het weergeven van alle groepen waarvan hij lid is lijkt me dit juist weer langzamer. Cancelt dit elkaar uit? Of is het gebruik van een koppeltabel toch altijd sneller? De module wordt namelijk wel vrij actief onderhouden en het lijkt me niet dat ze zo'n basale fout zouden maken? Het systeem gaat hopelijk wel groeien en voor ik straks in de problemen kom als er ineens duizenden (of nog wel meer) gebruikers en groepen zijn zou ik nu graag horen of iemand hier iets over zou kunnen zeggen.

[incaz]

Allereerst is het een vrij jonge plugin zo te zien, dus best mogelijk dat ze die 'fout' wel maken. Een koppeltabel lijkt mij mooier en goed mogelijk.

Waar je wel op moet letten is dat je bij een gewone join de informatie voor owner_id, title, description steeds dupliceert. Dat maakt je loop ingewikkelder, en geeft bij veel participants wel een flinke hit. De data apart laden is ook risicovol, omdat je goed moet uitkijken dat je framework niet voor elke owner 2 queries uitvoert (1 om de owner, title, description op te halen, en 1 voor de participants.)

Dit kun je oplossen door in plaats daarvan een group_concat te gebruiken, wat in feite hetzelfde is als nu (en aan de code-kant exact hetzelfde kan blijven), behalve dat je de lijst opslaat als netjes genormaliseerde tabel. Met een index op owner_id in de participants-tabel zou performanceverlies (wel iets natuurlijk: concatten is moeilijker dan een enkel veld inlezen) minimaal zijn, en de omgekeerde actie zou idd sneller moeten zijn dan text-search op alle velden.

Dat laatste zou dus denk ik de beste oplossing zijn. Suggereer het aan de pluginbouwer