[php/mysql] Dynamisch selecteren van fields

[fly]

Ik heb het volgende situatie en wil graag een goede start maken, vandaar dat ik even hardop aan het denken ben en hopelijk kan iemand hier tegen me zeggen of ik het goed denk aan te pakken of dat een andere manier velen malen beter is.

Ik moet een systeem maken waar de eindegebruiker op het laatst zelf zijn klantentable kan uitbreiden.
In de klantentable moet als basis klantid, email, naam, actief of niet komen. De rest moet allemaal achteraf uitbreidbaar zijn.

Als oplossing zit ik te denken een installatiescriptje waar de eindgebruiker de extra NAW gegevens kan toevoegen. Hierin komt bijv. adres, postcode en tel erbij.
Na het invoeren maakt de script in de klanten table die volgende kolommen erbij en daarnaast voert de script in een in een aparte table de kolom namen.

Het probleem is het dynamisch selecteren van de kolommen in de klantentable zonder dat ik weet hoe ze later heten. In principe wil ik niet alle kolommen uit de klantentable constant hebben, en omdat ik niet weet hoe ze uiteindelijk heten heb ik een table met de extra kolom namen.
Uit deze table haal ik de kolomnamen die erbij zijn gekomen en vervolgens zet ik het om naar een array met daarin die kolomnamen. In mijn sql voor de klantendatabase gebruik ik deze array in mijn select statement om zo alle velden te vinden die ik nodig heb.

Nou is mijn vraag of dit een slimme idee is of dat er een betere methode is?

[RedHat]

Gewoon een tabel met de kolomnaam, het type, de "regels" ?

Maar of het handig is weet ik niet. Waarom niet gewoon postcode etc al integreren? Je hoeft ze niet Persé te gebruiken.

[Gomez12]

Simpele vraag, waarom wil je deze velden als kolommen hebben?
Als je ze echt dynamisch wilt hebben dan maak je er geen kolommen van, maar een extra tabel met 2 kolommen ( header / value ), dan maak je 1 koppeltabel tussen je extra tabel en je standaard tabel en je bent klaar om te gaan....

Dynamisch je kolommen uitbreiden is imho wachten op een ramp, je dbase model hoort vast te liggen en performant te zijn ( hoe wil je een index leggen op een kolom waarvan je de naam nog niet eens weet etc ) en je moet niet tegen bugs als een max aantal kolommen aanlopen als de klant iets teveel invoert.

Google eens op hoe je attributen in een relationele dbase opslaat, daar kom je een heel stuk verder mee gok ik. Die zijn ook niet vast ( in aantal ) .

Feitelijk is het enige waar ik dynamische tabellen ( dus kolommetje erbij / eraf vanuit code ) gebruik een staging area waar de data eventjes staat en indien goed bevonden dan wordt het doorgezet naar het standaard relationele model.

[Fish]

geef hem dezelfde tools die jij gebruikt maar andere rechten op de database/tabellen/kolommen ?

Anders,
Als met de extra info geen ingewikkkelde dingen moeten gebeuren zou je een enkele extra kolom kunnen overwegene waar al die extra data "seperated" instaat.
Beetje (erg) lelijk, maar relatief simpel. en staat los van de rechten en de database zelf

de huidige koppeltabel is "netter"

[ Voor 66% gewijzigd door Fish op 28-04-2009 21:10 ]

[fly]

Het systeem is bedoeld voor een gebruiker die verschillende klanten heeft met elk een eigen klanten syteem.
Omdat elk klanten systeem andere NAW gegevens heeft moet het dynamisch kunnen zijn.

Ik probeer het in 1 klantentable te zetten omdat het vertrekpunt van mijn eindgebruiker is dat hij/zij de database klaarzet voor een klant. Klant x kan 4 extra kolomen nodig hebben en klant y 2. Maar ik wil het liefst niet de kolommen van te voren defineren als blanco kolommen omdat ik de totaal set van kolommen voor veel meer dingen gebruik. En omdat ik het voor meerder doeleinde gebruik wil ik het liefst het in 1 table kunnen zetten met kolomnamen zodat ik niet verschillende complexe joins hoeft te maken voor gegevens die ik niet weet.

