[SQL] Meerdere waardes in één cel

[Mei]

Ik moet in een tabel instellingen voor een bepaalde feature opslaan. Aan elke set instellingen kunnen meerdere ID's gekoppeld worden. Wat ik mij afvraag is hoe ik het beste die ID's in de database kan opslaan. De eis is dat ze wel doorzoekbaar moeten blijven, dus als geserializede array opslaan is geen optie. Waar ik zelf op was gekomen was het imploden van de array met ID's met een komma ertussen en een komma aan het begin en einde van de string (die zijn nodig zodat je op ID = ',9,' kan zoeken en dat een ID van 19 niet teruggeven wordt). Ik heb in een ander topic op GoT echter gelezen dat het bad practice is om meerdere waardes in één cel te stoppen. Als ik dat niet zou doen, dan zou ik per ID een row aan moeten maken, maar dan worden de instellingen meerdere malen opgeslagen als er aan een set instellingen toevallig meerdere ID's verbonden zijn.

Vanuit het standpunt van efficiëntie gezien zou ik voor het imploden kiezen, maar misschien is er nog een betere manier. Ik heb net al gezocht op speciale SQL velden voor meerdere waardes, maar de enige die ik kon vinden is SET; het probleem daarmee is dat de waardes vooraf gedefiniëerd moeten worden.

[RobIII]

Gewoon een koppeltabel gebruiken; meer waardes in 1 veld frotten gaat per definitie en altijd gegarandeerd en geheid mis. Niet aan beginnen, niet over twijfelen, niet eens aan denken. Basta.

Mei schreef op zondag 04 mei 2008 @ 21:11:
Als ik dat niet zou doen, dan zou ik per ID een row aan moeten maken, maar dan worden de instellingen meerdere malen opgeslagen als er aan een set instellingen toevallig meerdere ID's verbonden zijn.

