[Database] Model en dynamiek

[Verwijderd]

Ik ben bezig met een database ontwerp en loop tegen het volgende probleem aan:

Ik heb een tabel met persoonsgegevens erin en een tabel met groepen waarbij deze persoon behoort (persoon kan bij meerdere groepen horen en een groep kan meerdere personen hebben). Deze groepen horen allemaal eigenschappen te hebben zoals:

Groep #1 (Leden Vakbond):
Lid sinds: #datum# (timestamp)
Opmerkingen: #opmerk# (text)

Groep #2 (MR):
Functie: #functie# (varchar)
Telefoon: #telefoon# (char)

enzovoorts

Nu zit ik met het probleem welke invulling ik hierop moet geven in de database. Na lang uitzoeken enzovoorts, kwam ik tot een oplossing.

Een tabel aanmaken met een link naar persoon (1-op-1) en een veld van het type text. In het veld met type text zet ik het volgende

code:

[1.datum]20031222[1.opmerk]Actief lid[2.functie]Voorzitter[2.telefoon]012-3456789[.]

Dit gaat vervolgens in de code (bijvoorbeeld php) door een parser heen en maakt mbv templates alles zo is gewenst. (Ik heb gekozen voor 1 en niet voor Lid Vakbond omdat 1 het id van de groep unique is en de naam/omschrijving niet)

Ook zoeken lukt enigzins.

Echter vraag ik mezelf de hele tijd af er moeten nog meer mensen met dit soort problemen zitten vooral als het om bijvoorbeeld CMS gaat. Dynamisch forms etc. Hoe hebben jullie het opgelost?

[whoami]

Euhm, misschien moet jij eerst maar eens een tutorial over normaliseren gaan volgen?
Dit is gewoon basiskennis databank-ontwerp.

Je maakt een tabel 'Personen', je maakt een tabel 'Groepen'. Vervolgens maak je een tabel 'Persoon-Groep', die een persoon-id en een groep-id bevat. Met die tabel geef je dus aan tot welke groepen een persoon behoort.

[ Voor 56% gewijzigd door whoami op 22-12-2003 13:45 ]

[whoami]

Heropend na overleg.

De topicstarter was blijkbaar niet helemaal duidelijk over z'n probleem, en zal z'n probleem hier verder uit de doeken doen.

[Verwijderd]

Er zijn dus zoals ik al eerder aangaf nu twee tabellen + een "relatie" tabel aanwezig, deze zien er als volgt uit:

Tabel persoon:
persoon_id (PK)
persoon_naam
persoon_adres
persoon_woonplaats

tabel groep:
groep_id (PK)
groep_parent_id (FK naar groep.groep_id, geen parent dan NULL)
groep_omschrijving

Tabel persoon_groep:
persoon_id (PK)
groep_id (PK)

Nou hoort elke groep zijn eigen eigenschappen te hebben. Denk hierbij aan boven genoemde eigenschappen per groep. Aangezien deze eigenschappen een tot meerdere kunnen zijn van verschillende veldtypen (integer,varchar,text) is het lijkt mij niet logisch om deze in de tabel persoon_groep toe te voegen aangezien onbekend is hoeveel velden er nodig zijn en dit verschilt per groep.

Dus zat ik er zelf aan te denken om met een text veld te werken en deze of te plaatsen in de persoon_groep tabel of in de tabel persoon.

Echter vraag ik mezelf de hele tijd af er moeten nog meer mensen met dit soort problemen zitten vooral als het om bijvoorbeeld CMS gaat. Dynamisch forms etc. Hoe hebben jullie het opgelost?

In het geval van toevoegen van een text veld bij de tabel persoon:

code:

[1.datum]20031222[1.opmerk]Actief lid[2.functie]Voorzitter[2.telefoon]012-3456789[.]

In het geval van toevoegen van een text veld bij de tabel persoon_groep:

code:

[datum]20031222[opmerk]Actief lid[.]

dit omdat persoon_id en groep_id bekend zijn.

[ Voor 15% gewijzigd door Verwijderd op 22-12-2003 16:30 ]

[hobbit_be]

heb drie keer gelezen snap er nog steeds niets van .

ik denk dat je zoiets wil zeggen als.

elke persoon kan x-aantal (dat dus afhangt per persoon) extra parameters hebben die allemaal van een ander type zijn?

mogelijkheid

code:

person_attribute (table)
   pa_pk
   pa_person_fk
   pa_name      //de naam van je attribuut
   pa_type        //type van attrinbuut

   pa_int             //NULLAABLE
   pa_boolean    // "
   pa_varchar    /""

er bestaat 'misschien' wel zoiets als een compleet 'typeless' field in SQL maar als je specifiek gaat zeggen waar het zit (ie door je type attribuut) kan je de volle kracht van SQL gebruiken: de overhead is echter wel een nadeel.

