Variabel aantal eigenschappen opslaan
Eerst zal ik het probleem verduidelijken met een klein voorbeeld.
Code (php)
1
2
3
2
3
companies
- id
- name
- id
- name
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.
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
Ruben
(edit typo)
Gewijzigd op 20/08/2010 20:36:46 door Ruben Vastenhoudt
http://en.wikipedia.org/wiki/Database_normalization
http://www.yapf.net/index.php/Database_ontwerp_101
Ruben Vastenhoudt op 20/08/2010 20:35:43:
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.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.
Ruben Vastenhoudt op 20/08/2010 20:35:43:
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.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.
Noppes Homeland op 20/08/2010 20:47:55:
Normalisatie is zeker niet het probleem. Ik vraag me juist af hoe je een variabel aantal optionele eigenschappen het best (genormaliseerd) kan opslaan.Je dient gewoon datbase normalisatie toe te passen
Gewijzigd op 20/08/2010 21:09:30 door Mark PHP
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
Gewijzigd op 21/08/2010 12:18:36 door Noppes Homeland
@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)
Gewijzigd op 20/08/2010 22:45:50 door Ruben Vastenhoudt
BedrijfsEigenschappen
BedrijfId
Key
Value
is dus echt foute boel!!
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.
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.
Ruben Vastenhoudt op 21/08/2010 08:23:55:
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.
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
Gewijzigd op 21/08/2010 11:57:12 door Noppes Homeland
Noppes Homeland op 20/08/2010 22:13:15:
Niet helemaal waar. Stel een bepaald bedrijf heeft een twitter, feed, website, terwijl een ander bedrijf dit allemaal niet heeft. In een groter plaatje kan het dus zo zijn dat het ene bedrijf 5 eigenschappen heeft, en een ander 50. Hoe zou jij dit modelleren in SQL?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.
Ruben Vastenhoudt op 20/08/2010 22:45:10:
Zoiets had ik ook in gedachten, maar dan een scheiding op gebied van type. Dus een adrestabel, websitetabel (met Twitter, feed etc.), en wellicht een tabel met overige eigenschappen. Zie mijn vorige post.@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.
Uiteraard wil je, zoals Nico suggereert, wel de keys in een aparte tabel gaan bijhouden.
Ruben Frijns op 21/08/2010 11:37:13:
Krijg je dan op gegeven moment niet hetzelfde als punt 3 in mijn eerste post? Immers, als je de tabel dynamisch uitbreidt (dus een extra kolom aanmaakt), heeft dit invloed op alle records.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.
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.
Noppes Homeland op 21/08/2010 11:47:52:
Wat dus betekent dat ik me afvraag hoe je dit op correcte wijze kan normaliseren. Wellicht kan je een voorbeeld geven van hoe jij dit probleem zou oplossen?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)."
"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)."
Gewijzigd op 21/08/2010 11:53:22 door Mark PHP
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 wel of geen waarde in het tabelveld
situatie 2: er is wel of geen record aanwezig
De essentie van het probleem is het correct vastleggen van een dynamisch aantal eigenschappen (key/value pairs). Aangezien een value, afhankelijk van de key, een bepaald type heeft, zal dit moeten worden gerespecteerd door de database. De verschillende keys hebben geen (duidelijke) onderlinge relatie.
Hoe dit het beste te modelleren? De post van Noppes Homeland hierboven zou prima voldoen als we van tevoren weten welke eigenschappen we precies hebben. Echter, dit weten we dus niet.
Ik geef toe dat de term optionele eigenschappen wat slecht gekozen is en de lading niet afdoende dekt. De term dynamische eigenschappen past meer bij dit probleem.
Gewijzigd op 21/08/2010 17:53:48 door Mark PHP
"De post van Noppes Homeland hierboven zou prima voldoen als we van tevoren weten welke eigenschappen we precies hebben. Echter, dit weten we dus niet."
Als je dat niet weet, dan kan je er dus ook niets mee! Alleen door een goede analyse te maken van de mogelijke eigenschappen kan je een correct genormaliseerde database opzetten.
Je kan later altijd nog tabellen uitbreiden sq aanmaken om te voorzien in bepaalde eigenschappen.
Een andere optie is, om deze onbekende eigenschappen vast te leggen in een xml file. De data die in de database staan transformeer je dan ook naar xml en dan kan je mooi met xsl(t) doen en laten wat je wilt
Gewijzigd op 21/08/2010 18:49:14 door Noppes Homeland
Het zo bijvoorbeeld mogelijk zijn dat een Administrator van een website, en veld wilt toevoegen in het registratie formulier, en dat dit dan in de DB word opgeslagen.
Dan zou ik het zelf iets dan als:
-Keys
--id
--name
--type (kan je misschien op checken bij de invoer)
-KeyValues
--keyId
--value
Alleen dan zou value van het type text moeten zijn om alles kunnen af te vangen, dus dat word aardig lastig.
Misschien dat MySQL een soort typeOf() functie kent?
Quote:
Krijg je dan op gegeven moment niet hetzelfde als punt 3 in mijn eerste post? Immers, als je de tabel dynamisch uitbreidt (dus een extra kolom aanmaakt), heeft dit invloed op alle records.
Ruben Frijns op 21/08/2010 11:37:13:
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.
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.
Krijg je dan op gegeven moment niet hetzelfde als punt 3 in mijn eerste post? Immers, als je de tabel dynamisch uitbreidt (dus een extra kolom aanmaakt), heeft dit invloed op alle records.
De tabel die aanpasbaar is, is niet je hoofd tabel.
Je maakt dus naast een standaard tabel met eigenschappen, een nieuwe tabel met daarin de betreffende eigenschappen.
Dit zou je vooral gebruiken in een pakket dat voor verschillende klanten gebruikt wordt, waar je dus van tevoren een basis wil hebben.
Groetjes,
Jens
edit:
Hier is dus de dump ervan:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CREATE TABLE IF NOT EXISTS `phpbb_config` (
`config_name` varchar(255) NOT NULL,
`config_value` varchar(255) NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Gegevens worden uitgevoerd voor tabel `phpbb_config`
--
INSERT INTO `phpbb_config` (`config_name`, `config_value`) VALUES
('config_id', '1'),
('board_disable', '0'),
('sitename', 'yourdomain.com'),
('site_desc', 'A _little_ text to describe your forum'),
('cookie_name', 'phpbb2mysql'),
('cookie_path', '/'),
('cookie_domain', ''),
('cookie_secure', '0'),
('session_length', '3600'),
('allow_html', '0'),
('allow_html_tags', 'b,i,u,pre'),
('allow_bbcode', '1'),
('allow_smilies', '1'),
('allow_sig', '1'),
('allow_namechange', '0'),
('allow_theme_create', '0'),
('allow_avatar_local', '0'),
('allow_avatar_remote', '0'),
('allow_avatar_upload', '0'),
('enable_confirm', '1'),
('allow_autologin', '1'),
('max_autologin_time', '0'),
('override_user_style', '0'),
('posts_per_page', '15'),
('topics_per_page', '50'),
('hot_threshold', '25'),
('max_poll_options', '10'),
('max_sig_chars', '255'),
('max_inbox_privmsgs', '50'),
('max_sentbox_privmsgs', '25'),
('max_savebox_privmsgs', '50'),
('board_email_sig', 'Thanks, The Management'),
('board_email', 'bla bla bla bla'),
('smtp_delivery', '0'),
('smtp_host', ''),
('smtp_username', ''),
('smtp_password', ''),
('sendmail_fix', '0'),
('require_activation', '0'),
('flood_interval', '15'),
('search_flood_interval', '15'),
('search_min_chars', '3'),
('max_login_attempts', '5'),
('login_reset_time', '30'),
('board_email_form', '0'),
('avatar_filesize', '6144'),
('avatar_max_width', '80'),
('avatar_max_height', '80'),
('avatar_path', 'images/avatars'),
('avatar_gallery_path', 'images/avatars/gallery'),
('smilies_path', 'images/smiles'),
('default_style', '1'),
('default_dateformat', 'D M d, Y g:i a'),
('board_timezone', '0'),
('prune_enable', '1'),
('privmsg_disable', '0'),
('gzip_compress', '0'),
('coppa_fax', ''),
('coppa_mail', ''),
('record_online_users', '1'),
('record_online_date', '1282508845'),
('server_name', 'localhost'),
('server_port', '80'),
('script_path', '/phpBB2/'),
('version', '.0.23'),
('rand_seed', '50c24ffcc7bdc692607d98c122593feb'),
('board_startdate', '1282508829'),
('default_lang', 'english');
`config_name` varchar(255) NOT NULL,
`config_value` varchar(255) NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Gegevens worden uitgevoerd voor tabel `phpbb_config`
--
INSERT INTO `phpbb_config` (`config_name`, `config_value`) VALUES
('config_id', '1'),
('board_disable', '0'),
('sitename', 'yourdomain.com'),
('site_desc', 'A _little_ text to describe your forum'),
('cookie_name', 'phpbb2mysql'),
('cookie_path', '/'),
('cookie_domain', ''),
('cookie_secure', '0'),
('session_length', '3600'),
('allow_html', '0'),
('allow_html_tags', 'b,i,u,pre'),
('allow_bbcode', '1'),
('allow_smilies', '1'),
('allow_sig', '1'),
('allow_namechange', '0'),
('allow_theme_create', '0'),
('allow_avatar_local', '0'),
('allow_avatar_remote', '0'),
('allow_avatar_upload', '0'),
('enable_confirm', '1'),
('allow_autologin', '1'),
('max_autologin_time', '0'),
('override_user_style', '0'),
('posts_per_page', '15'),
('topics_per_page', '50'),
('hot_threshold', '25'),
('max_poll_options', '10'),
('max_sig_chars', '255'),
('max_inbox_privmsgs', '50'),
('max_sentbox_privmsgs', '25'),
('max_savebox_privmsgs', '50'),
('board_email_sig', 'Thanks, The Management'),
('board_email', 'bla bla bla bla'),
('smtp_delivery', '0'),
('smtp_host', ''),
('smtp_username', ''),
('smtp_password', ''),
('sendmail_fix', '0'),
('require_activation', '0'),
('flood_interval', '15'),
('search_flood_interval', '15'),
('search_min_chars', '3'),
('max_login_attempts', '5'),
('login_reset_time', '30'),
('board_email_form', '0'),
('avatar_filesize', '6144'),
('avatar_max_width', '80'),
('avatar_max_height', '80'),
('avatar_path', 'images/avatars'),
('avatar_gallery_path', 'images/avatars/gallery'),
('smilies_path', 'images/smiles'),
('default_style', '1'),
('default_dateformat', 'D M d, Y g:i a'),
('board_timezone', '0'),
('prune_enable', '1'),
('privmsg_disable', '0'),
('gzip_compress', '0'),
('coppa_fax', ''),
('coppa_mail', ''),
('record_online_users', '1'),
('record_online_date', '1282508845'),
('server_name', 'localhost'),
('server_port', '80'),
('script_path', '/phpBB2/'),
('version', '.0.23'),
('rand_seed', '50c24ffcc7bdc692607d98c122593feb'),
('board_startdate', '1282508829'),
('default_lang', 'english');
Gewijzigd op 23/08/2010 10:13:07 door Jens V