So? Bang dat je database opeens moeite zou hebben met 2 tot 10 rows meer (zeker als er enkel int-id's in staan)?? Een beetje DB wordt niet eens warm of koud van miljoenen tot miljarden records (mits fatsoenlijk ingericht); die paar rows zullen 'm de nek niet om doen hoor

Mei schreef op zondag 04 mei 2008 @ 21:11:
Vanuit het standpunt van efficiëntie gezien zou ik voor het imploden kiezen, maar misschien is er nog een betere manier.

Vanuit het standpunt van efficiëntie moet je dus gewoon een koppeltabel gebruiken; je hebt immers een n:m relatie (als ik het goed begrijp).

Mei schreef op zondag 04 mei 2008 @ 21:11:
Ik heb net al gezocht op speciale SQL velden voor meerdere waardes, maar de enige die ik kon vinden is SET; het probleem daarmee is dat de waardes vooraf gedefiniëerd moeten worden.

SET is (bij mijn weten) MySQL-only (of in ieder geval niet erg gangbaar) en (o.a.) beperkt tot 64 members en (IMHO) net zo ranzig als zelf comma-separated meuk in een varchard veld gooien en daarmee je datatype misbruiken voor dingen waar het niet voor bedoeld is.

[ Voor 90% gewijzigd door RobIII op 04-05-2008 21:18 ]

[Gomez12]

Koppeltabel?

Toch komisch dat als je 1 tabblad 1 minuut te lang open laat staan dat er dan iemand tussen komt waardoor je hele simpele reply betreffende waarom niet opeens als vraag om uitleg opgevat wordt

[ Voor 89% gewijzigd door Gomez12 op 04-05-2008 21:32 ]

[Mei]

Gomez12 schreef op zondag 04 mei 2008 @ 21:14:
Koppeltabel?

Je slaat de instellingen op in de ene tabel met een speciale ID die bij die set instellingen hoort. In een tweede tabel link je de ID's die ik wil weten aan de instellingenID.

Dit klinkt inderdaad wel als een nette oplossing, alhoewel het me geheid weer extra queries gaat kosten (Of niet, maar ben niet zo'n SQL held, dus moet even goed gaan kijken hoe ik dit ga doen)

In ieder geval bedankt voor de input

[ Voor 28% gewijzigd door Mei op 04-05-2008 21:16 ]

[Creepy]

Gomez12: RobIII bedoeld daarmee dat er een n:m relatie gelegd moet worden tussen de instellingen en de tabel waar de ID's vandaan komen. In de regel doe je dat met een extra tabel zodat je het ID van de ene tabel aan de andere kan koppelen, vandaar in de volksmond ook wel: een koppeltabel

@Mei: Met twee joins kan je alle drie de tabellen in 1 query opvragen.

[ Voor 11% gewijzigd door Creepy op 04-05-2008 21:17 ]

[flowerp]

RobIII schreef op zondag 04 mei 2008 @ 21:13:
Gewoon een koppeltabel gebruiken; meer waardes in 1 veld frotten gaat per definitie en altijd gegarandeerd en geheid mis. Niet aan beginnen, niet over twijfelen, niet eens aan denken. Basta.

In de meeste gevallen is dat inderdaad zo. Toch is er af en toe wel eens reden om hiervan af te wijken. Er bestaat b.v. zoiets als het Array type en dat is natuurlijk ook niet helemaal voor niets

(los van het feit natuurlijk of de TS die wel of niet moet gebruiken )

[Mei]

Volgens mij bestaan er geen ARRAY fields. Kan ze via Google althans niet vinden.

[Verwijderd]

ik zou zeggen, 1 row en 1 column. meer heb je niet nodig in je db.. alles csv er in plempen ;P

wat jij wilt is idd gewoon die koppel tabel.

dus een feature tabel. een instellingen tabel en een koppeltabel.

table feature
featid
featname
etc

table settings
settingid
settingname
etc

table featuresettings
featid
settingid
value

[flowerp]

Mei schreef op zondag 04 mei 2008 @ 23:13:
Volgens mij bestaan er geen ARRAY fields. Kan ze via Google althans niet vinden.

Uhm, zit gewoon in standaard SQL3 hoor:

The ISO (International Organization for Standardization) and IEC (the International Electrotechnical Commission) have defined data types that are commonly referred to as SQL3 types. Of these SQL3 data types, BLOB, CLOB, ARRAY, and REF are predefined types, whereas the SQL structured type and the DISTINCT type are user-defined types (UDTs).

Je kunt ze zelfs multi-dimensionaal definiëren als je PG gebruikt.

SQL:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

Zie: http://www.postgresql.org/docs/8.3/interactive/arrays.html

De standaard SQL syntax is deze:

SQL:

CREATE TABLE loans (
   pay_by_quarter  integer ARRAY[4]
);

De JDBC mapping bestaat al sinds mensenheugenis, de SQL ARRAY mapped naar java.sql.Array. zie: http://java.sun.com/j2se/...dbc/getstart/mapping.html

[ Voor 7% gewijzigd door flowerp op 04-05-2008 23:55 ]

[Mei]

Het moet zowel in MySQL als PGSQL werken.

[Fish]

koppel tabel, domweg het makkelijkste en het overzichtelijkst

[pedorus]

flowerp schreef op zondag 04 mei 2008 @ 23:51:
Zie: http://www.postgresql.org/docs/8.3/interactive/arrays.html

Belangrijk stukje inhoud hier:

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.

Mei schreef op maandag 05 mei 2008 @ 00:08:
Het moet zowel in MySQL als PGSQL werken.

En MySQL heeft bij mijn weten (nog) geen SQL3 ARRAY.

[Verwijderd]

pedorus schreef op maandag 05 mei 2008 @ 12:21:
En MySQL heeft bij mijn weten (nog) geen SQL3 ARRAY.

Das raar. Loopt MySQL dan zover achter? Dit type bestaat toch al best een tijdje...

[RobIII]

Verwijderd schreef op maandag 05 mei 2008 @ 14:03:
[...]


Das raar. Loopt MySQL dan zover achter? Dit type bestaat toch al best een tijdje...

Bij mijn weten implementeren maar bar weinig RDBMSen dit datatype. Maar los daarvan is het IMHO nog steeds een ranzige workaround voor het probleem van TS; zeker als je even naleest wat pedorus erover post.

[ Voor 28% gewijzigd door RobIII op 05-05-2008 14:15 ]

[Mei]

Waar ik nu tegenaan loop is het volgende: Hoe selecteer ik zonder een hele lading queries alle items uit mijn originele tabel, samen met de ID's uit de koppeltabel? Ik wil dus per row uit de originele tabel een object met in en property een array met de ID's die in de koppeltabel staan. Nou kan ik heel quick en dirty in één query de rows uit de originele tabel ophalen en dan per row met een query de ID's uit de koppeltabel, maar dan zit je dus met n+1 queries; niet echt lekker voor de performance.

Iemand een idee?

[MMUilwijk]

Met behulp van een left join? SQL tutorial

Liever een left join dan een inner join, omdat je naar mijn gevoel in ieder geval alle items uit de basistabel wil meenemen en wanneer aanwezig ook de gekoppelde ID's. Met een inner join mis je namelijk de items uit de basistabel die geen koppelingen hebben.

[RobIII]

Mei schreef op donderdag 08 mei 2008 @ 09:28:
Waar ik nu tegenaan loop is het volgende: Hoe selecteer ik zonder een hele lading queries alle items uit mijn originele tabel, samen met de ID's uit de koppeltabel? Ik wil dus per row uit de originele tabel een object met in en property een array met de ID's die in de koppeltabel staan. Nou kan ik heel quick en dirty in één query de rows uit de originele tabel ophalen en dan per row met een query de ID's uit de koppeltabel, maar dan zit je dus met n+1 queries; niet echt lekker voor de performance.

Iemand een idee?

Misschien is het handig je eens te verdiepen in de basics van SQL, want (als ik je goed begrijp, het duurde even voor ik er een beetje hout van kon snijden) dit los je gewoon op met een join. En dat is na normaliseren les 3 of 4 SQL Als je dan zo'n tutorial doorneemt leer je ook een beetje de terminologie; wel zo handig als je een probleem wil uitleggen

[ Voor 6% gewijzigd door RobIII op 08-05-2008 09:43 ]

[Mei]

JOINS zijn het probleem niet. Het is de combinatie van het verwerken van de data in PHP/SQL. Ik heb dus in tabel settings een paar rows staan. In de tabel vid staan per row uit tabel settings een paar andere rows die via een LEFT JOIN aan elkaar gekoppeld kunnen worden. Ik kan zo gemakkelijk voor een enkele settings row de rows uit vid erbij halen. Ik weet echter niet hoe ik efficiënt alle rows uit settings kan halen en de bij elke settings horende rows uit tabel vid.

Schetsje van de kolommen:
Tabel settings:
viid, setting1, setting2, setting3

Tabel vid:
viid, vid

Als ik dan voor één row uit settings de boel eruit haal en LEFT JOIN met vid, dan krijg je zo'n result:
viid, vid, setting1, setting2, setting3
viid, vid1, setting1, setting2, setting3
viid, vid2, setting1, setting2, setting3
viid, vid3, setting1, setting2, setting3

Zoals je ziet verschilt de vid telkens. Dit is nog redelijk gemakkelijk in een object te zetten met een while loop die bij de eerste row de settings het object zet en verder per loop de vid in een array gooit. Het verdient niet de schoonheidsprijs, maar het werkt.

Wil je echter meerdere rows uit tabel settings hebben, dan krijg je zo'n resultaat:
viid, vid, setting1, setting2, setting3
viid, vid1, setting1, setting2, setting3
viid, vid2, setting1, setting2, setting3
viid, vid3, setting1, setting2, setting3
viid1, vid7, setting1, setting2, setting3
viid1, vid8, setting1, setting2, setting3
viid1, vid10, setting1, setting2, setting3
viid2, vid23, setting1, setting2, setting3

Als ik dit op de eerder genoemde manier in losse objecten (want per viid, viid1, viid2 enz een object) wil zetten, dan krijg je een loopconstructie die niet echt charmant is. Mijn vraag: Hoe kan ik dit efficiënt doen?

[Verwijderd]

Daar heb je hierarchische queries voor nodig als je dit in de database wilt oplossen. Voor zover ik weet ondersteund MySQL echter dergelijke queries niet.

[bigbeng]

Binnen de mogelijkheden van SQL kun je dit niet efficienter oplossen dan:
- alle resultaten in 1 query. Loop constructie om er kop-sub structuur in aan te brengen.
- eerst de hoofd-tabel leegtrekken en dan per hoofd-id de bijbehorende settings uitlezen.
- de volledige settings-tabel in het geheugen van je applicatie server trekken en toegankelijk maken op basis van hoofd-id.

Afhankelijk van de groottes van je hoofdtabel en settings tabel en/of de frequentie van uitvoeren van de queries is de ene oplossing "beter" dan de andere. Je zult dit moeten inschatten en dan een beslissing nemen.

Overigens zijn veel applicatie-talen prima in staat om de zorg over het ophalen van kop-sub data weg te nemen. Bijvoorbeeld DataSets in .NET.
Ook OR-mappers kunnen dit soort relaties prima managen.

[Verwijderd]

lama. had niet gezien dat je zover al wel was

[ Voor 88% gewijzigd door Verwijderd op 08-05-2008 20:37 ]

[flowerp]

Mei schreef op donderdag 08 mei 2008 @ 09:54:
Als ik dit op de eerder genoemde manier in losse objecten (want per viid, viid1, viid2 enz een object) wil zetten, dan krijg je een loopconstructie die niet echt charmant is. Mijn vraag: Hoe kan ik dit efficiënt doen?

Ook dit kun je weer met een array doen en een array aggregator.

Iets als:

SQL:

select *
from settings

left outer join
        
    (
    select viid, array_accum(vid) as vids 
    from vid 
    group by vid
    ) as all_vids       
using (viid)    

Je bovenstaande voorbeeld wordt dan iets als:

code:

viid, vid, setting1, setting2, setting3, {vid,vid1,vid2,vid3}
viid1, vid7, setting1, setting2, setting3, {vid7, vid8, vid10}

De laatste column is gewoon 1 array waar je in code gewoon ook als een array doorheen loopt.

De aggregator kan al standaard in je DB zitten, maar je kunt hem ook zo maken (direct uit de PG docs)

SQL:

CREATE AGGREGATE array_accum(
  BASETYPE=anyelement,
  SFUNC=array_append,
  STYPE=anyarray,
  INITCOND='{}'
);

[Mei]

Er is eerder uitgelegd dat arrays nog niet in MySQL zitten, dus die oplossing zal helaas niet gaan werken.

Ik heb nu al een koppeltabel met een loopconstructie in PHP gemaakt. Voordeel hiervan is dat ik de ID's uit die koppeltabel nog makkelijk weer kan joinen aan de tabel waar de data van die items in staat. Kan met een array-kolom weer niet.

PHP:

function vocabindex_vi_load($viid = 0, $all = FALSE) {
  static $vis = array();
  //We need to know if we have already tried to get all VIs. $vi is not reliable because it's empty if there are no VIs
  static $vis_isset = FALSE;
  
  if ($vis_isset == FALSE) {
    $current_viid = 0;
    //NOTE: join with vocabulary.description & vocabulary.name and calculate the amount of vocabs associated with each VI. In case there's one vocab and no VI desc and name, the vocab's data is used
    $result = db_query("SELECT * FROM {vocabindex_settings} vi_set LEFT JOIN {vocabindex_vid} vi_vid ON vi_set.vid = vi_vid.vid");  
    while ($row = db_fetch_object($result)) {
      //Only use the settings from the first row
      if ($row->viid != $current_viid) {
        $current_viid = $row->viid;
        $vi = $vis[$viid] = $row;
        $vi->$vid = array();
        
        //Convert some settings from integers to strings
        $vi->list_style = $vi->list_style == 0 ? 'threaded' : 'flat';
        $vi->terms_order = $vi->terms_order == 0 ? 'weight' : 'name';
        $vi->node_count = $vi->node_count == 0 ? 'disabled' : 'enabled';
        
        //Only when displaying flat lists the depth should be 2 (one to display, the other to determine if terms are parents)
        $vi->depth = $vi->list_style == 'flat' ? 2 : NULL;
      }
      $vi = $vis[$viid];
      $vi->vid[] = $row->vid;
    }
    //Set the flag
    $vis_isset = TRUE;
  }
  
  if ($all == TRUE) {
    return $vis;
  }
  else {
    return $vis[$viid];
  }
}

[flowerp]

Mei schreef op vrijdag 09 mei 2008 @ 10:48:
Er is eerder uitgelegd dat arrays nog niet in MySQL zitten, dus die oplossing zal helaas niet gaan werken.

Ik begreep dat MySQL geen array als column definitie kent, maar ook niet in de resultset? Hoe aggregate je dan diverse waardes naar een group in MySQL? Toch wel vreemd als ze dat niet zouden ondersteunen. Ik wist dat ze iets achter liepen op PG, maar zover?

Daarnaast, het komt misschien als een verrassing voor je, maar PHP werkt ook met andere databases dan MySQL hoor

[Mei]

flowerp schreef op vrijdag 09 mei 2008 @ 11:04:
[...]
Daarnaast, het komt misschien als een verrassing voor je, maar PHP werkt ook met andere databases dan MySQL hoor

Weet ik, maar het moet zowel in MySQL als PGSQL werken