ivm genereren van forms etc: al mijn applicaties zijn volledig database aware dus de html/of what export ook kunnen gewoon zien wat er kan + een mogelijk view erop...

[whoami]

Ik zou idd ook een extra tabel creeëren waarin je per attribuut voor een groep een record hebt.
Dat is iig veel flexibeler, makkelijker dan de oplossing die je nu bedacht hebt met dat textveld.
In SQL Server heb je trouwens een VARIANT data type dacht ik.

[Verwijderd]

Dank voor het drie keer lezen

Wat ik wil zeggen is: er zijn bijvoorbeeld 10 verschillende groepen waarvan ze alle 10 een andere invulling hebben op data die benodig is bij een groep (denk hierbij aan een of meerdere velden van verschillende typen), deze invulling hangt ook nog is van de persoon af dus normaliseren. Nu heb ik een tabel persoon_groep en kan ik daar bijvoorbeeld 10 velden toevoegen (integer,varchar,text,enz) maar er zal maar per groep bijvoorbeeld 3 velden ingevuld zijn, dus een overheid van 7. Hoe kan ik dit aanpakken om te voorkomen?

Met een text veld kan ik dat dynamisch maken dus opspliten middels een seperator, maar is dat wel de juiste oplossing?

[Verwijderd]

ik wil geen php-achtige constructie voor velden te defineren gebruiken Een veld is of dit of dat. De overhead die ik heb met zo'n tabel die hobbit beschrijft lijkt mij minimaal maar overheid in grote maakt me niet uit echter de snelheid vind ik het belangrijkst

[hobbit_be]

tja alles kan en wat is fout of juist? Er zijn mensen die hele arrays opslagen in een 'tekstveld'.

Een concreet voorbeeld bij jouw is een serialize van een associatieve array in PHP. Je moffelt er hem gewoon in en dan weer eruit. Daar is op zich niets mis mee.

Maar dan heb je wel data waarmee je in SQL dan weinig aan hebt. Net zoals in een BLOB images gaan gooien terwijl een filename misschien wat handiger is.

Iets doen met de data wordt dat totaal futile.

Mooi is het niet natuurlijk want het breekt een beetje als iemand anders het moet overnemen.

zo'n variant in SQL mag dan lelijk zijn - het is wel consistent.

offtopic:
zover is weet is een overheid in het nederlands toch net iets anders?

[ Voor 8% gewijzigd door hobbit_be op 22-12-2003 16:57 ]

[RobIII]

Huh? En zoiets dan?

Dat haal ik er uit. Een n op m relatie met per relatie een opmerking (of whatever)...

Wil je werken met vaste attributen dan is dit nog mooier:



Of nog beter:

[ Voor 41% gewijzigd door RobIII op 22-12-2003 17:08 ]

[Verwijderd]

Allereerst (offtopic) met welke programma heb je dat gedaan RobIII?

Ten tweede ik zat ook aan het laatste te denken, echter zit je met het probleem dat je het te complex gaat maken als het op veldtype neerkomt. Dus tbl_Attributes moet naast een varchar ook kunnen voorzien in een int. Dus kom ik toch terug op de tweede situatie

[Annie]

Verwijderd schreef op 22 december 2003 @ 17:22:
Allereerst (offtopic) met welke programma heb je dat gedaan RobIII?

offtopic:
Enterprise Manager (MS SQL Server) en dan kijken bij de diagrams van een database.

[RobIII]

Verwijderd schreef op 22 december 2003 @ 17:22:
Allereerst (offtopic) met welke programma heb je dat gedaan RobIII?

Ten tweede ik zat ook aan het laatste te denken, echter zit je met het probleem dat je het te complex gaat maken als het op veldtype neerkomt. Dus tbl_Attributes moet naast een varchar ook kunnen voorzien in een int. Dus kom ik toch terug op de tweede situatie

1) MS SQL server (of MSDE, maar daarin kun je niet van die mooie diagrammen maken )

2) Als je verschillende soorten datatypen als "value" wil opslaan, wordt het inderdaad wat moeilijker. Het makkelijkst is gewoon alles in een varchar(xx) op te slaan en dan alles zelf terugparsen (varchar->int ofzo). Je zou ook andere datatypen (variant ofzo) kunnen gebruiken is MSSQL, maar daar heb ik zelf nooit iets mee gedaan (omdat ik ze nog nooit nodig heb gehad ). Een andere smerige optie die ik ooit heb gezien (en dus absoluut afraad!) is van elk type 1 veld opnemen in je database en afhankelijk van het attribuut het juiste veld uitlezen. Je zou in een int kunnen vastleggen welk veld je dient te lezen om zo het parsen van attribuutnaam te voorkomen, maar geloof me, je maakt het alleen maar erger.

