[php/mysql] string ontleden in mysql

[Spooksel]

De situatie:

Ik heb een enorme lijst met persoonsgegevens, deze personen kunnen verschillende rollen hebben: cursist, medewerker, docent, donateur of bestuurslid.

Iedere persoon kan 1 of meerder van deze rollen hebben en deze staan dan opgeslagen in een extra veldje in de tabel: kan er bijv zo uitzien "mdb". Deze persoon is dan medewerker, docent en bestuurslid.

Als ik dus een overzicht van een bepaalde groep mensen (bestuursleden) wil hebben kan ik dus zeggen:

code:

SELECT mensjes WHERE rol LIKE '%b%'

Nou wil ik echter de rollen opslaan als een string van 1 en 0, de rollen "mdb" zouden dan als volgt in het veld staan:

code:

cmdtb
01101

Dit kan ik dan (in php) als volgt controleren:

code:

If (substr($rol,4,1) == "1") {
     echo "Dit is een bestuurslid";
};

SubStr kijkt dus naar het 5e teken in de string en checked of deze een 1 is. Het nadeel van deze methode is dus dat ik eerst ALLE personen moet ophalen om daarna middels PHP te controleren of ze bestuurslid zijn.

Nou wil ik weten of er voor MySQL een query op te bouwen is waarbij ik de substr() van PHP direct kan toepassen in het DBveld met rollen.

Ik als enige tot nu toe de methode gevonden waarbij ik dus niet 1 en 0 gebruik maar gewoon de letters.

[Verwijderd]

Ik zou zelf eerder voor een tabel 'persons_functions' gaan. Op die manier kun je gewoon: SELECT person_id FROM persons_functions WHERE function_id = 1.

[Cavorka]

Ik ben het daar mee eens, de 'oplossing' die je nu probeert te maken is niet handig. Je moet gewoon je database normaliseren. Nu moet je al voor elke selectie een full-table search (met of zonder indices) gaan doen. Als je een veld gaat maken waar dus alle waardes in staan (01101 bijv.) dan MOET je alle velden op gaan halen als mensen zoekt met een bepaalde rol. Lijkt me niet echt handig. Het hele idee achter een database gaat zo verloren.

[Spooksel]

Cavorka schreef op 24 oktober 2004 @ 17:52:
Ik ben het daar mee eens, de 'oplossing' die je nu probeert te maken is niet handig. Je moet gewoon je database normaliseren. Nu moet je al voor elke selectie een full-table search (met of zonder indices) gaan doen. Als je een veld gaat maken waar dus alle waardes in staan (01101 bijv.) dan MOET je alle velden op gaan halen als mensen zoekt met een bepaalde rol. Lijkt me niet echt handig. Het hele idee achter een database gaat zo verloren.

Daarom ben ik dus op zoek naar een query die in MySQL alleen die rijen eruit pikt waarbij het veld "rollen" voldoet. De methode met de 1en en 0en die ik hierboven omschreef was meer een indicatie om aan tegeven hoe de controle achteraf met php gedaan kan worden. Ik zoek de methode om dit vooraf in MySQL direct te doen.

Ik zie het nut er niet van in om de rollen uit de tabel te normaliseren als ik direct de goeie selectie zou kunnen maken zonder alles op te halen!

[ripexx]

Spooksel schreef op 24 oktober 2004 @ 17:59:
[...]
Daarom ben ik dus op zoek naar een query die in MySQL alleen die rijen eruit pikt waarbij het veld "rollen" voldoet. De methode met de 1en en 0en die ik hierboven omschreef was meer een indicatie om aan tegeven hoe de controle achteraf met php gedaan kan worden. Ik zoek de methode om dit vooraf in MySQL direct te doen.

Niet doen, zodra je gaat selecteren op een substring kan ke geen indexen gebruiken dus houdt het al snel op en zal er dus een volledige table scan gedaan moeten worden. Zowel qua overzicht, onderhoudtbaarheid, logica en snelheid is het gewoon geen idee.

Ik zie het nut er niet van in om de rollen uit de tabel te normaliseren als ik direct de goeie selectie zou kunnen maken zonder alles op te halen!

Dit kan niet goed in MySQL of een andere database, vandaar dat er zoiets als normaliseren is Vandaar dat men gewoon werkt met joins etc. Dus twee of drie tabellen maken:

code:

users
User id
Naam
etc.
---------
user_rol
User id
Rol id
----------
Rol
Rol id
Naam
Omschrijving

SQL:

SELECT user.user_id, user.naam 
FROM user 
    LEFT JOIN user_rol
        ON user.user_id = user_rol.user_id
    LEFT JOIN rol
        ON user_rol.rol_id = rol.rol_id
WHERE rol.naam = 'bestuur';

[Verwijderd]

Spooksel schreef op 24 oktober 2004 @ 17:59:
[...]
Ik zie het nut er niet van in om de rollen uit de tabel te normaliseren als ik direct de goeie selectie zou kunnen maken zonder alles op te halen!

1) Als je dit normaliseert, dus rollen in een aparte tabel onderbrengt, en je indexen goed zet, werkt je systeem veel sneller
2) Een genormaliseerde oplossing is veel flexibeler, aangezien je minder betekenis van data in de code zet (nl. de relatie tussen letter en rol). De rollen zijn makkelijker uit te breiden en je kan meer informatie over de rol zelf opslaan.
3) Niet normaliseren is verwarrend voor onderhoud van het systeem en zou alleen kunnen bestaan als er een heel goede reden voor is, en die heb ik nog niet gehoord.

[Verwijderd]

Een linkje dan maar:
http://dev.mysql.com/doc/mysql/en/String_functions.html

[Erkens]

Mocht je toch niet willen normaliseren dan raad ik je in dit geval aan om een SET te gebruiken

[Cavorka]

Spooksel schreef op 24 oktober 2004 @ 17:59:
[...]Daarom ben ik dus op zoek naar een query die in MySQL alleen die rijen eruit pikt waarbij het veld "rollen" voldoet. De methode met de 1en en 0en die ik hierboven omschreef was meer een indicatie om aan tegeven hoe de controle achteraf met php gedaan kan worden. Ik zoek de methode om dit vooraf in MySQL direct te doen.

Ik zie het nut er niet van in om de rollen uit de tabel te normaliseren als ik direct de goeie selectie zou kunnen maken zonder alles op te halen!

Moet je even voor de grap gaan nadenken wat je zou moeten doen als:
- je een extra rol wilt aanmaken
- een rol wilt tussenvoegen (tussen twee rollen in)
- een rol wilt verwijderen

Bij elke actie moeten alle rijen worden geupdate. Ouch.

[Spooksel]

Ik heb besloten om te gaan voor een genormaliseerde versie met 3 tabellen zoals omschreven door ripexx.

Ik heb nog zitten twijfelen om de door mij 'bedachte' methode te gebruiken met een veldje waarin 'mdb' staat waaruit ik met "rol LIKE '%d%' zou kunnen werken. Maar dan kom ik dus in de knoop met eventuele situaties zoals omschreven door Cavorka.