[Janoz]

Kun je niet beter een generieker model gebruiken en dan voor elk van de klanten een convertor schrijven die van dit generieke model naar het specefieke klantmodel gaat en omgekeerd? Op die manier heb je het dynamische deel beperkt tot enkel de koppeling en heb je er in de rest van je applicatie geen last van. Het enige lastige van deze oplossing is om een goed generiek klant model te maken.

[fly]

Is het mogelijk om in mijn sql een soort van field select te maken op alles behalve fields x, y, z ofzo?
Want eigelijk wil ik alle velden behalve een paar basis velden.
Op deze manier kan ik ook gelijk die extra velden automatisch laten meenemen in mijn sql.

[Fish]

Ga anders naar een model toe waar een extra eigenschap geen extra kolom is maar een extra regel


voorbeeld (nu)

code:

klant             Naam    adres      postcode   eigenschap x  eigenschap y

company xyz       jan     joepie1    1000AA     blauwe teen
bedrijf abc       kees    lala2       999zz                   veel boetes


naar

klant                 eigenschap      waarde_eigenschap

company xyz       jan      adres           joepi1
company xyz       jan      postcode        1000aa
company xyz       jan      eigenschap x    blauwe teen             
bedrijf abc       kees     adres           lala2
bedrijf abc       kees     postcode        999zz
bedrijf abc       kees     eigenschap y    veel boetes

naam kan ook een referentie(id) zijn naar eventuele de standaard gegevens van die persoon bij een klant

[ Voor 10% gewijzigd door Fish op 28-04-2009 21:34 ]

[Gomez12]

fly schreef op dinsdag 28 april 2009 @ 21:11:
Het systeem is bedoeld voor een gebruiker die verschillende klanten heeft met elk een eigen klanten syteem.
Omdat elk klanten systeem andere NAW gegevens heeft moet het dynamisch kunnen zijn.

Verzin dan het meest uitgebreide NAW model wat je kan vinden en implementeer dat met alle kolommen ( door een optiestabel ) optioneel / verplicht te stellen door de klant.

NAW gegevens zijn redelijk eenduidig, ik krijg meer het idee dat je gewoon opties / attributen bij de NAW-gegevens probeert te trekken terwijl deze daar gewoon niets mee te maken hebben.

Qua NAW-gegevens zou ik me kunnen voorstellen dat bijv een land optioneel is ( de ene klant handelt enkel nationaal, de andere internationaal ) maar dan zou ik meer denken aan defaults en dat de land-kolom toch ook bij nationale klanten aanwezig is.
Misschien dat je eens een voorbeeldje kan geven van optionele kolommen want ik zie ze niet echt bij NAW gegevens.

Maar bovenal, ga het niet 1 extra kolom separated douwen. Daarmee ondermijn je alle performance en het hele idee van een relationele database. Als je hiervoor kiest ga je jezelf in de toekomst echt nog een keer voor je kop slaan.

Ik probeer het in 1 klantentable te zetten omdat het vertrekpunt van mijn eindgebruiker is dat hij/zij de database klaarzet voor een klant. Klant x kan 4 extra kolomen nodig hebben en klant y 2. Maar ik wil het liefst niet de kolommen van te voren defineren als blanco kolommen omdat ik de totaal set van kolommen voor veel meer dingen gebruik.

Nogmaals, kan je hier een voorbeeldje van geven, want ik krijg echt het idee dat je gewoon verkeerd redeneert ( of tenminste ik kan me geen reden bedenken waarom ik de totale set van kolommen nodig zou hebben )

En omdat ik het voor meerder doeleinde gebruik wil ik het liefst het in 1 table kunnen zetten met kolomnamen zodat ik niet verschillende complexe joins hoeft te maken voor gegevens die ik niet weet.

Tsja, een relationele database is zo ongeveer gemaakt voor joins, als complexe joins je afschrikken dan zou ik me nog maar eens gaan afvragen of je het wel in een relationele db wilt hebben.

fly schreef op dinsdag 28 april 2009 @ 21:24:
Is het mogelijk om in mijn sql een soort van field select te maken op alles behalve fields x, y, z ofzo?
Want eigelijk wil ik alle velden behalve een paar basis velden.
Op deze manier kan ik ook gelijk die extra velden automatisch laten meenemen in mijn sql.

