Variabel aantal eigenschappen opslaan

Door Mark PHP op 20-08-2010 20:20

2.113 views

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.

Mark PHP

21-08-2010 11:52 gewijzigd op 21-08-2010 11:53

Noppes Homeland op 20/08/2010 22:13:15

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.

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?

Ruben Vastenhoudt op 20/08/2010 22:45:10
@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.

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.
Uiteraard wil je, zoals Nico suggereert, wel de keys in een aparte tabel gaan bijhouden.

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.

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.

Noppes Homeland op 21/08/2010 11:47:52

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)."

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?

Noppes Homeland

21-08-2010 12:22

"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 wel of geen waarde in het tabelveld
situatie 2: er is wel of geen record aanwezig

Mark PHP

21-08-2010 17:53 gewijzigd op 21-08-2010 17:53

Om misverstanden te voorkomen zal ik in deze post nogmaals het probleem schetsen, op een wat abstractere manier.

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.

Noppes Homeland

21-08-2010 18:42 gewijzigd op 21-08-2010 18:49

Stap nu eens af van het idee dat je in een database model een dynamisch aantal eigenschappen kan vastleggen!

"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

Nicoow Unknown

21-08-2010 19:23

Ik denk dat ik ondertussen een soort doel begin te begrijpen wat je wilt.
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?

Ruben Frijns

22-08-2010 15:45

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.

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.

Noppes Homeland

22-08-2010 17:03

@Ruben, jouw denkwijze is een kansloze missie, daar hangen zoveel nadelen aanvast dat je met z'n model niets kan aanvangen

Jens V

22-08-2010 22:13 gewijzigd op 23-08-2010 10:13

Ik zou eens kijken naar het databasemodel van phpBB2. Daar moet je de configuratie tabel maar eens van bekijken. Die had dat ook geloof ik.

Groetjes,
Jens

edit:
Hier is dus de dump ervan:



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');

Mark PHP

25-08-2010 13:19

Dat is precies het probleem. Het datamodel van phpBB is een ramp, aangezien er geen onderscheid is tussen datatypes. Voorlopig heb ik nog niet echt een oplossing. De meeste eigenschappen zijn bekend en dus prima te normaliseren, alleen dekt dat de lading niet 100%.

Reageren

Inloggen om te reageren