3) Is een XML bestand ofzo niks voor wat jij wil? Lekker hiërarchisch en je kunt er wat meer stunts zoals in 2) genoemd mee uithalen (wel zorgen voor een mooie DTD/schema whatever)

[ Voor 26% gewijzigd door RobIII op 22-12-2003 17:59 ]

[nescafe]

hry lijkt het mij logischer om per veldtype een tabel te maken:
FreeTextFields met daarin een Id en een waarde (text).
FreeNumberFields met daarin Id, waarde (nr)
Free...Fields etc..

[iig voor ms sql ]
Vervolgens kun je een mooie view (met union all's) maken die de waarden ophaalt en e.v.t. een stored procedure die je properties in de juiste tabel zet.
[/mssql]

Hoe denken jullie hierover? Is het wat performance betreft haalbaar? Je hebt iig geen beperkingen meer.

[RobIII]

nescafe schreef op 22 december 2003 @ 18:09:
hry lijkt het mij logischer om per veldtype een tabel te maken:
FreeTextFields met daarin een Id en een waarde (text).
FreeNumberFields met daarin Id, waarde (nr)
Free...Fields etc..

[iig voor ms sql ]
Vervolgens kun je een mooie view (met union all's) maken die de waarden ophaalt en e.v.t. een stored procedure die je properties in de juiste tabel zet.
[/mssql]

Hoe denken jullie hierover? Is het wat performance betreft haalbaar? Je hebt iig geen beperkingen meer.

Ik vind per veldtype een aparte tabel waarschijnlijk nog ranziger dan de voorgenoemde oplossing...Maar da's mijn mening...

[Verwijderd]

Als ik naar software pakketen like Goldmine enzo kijk slaan ze het op in memo/text fields.

Wat misschien een oplossing kan zijn is het uitwerken middels varchar. een varchar kan 2048 lang zijn wat inprincipe lang genoeg moet zijn. Echter overal waarvan je denkt dat het lang genoeg is is het op den duur toch te klein

RobIII schreef op 22 december 2003 @ 17:56:
[...]

3) Is een XML bestand ofzo niks voor wat jij wil? Lekker hiërarchisch en je kunt er wat meer stunts zoals in 2) genoemd mee uithalen (wel zorgen voor een mooie DTD/schema whatever)

Alsnog moet ik de ingevulde data opslaan. Het gaat puur om opslag, de forms zijn er gewoon al in de vorm van eigen templates met een parser wat deels gebaseerd is op XML.

[ Voor 43% gewijzigd door Verwijderd op 22-12-2003 18:31 ]

[hobbit_be]

RobIII schreef op 22 december 2003 @ 17:56:
[...]
Een andere smerige optie die ik ooit heb gezien (en dus absoluut afraad!) is van elk type 1 veld opnemen in je database en afhankelijk van het attribuut het juiste veld uitlezen.

Niet omdat zoiets voorstelde, maar gewoon uit de kracht van je disgust zou ik willen weten wat er fundamenteel mis mee is (except van bloatiness).

Dat per tabel type vind ik wel tamelijk extreem, want dat kun je dan ineens voor ALLES doen

heb je eigenlijk alleen maar tabellen met FK's naar 'velden'. Performance wise lijkt me dat maar surrealistisch.

Nou ja ik ben dan ook geen SQL fan

[nescafe]

RobIII schreef op 22 december 2003 @ 18:16:
[...]

Ik vind per veldtype een aparte tabel waarschijnlijk nog ranziger dan de voorgenoemde oplossing...Maar da's mijn mening...

Mijn reactie is meer naar aanleiding van een bestaand pakket (van Exact) waarbij in zowat elke tabel een aantal vrije velden in zijn opgenomen. Verschrikkelijk, omdat je altijd velden te veel of te weinig hebt.
Afgezien van ranzigheid vraag ik me dus of dit erg nadelig is voor de performance.

[RobIII]

Verwijderd schreef op 22 december 2003 @ 18:25:
Als ik naar software pakketen like Goldmine enzo kijk slaan ze het op in memo/text fields.

Wat misschien een oplossing kan zijn is het uitwerken middels varchar. een varchar kan 2048 lang zijn wat inprincipe lang genoeg moet zijn. Echter overal waarvan je denkt dat het lang genoeg is is het op den duur toch te klein
[...]
Alsnog moet ik de ingevulde data opslaan. Het gaat puur om opslag, de forms zijn er gewoon al in de vorm van eigen templates met een parser wat deels gebaseerd is op XML.

Bij mijn weten mag een varchar tot 8000 tekens lang zijn:

varchar[(n)]

Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

En daarna kun je nog altijd over op text... Maar ik vind 8k aan data toch al heel behoorlijk hoor!

hobbit_be schreef op 22 december 2003 @ 18:26:
[...]