Simpele tip, denk andersom, welke velden heb je wel nodig selecteer die.
Niet uitgaan van ik heb alles nodig behalve, maar uitgaan van ik heb x,y,z nodig.

[ Voor 10% gewijzigd door Gomez12 op 28-04-2009 21:37 ]

[Kwastie]

In jou geval is er een grote kans dat je het database model moet wijzigen.

Het wijzigen van een database model is altijd fout (dit betekend dat er niet goed over nagedacht is.) Natuurlijk tijdens de ontwikkeling van je applicatie kom je altijd dingen tegen die achteraf niet had kunnen weten. Maar zou gauw een applicatie in 'productie' draait moet je nooit iets wijzigen aan je database.

fly schreef op dinsdag 28 april 2009 @ 21:11:
Ik probeer het in 1 klantentable te zetten omdat het vertrekpunt van mijn eindgebruiker is dat hij/zij de database klaarzet voor een klant.

Waarom blijf je steken op ik wil 1 tabel? Als je gebuikt maakt van 2 tabellen word het juist dynamisch uitbreidbaar.

fly schreef op dinsdag 28 april 2009 @ 21:11:
zodat ik niet verschillende complexe joins hoeft te maken voor gegevens die ik niet weet.

Ik vindt dit een slecht argument. Er hoeft maar 1 join te worden gemaakt op de 2 tabellen en deze is NIET complex.

SQL:

SELECT *
   FROM klanten 
INNER JOIN parameters
  ON(klanten.id = parameters.id)

Het database model ga ik hier niet voorlopen kauwen, ik hoop dat je hier zelf wijs genoeg bent om dit uit te zoeken:

Edit:

fish schreef op dinsdag 28 april 2009 @ 21:27:
Ga anders naar een model toe waar een extra eigenschap geen extra kolom is maar een extra regel

voorbeeld (nu)
*knip*
naam kan ook een referentie(id) zijn naar eventuele de standaard gegevens van die persoon bij een klant

Stel je zou 5 extra velden willen toevoegen betekend dit dat je 10 extra columns moet toevoegen ? Lijkt me toch niet zo handig. Een "leuk" bijkomend probleem is dat je nooit de input van de gebruiker kan valideren.

Bijvoorbeeld: Je voegt het veld 'Geboortedatum' toe, in dit geval kan de gebruiker 'blaat' invullen en word het opgeslagen in database, want je kan niet valideren of een Geboortedatum een datum is omdat het systeem NIET weet weer hij op moet valideren

Dit probleem zou (waarschijnlijk) niet (zo snel) hebben als je gebruik maakt van 2 tabellen simpelweg omdat je simpel een 'extra' column kan toevoegen.

Het is nu eigenlijk al gewenst om een 3e tabel aan te maken waarin je validatie types opslaat, omdat een validatie type zéér waarschijnlijk vaker voorkomt(Normaliseren). Maar het is al moeilijk genoeg zo, dus laat ik het buiten beschouwing:+

Leesvoer:
Wikipedia: Entity-relationshipmodel
Wikipedia: Databasenormalisatie
W3schools: Sql Joins

[ Voor 37% gewijzigd door Kwastie op 28-04-2009 22:20 . Reden: Reactie na aanleiding plaatsing tabel 'layout' ]

[fly]

Allereerst hartelijk dank voor alle hulp, misschien verwoord ik het soms verkeerd, er staat nog niets vast, ik zit alleen na denken hoe ik dit het beste kan oplossen.

Voor het klantenbestand had ik al gedacht om meerdere tabellen te gebruiken, een basis met klantid en active (yes/no) de laatste is nodig om te weten of de klant gebruikt mag worden of niet.

Daarnaast de "NAW" gegevens table met daarin klantid, email, voornaam, achternaam en tot zover ik weet atm de extra's: adres, postcode (belangrijke!), stad, provincie, land, telefoon, fax.

