Aangezien het opslaan van een variabel aantal eigenschappen, behorende bij een bepaald record, in SQL geen simpele taak is, ben ik benieuwd hoe jij dit aanpakt.
Eerst zal ik het probleem verduidelijken met een klein voorbeeld.
companies
- id
- name
Elk record uit deze tabel heeft een aantal (optionele!) eigenschappen. Denk hierbij aan een adres, factuuradres, contactpersoon, email, website, twitter, feed, historie, oprichtingsdatum, aantal werknemers, etc.
Het grote probleem is hoe je dit het beste kan opslaan. Denk erom dat er onderscheid gemaakt dient te worden tussen een datum, getal en tekst(veld).
Er zijn mij de volgende 'oplossingen' bekend:
- EAV. Het idee hierachter wordt hier uitstekend geschetst. Mijn insziens schiet deze benadering zijn doel voorbij, het heeft niets meer met een relationele database te maken.
- Key/pair tabel. Hierbij kan geen rekening gehouden worden met een kolomtype, wat voor problemen gaat zorgen indien je wilt gaan rekenen met data of getallen.
- Eén properties tabel aanmaken, met kolommen voor elke eigenschap. Dit schaalt echter niet, en bovendien (aangezien het optionele eigenschappen betreft) kan het vol komen te staan met NULL. Ook niet echt een schoolvoorbeeld van een goede relationele database.
Alle bovenstaande keuzes maken mij niet blij. Ik ben geïnteresseerd hoe jij dit probleem oplost en welke aanpak je hierbij gebruikt.
Ik gebruik voor dit soort grapjes meestal een andere database namelijk CouchDB, daar plemp je de hele mik in en via views haal je ze er uit. Schaalt als een gek en je hebt er geen omkijken meer naar.
Daarnaast is het vaak (heb ik gemerkt) zo dat dat dit probleem vooral op papier bestaat. Vaak wil je zsm je data hebben en zijn er maar een paar velden niet ingevuld. Als het dan ook nog eens om een 1 op 1 relatie gaat kies je gewoon voor optie 3. Vele malen sneller.
Als daar bezwaren voor zijn dan krijg je al snel optie 1. Optie 2 is de slechtste van het hele verhaal maar wel makkelijker te modelleren van optie 1
Ik gebruik voor dit soort grapjes meestal een andere database namelijk CouchDB, daar plemp je de hele mik in en via views haal je ze er uit. Schaalt als een gek en je hebt er geen omkijken meer naar.
Ik was inderdaad op de hoogte van het feit dat er gespecialiseerde tools zijn om dit probleem op te lossen. Ik vraag me echter af wat de beste manier is om dit in (My)SQL af te handelen.
Ruben Vastenhoudt op 20/08/2010 20:35:43
Daarnaast is het vaak (heb ik gemerkt) zo dat dat dit probleem vooral op papier bestaat. Vaak wil je zsm je data hebben en zijn er maar een paar velden niet ingevuld. Als het dan ook nog eens om een 1 op 1 relatie gaat kies je gewoon voor optie 3. Vele malen sneller.
Goed punt. Voorlopig zit ik met het idee om de verschillende tabellen te maken (adres, contact, company_properties voor de eigenschappen die niet in eerdergenoemde tabellen passen). Probleem hier is echter dat je niet zonder je tabel aan te passen eigenschappen bij kunt maken. Dit is iets wat je in de praktijk zeker gaat gebruiken.
Noppes Homeland op 20/08/2010 20:47:55
Je dient gewoon datbase normalisatie toe te passen
Normalisatie is zeker niet het probleem. Ik vraag me juist af hoe je een variabel aantal optionele eigenschappen het best (genormaliseerd) kan opslaan.
"Stel een bepaald bedrijf heeft een twitter, feed, website, terwijl een ander bedrijf dit allemaal niet heeft"
Dit is niet z'n best voorbeeld, er is niets op tegen om deze velden op te nemen in de main tabel - situatie 1 -.
Een andere optie is om de eigenschappen te groeperen naar soort en daarvoor een tabel aan te maken - situatie 2 -.
In dit geval kan je dus gewoon een tabel InternetCommunicatie maken
id (companies)
internetcommunicatietype
description
InternetCommunicatieTypen
name
description
En kom ik terug op:
"vraag me juist af hoe je een variabel aantal optionele eigenschappen het best (genormaliseerd) kan opslaan."
Het bestaat niet dat iets een optionele eigenschap heeft. Iets heeft een eigenschap of geen eigenschap. Dus als je op correcte wijze normaliseert, dan hoef je je hoofd daar ook niet over te breken.
Note: "optionele eigenschappen" bestaan in een database niet
situatie 1: een eigenschap heeft dan geen waarde in de tabelveld
situatie 2: er is geen record aanwezig
@Noppes, ik vraag me ook of wat je bedoelt. Wat zou volgens jou concreet een goed design zijn ?
@Mark, ik heb het ook wel eens duaal opgelost. Dus het belangrijke, doorzoekbare, join-bare en snel beschikbare informatie in een enkele table en de rest in een key pair opzet.
Bedrijven
BedrijfId
Naam
Telefoon
etc
BedrijfsEigenschappen
BedrijfId
Key
Value
In de meeste gevallen is de data supersnel op te vragen (SELECT * FROM Bedrijven WHERE ..) en ook vrij simpel te modelleren als er meer info opgevraag wordt
public function __get(){
if(array_key_exists()){
// return value uit Bedrijven database
} else if($this->isLoaded('Bedrijfseigenschappen')){
// check if key value exist if loaded
} else {
$this->load('Bedrijfseigenschappen');
//load key par values and check if key exist
}
}
Zo kun je gewoon dit gebruiken
$Bedrijf->EenofAndereVariabele;
(edit typo)
@Ruben,
Dat is zeker niet handig,
In een tabel naam zal geen fout voorkomen,
Als je met key's gaat werken, kan het voorkomen dat de key net even iets anders is als eerst, waardoor je daar niet meer op kan zoeken.
Hoewel het misschien een doem scenario blijkt, die niet vaak voorkomt, mag je deze risico's niet nemen.
En je mag nooit uitgaan van "in de meeste gevallen", maak iets dat ALTIJD werkt.
Anders word je net zo goed als de makers van de OV-Chipkaart, en of je daar nou trots op moet zijn.
Een optie die ik hier nog niet voorbij heb zien komen, maar wel al heb gezien in een PIM systeem: dynamisch eigenschappen tabellen uitbreiden
Als je een tabel op dezelfde manier bekijkt als een object in php dat je kunt extenden, dan kun je hetzelfde principe toepassen.
Dus een basis tabel, die je dynamisch uitbreid. Uiteraard met de nodige controles en middels een database init. De ORM laag kan hiervoor zorgen.
Misschien geen constructie om toe te passen in een kleine database maar iets dat wel kan werken in een grotere omgeving.
Niet per se Noppes. Lees eens verder dan je eerste studieboek, regel wat praktijkervaring en kom dan nog eens terug.
Ik denk eerder dat jij een cursus intrepeteren nodig hebt.
In de openings post:
"Het grote probleem is hoe je dit het beste kan opslaan. Denk erom dat er onderscheid gemaakt dient te worden tussen een datum, getal en tekst(veld)."
Ruben Frijns op 21/08/2010 11:37:13
Een optie die ik hier nog niet voorbij heb zien komen, maar wel al heb gezien in een PIM systeem: dynamisch eigenschappen tabellen uitbreiden
Als je een tabel op dezelfde manier bekijkt als een object in php dat je kunt extenden, dan kun je hetzelfde principe toepassen.
Dus een basis tabel, die je dynamisch uitbreid. Uiteraard met de nodige controles en middels een database init. De ORM laag kan hiervoor zorgen.
Misschien geen constructie om toe te passen in een kleine database maar iets dat wel kan werken in een grotere omgeving.
En hiermee sla je de plank helemaal mis! Het komt er dan dus opneer dat er niet goed is nagedacht over de database opzet