Niet omdat zoiets voorstelde, maar gewoon uit de kracht van je disgust zou ik willen weten wat er fundamenteel mis mee is (except van bloatiness).
[...]
Nou ja ik ben dan ook geen SQL fan

Je slaat de spijker al op zijn kop: bloatiness. En dan het volgende punt: zoeken. Heb je de query al eens bedacht om in al die velden te zoeken en alles naar het juiste type te casten? Als je dit ooit in een SP wilt gooien mag je van elk type een parameter meegeven of EXEC gaan gebruiken. Hoe dan ook: dit soort gedrag is bij een goed DB ontwerp niet snel goed te praten (er zijn misschien uitzonderingen, maar ik heb er zo geen).

nescafe schreef op 22 december 2003 @ 18:38:
[...]
Mijn reactie is meer naar aanleiding van een bestaand pakket (van Exact) waarbij in zowat elke tabel een aantal vrije velden in zijn opgenomen. Verschrikkelijk, omdat je altijd velden te veel of te weinig hebt.
Afgezien van ranzigheid vraag ik me dus of dit erg nadelig is voor de performance.

Dat heb ik nou ook nooit begrepen van Exact. Nou stel dat er een "update" komt en je DB heeft een extra veldje nodig. Daar hebben ze dan toch ALTER TABLE voor uitgevonden, of hoe heb ik dat?

[Verwijderd]

Helaas is in MySQL de beperking tot 255 voor een varchar.

Omdat een text blok en/of velden over meerdere tabellen niet de juiste manieren zijn om met een database goed te werk te kunnen gaan heb ik deze laten varen

Vervolgens de volgende tabel gemaakt:

code:

CREATE TABLE `dyndata` (
  `persoon_id` int(11) NOT NULL default '0',
  `groep_id` int(11) NOT NULL default '0',
  `object_id` int(11) NOT NULL default '0',
  `dd_int` int(11) default NULL,
  `dd_varchar` varchar(255) default NULL,
  `dd_text` text,
  PRIMARY KEY  (`persoon_id`,`groep_id`,`object_id`)
) TYPE=MyISAM;

ook deze tabel zonder de velden dd_int en dd_text gemaakt qua opslag ruimte maakt dit niks uit, blijven allemaal gelijk

ook snelheid maakt niks uit.

zal nog search results later posten, als de engine aangepast is om hierop te kunnen zoeken

[RobIII]

Verwijderd schreef op 23 december 2003 @ 13:12:
Helaas is in MySQL de beperking tot 255 voor een varchar.

Omdat een text blok en/of velden over meerdere tabellen niet de juiste manieren zijn om met een database goed te werk te kunnen gaan heb ik deze laten varen

Vervolgens de volgende tabel gemaakt:

code:

CREATE TABLE `dyndata` (
  `persoon_id` int(11) NOT NULL default '0',
  `groep_id` int(11) NOT NULL default '0',
  `object_id` int(11) NOT NULL default '0',
  `dd_int` int(11) default NULL,
  `dd_varchar` varchar(255) default NULL,
  `dd_text` text,
  PRIMARY KEY  (`persoon_id`,`groep_id`,`object_id`)
) TYPE=MyISAM;

ook deze tabel zonder de velden dd_int en dd_text gemaakt qua opslag ruimte maakt dit niks uit, blijven allemaal gelijk

ook snelheid maakt niks uit.

zal nog search results later posten, als de engine aangepast is om hierop te kunnen zoeken

't is maar een lullige opmerking, maar ik moet 'm wel effe kwijt: Wat nu als je nog een datum ofzo wil opslaan? Je hebt nu een int, varchar en een text... Op deze manier kom je nog altijd iets te kort denk ik zo. Dan zou ik dus nog steeds gaan voor 1 varchar (255 is ook veel hoor!) of desnoods 1 text veld...

Maar aan de andere kant: Whatever suits your needs Have a ball!

[ Voor 3% gewijzigd door RobIII op 23-12-2003 14:24 ]

[Verwijderd]

RobIII schreef op 23 december 2003 @ 14:24:
[...]

't is maar een lullige opmerking, maar ik moet 'm wel effe kwijt: Wat nu als je nog een datum ofzo wil opslaan? Je hebt nu een int, varchar en een text... Op deze manier kom je nog altijd iets te kort denk ik zo. Dan zou ik dus nog steeds gaan voor 1 varchar (255 is ook veel hoor!) of desnoods 1 text veld...

Maar aan de andere kant: Whatever suits your needs Have a ball!

Over datum had ik gelukkig al nagedacht En ik heb juist daarom gekozen om onder andere int erbij te doen:

datum's worden of in timestamp (unix) opgeslagen welke in een int past
maar aangezien we ook met jaartallen before 1970 werken hebben we de volgende notatie om te overwegen: 20031223 waarbij je dan ook op datum kan zoeken... wat bij een varchar niet te doen is met SQL