Alles is wel op te lossen door deze NAW gegevens hard en vast te leggen. Maar aan deze velden zit ook de invoerveld en die wordt bepaald door welke velden nodig is bij de klant. En aan die invoerveld zit dus ook voor elke kolom de interne fieldnaam (database naam) en externe fieldnaam (benaming bij de invoerform).
Een aparte table voor het aan en uit defineren van een gegevens kolom zou dit oplossen. Maar ik wou flexibiliteit behouden omdat ik niet weet of er bijv geboortedatum, sofinummer, etc. bij kan komen...

Wat ik ook moet doen is op de "naw" table een filter/zoek functie maken. Gelukkig hoeft deze niet zo uitgebreidt. Ik hoef alleen te defineren of een kolom een string of een number is. bij string is het bijv. zoeken naar alla like en bij een number; gelijk aan of tussen de range 1000 en 2000 bijv.

Omdat ik ook de die dingen moet defineren dacht ik dit op te lossen door te combineren in een table dan bij de kolomnamen neer te zetten of ze zoekbaar zijn via string of number en ook hoe ze voor de buitenwereld aangeroepen hoort te worden (in de database heet een field misschien straatnaam en de eindgebruiker wilt misschien wel Straat naam ofzo). Maargoed dit voelde voor mij niet echt aan als de beste methode vandaar dat ik hulp hier zocht.

Ik ben ook niet bang voor een paar joins maar het kan zomaar zijn dat ik straks 5 tot 6 tables nodig heb. Vandaar dat ik het wou gaan minimaliseren. Maargoed in mijn model zou ik dan bij een uitbreiding van de naw gegevenstable ook andere tabellen moeten bijwerken die eigenlijk de settings van de naw kolommen bijhoudt (wel of niet aan, welke filtertype, extern aanspreeknaam etc.). Vandaar dat ik waarschijnlijk krom nadenk. Maar ik zie het licht nog niet helemaal ...

[ Voor 10% gewijzigd door fly op 28-04-2009 22:56 ]

[Patriot]

Uhm, voor zover ik je kan begrijpen ben je juist heel erg goed bezig. Je lijkt goed door te hebben hoe een database in elkaar hoort te zitten, en bent in je hoofd volgens mij ook al bezig met normaliseren (ook al weet je wellicht nog niet wat dat is).

Je gaat in ieder geval de goede richting uit. Je hebt een klantentabel met daarin eigenlijk niets meer dan een id en veldje om soft-deletes te ondersteunen. Vervolgens een tabel met velden waarin je eigenlijk alleen de velden en hun eigenschappen wilt vastleggen, daar komt geen gebruiker bij te kijken. Als laatst heb je een tabel waarin je het id van de gebruiker en het id van het veld in stopt, en vervolgens de waarde die daarbij hoort.

Lijkt mij een prima systeem, en het is zeker flexibel. Er zijn vast nog dingen op aan te merken, maar in grote lijnen is dit volgens mij een prima databaseontwerp.

[Gomez12]

@Patriot : Ik ga ervanuit dat je het stukje in de TS gemist hebt over het script dat er kolommen bijmaakt?
Hij wil alles juist zoveel mogelijk in 1 tabel stouwen, desnoods vergroot hij die tabel wat....

[Patriot]

Gomez12 schreef op woensdag 29 april 2009 @ 02:51:
@Patriot : Ik ga ervanuit dat je het stukje in de TS gemist hebt over het script dat er kolommen bijmaakt?
Hij wil alles juist zoveel mogelijk in 1 tabel stouwen, desnoods vergroot hij die tabel wat....

Dat is omdat hij niet wist of de oplossing die hij in zijn laatst post gaf de beste was. Hij kwam toen met die oplossing van dat scriptje. In zijn laatste post legde hij echter uit over welke oplossing twijfelde, en daarom legde ik uit dat dat voor zover ik kon zien juist een uitstekende oplossing was. En daar kwam geen scriptje bij te kijken die automatisch tabellen vergroot, hij had het vrij specifiek over drie tabellen.

[fly]

Zo na een werkdag heb ik weer tijd om met dit projectje verder te gaan doktoren.

Het punt dat ik voorzie en nog steeds zie in mijn model is dat ik een table nodig heb die de kolomnamen van de "naw" gegevens table gebruikt.

Want table naw heeft verschillende kolommen; voornaam, achternaam
table specificatie heeft dan; gegevenskolomnaam, zoektype, actief, extern

code:

Table Naw
klantid     voornaam    achternaam      postcode    

1       jan     joepie1     1000AA
2       kees        lala2       9999zz

Table Nawspecificatie
Db_field    Zoektype    actief      extern_call

voornaam    string      ja      Voornaam
achternaam  string      ja      Achternaam
postcode    number      nee     Zipcode

Tabel Naw zou ik dus alvast kunnen opvullen met een aantal kolommen zoals adres, telefoon, etc.
maar dan heb je het alsnog het punt dat als je wilt gaan uitbreiden (omdat ik niet weet wat erbij kan komen) je in de nawtable een kolom erbij doet en in table nawspecificatie hem defineer. Vandaar dat het actief wel of niet ding volgens mij niet hoeft. En bij de installatiescript zou je dus de uitbreidingen kunnen defineren. Alleen moet het hele systeem dus telkens selects maken op basis van de aantal gedefineerde kolommen

Vandaar dat ik denk dat ik een dynamische selection nodig heb om te weten welke kolommen ik moet uitlezen, iets van:

SQL:

 SELECT db_field FROM nawspecificatie WHERE actief = 'ja' 

en dan de query loopen naar een string met een , ertussen ( voornaam, achternaam, postcode) zodat ik die string in mijn select statement kan gebruiken bij de naw table (misschien dat dit stukje makkelijker en direct via sql kan hoor via joins, hang me der niet aan op).. Maar dan wijk ik totaal niet af van mijn begin situatie... wat volgens jullie niet zo slim is.
Volgens mij heb je al bij het punt dat je een andere table gebruikt die specificeer of een kolom in de nawtable gebruikt mag worden een table met een kolom die de kolomnamen van de nawtable moet hebben... of zie ik dit fout?

Maargoed ik weet dus nog steeds niet echt of dit de beste oplossing is. Misschien dat ik heel scheef nadenk en een andere db model velen malen beter is om in te zetten voor mijn situatie?

[Fish]

Kwastie schreef op dinsdag 28 april 2009 @ 21:47:
[...]


Stel je zou 5 extra velden willen toevoegen betekend dit dat je 10 extra columns moet toevoegen ? Lijkt me toch niet zo handig. Een "leuk" bijkomend probleem is dat je nooit de input van de gebruiker kan valideren.

Bijvoorbeeld: Je voegt het veld 'Geboortedatum' toe, in dit geval kan de gebruiker 'blaat' invullen en word het opgeslagen in database, want je kan niet valideren of een Geboortedatum een datum is omdat het systeem NIET weet weer hij op moet valideren

Dit probleem zou (waarschijnlijk) niet (zo snel) hebben als je gebruik maakt van 2 tabellen simpelweg omdat je simpel een 'extra' column kan toevoegen.

Het is nu eigenlijk al gewenst om een 3e tabel aan te maken waarin je validatie types opslaat, omdat een validatie type zéér waarschijnlijk vaker voorkomt(Normaliseren). Maar het is al moeilijk genoeg zo, dus laat ik het buiten beschouwing:+

Leesvoer:
Wikipedia: Entity-relationshipmodel
Wikipedia: Databasenormalisatie
W3schools: Sql Joins

Nee natuurlijk niet, dit zijn extra rows. en staat compleet llos van de opzet van je tabel

De validate van een eigenschap verdient inderdaad zijn eigen tabel. waar helemaal niets mis mee is
je hoeft echt niet alles in een tabel te proppen.

[ Voor 6% gewijzigd door Fish op 29-04-2009 18:15 ]

[fly]

Zo na wat biertjes op en even een terugblik naar al het commentaar ga ik mijn model als volgt opzetten:

code:

Table klanten
klantid email          actief

1           [e-mail]  yes
2           [e-mail]  yes

Table klanten_naw
klantid naw_type_id naw_value   

1       1       jan
1       2       Heide
1       3       3527XX

2       1       Piet
2       2       Vaak
2       3       3237XX


Table klanten_naw_specificaties
naw_type_id naw_name    searchtype  

1       voornaam    string
2       achternaam  string
3       postcode    number

Hiermee staat mijn DB model in iedergeval vast, validatie wordt gewaarborgd door in specificaties 1 malig iets te laten invoeren bij de setup.

Met dank aan fish

[Fish]

np, Kwastie kwam wel met een valide punt, Validatie

Je klanten_naw_specificaties tabel verdient wel een extra kolom, validation_mask
Waar je geldige invoer patronen in kan frotten.

Tevens denk ik dat je dan (misschien) ook nog even klant kolom moet maken
Ik noem maar iets stoms stel dat één van je klantenn niet om kan gaan met namen langer dan 30 letters

[ Voor 41% gewijzigd door Fish op 30-04-2009 22:33 ]

[Gomez12]

@fly : Tja het is een werkwijze.

Ik zelf zou er voor kiezen om je klanten tabel uit te breiden met standaard (altijd aanwezige ) naw gegevens.
Enkel voornaam en achternaam achter elkaar tonen levert nu al een join op.

Met deze denkwijze kan je elke tabel opsplitsen naar meerdere 3 kolommen tabellen. ( of je kan het nog 1 stap verder brengen en alles omzetten naar 2 kolommen tabellen en dan heb je een paar extra koppeltabellen nodig )

Er zijn nog meer normalisatie stappen waarin gezegd wordt dat je bij elkaar horende data weer bij elkaar kan brengen.

[fly]

Gomez12 schreef op donderdag 30 april 2009 @ 22:49:
@fly : Tja het is een werkwijze.

Ik zelf zou er voor kiezen om je klanten tabel uit te breiden met standaard (altijd aanwezige ) naw gegevens.
Enkel voornaam en achternaam achter elkaar tonen levert nu al een join op.

Met deze denkwijze kan je elke tabel opsplitsen naar meerdere 3 kolommen tabellen. ( of je kan het nog 1 stap verder brengen en alles omzetten naar 2 kolommen tabellen en dan heb je een paar extra koppeltabellen nodig )

Er zijn nog meer normalisatie stappen waarin gezegd wordt dat je bij elkaar horende data weer bij elkaar kan brengen.

Ik kan inderdaad de standaard naw gegevens in de klanten tabel zetten. Alleen gebruikt niet elke klant alle kolommen. Ik zou dan ook ergens moeten defineren welke kolommen de klant kan gebruiken. Maar dan krijg je weer dat ik een table nodig heb met 2 kolommen: kolomnaam, actief (y/n).
Zoektype hoef niet per see ook aan deze table, die kan ik wel hardcoded maken omdat dit dan standaard toch vast zit.
Kan je trouwens een voorbeeld geven hoe je die opsplitsing kan maken?

@fish, ik zal validation_mask meenemen, opzich staat dat ook wel vast aan de zoektype.

[Gomez12]

fly schreef op vrijdag 01 mei 2009 @ 08:58:
[...]


Ik kan inderdaad de standaard naw gegevens in de klanten tabel zetten. Alleen gebruikt niet elke klant alle kolommen. Ik zou dan ook ergens moeten defineren welke kolommen de klant kan gebruiken. Maar dan krijg je weer dat ik een table nodig heb met 2 kolommen: kolomnaam, actief (y/n).
Zoektype hoef niet per see ook aan deze table, die kan ik wel hardcoded maken omdat dit dan standaard toch vast zit.
Kan je trouwens een voorbeeld geven hoe je die opsplitsing kan maken?

@fish, ik zal validation_mask meenemen, opzich staat dat ook wel vast aan de zoektype.

Wat bedoel je met niet elke klant gebruikt alle kolommen?
Iedere klant gebruikt toch voornaam / achternaam / adres kolommen, desnoods is het optioneel, desnoods wordt het via een template niet getoond. Dit gooi je in je klantentabel.

Dingen als attributen die per site kunnen verschillen ( weet ik veel, neem even lievelingskleur als voorbeeld ) die sla je wel op de eerder genoemde manier op.

Niet gebruikte kolommen in je klantentabel hebben in je dbase gewoon een NULL waarde en neem je gewoon niet mee in je templates.

Je dbase hoeft niet per se aan te geven of een kolom actief is of niet, als je templates het gewoon niet tonen is het ook daadwerkelijk inactief...