[MySQL/Alg] - Database flexbiliteit door variabele velden

[gvanh]

Goedemorgen!

Hierbij even een poging om een beetje te klankborden.

Een situatie-schets, die het best de werkelijkheid benaderd: binnen één database van een CRM-achtige applicatie draaien verschillende projecten. Voor ieder van die projecten worden klanten bijgehouden, maar de informatie die per klant opgeslagen moet worden varieert. Zo zullen er in de ene situatie (basale) gegevens opgeslagen worden die medisch van aard zijn, de andere keer kan het meer in de financiele sfeer liggen.

Nu wil ik de database zo inrichten, dat de klant-tabel(len) zo zijn ingericht, dat die flexibele informatie daar in past. Voor zover ik kan bedenken, zijn er drie opties:

OPTIE 1. Er komt één groot veld, waarin de aanvullende velden in XML-formaat opgeslagen worden.
voordeel:
- minder joins en daardoor wellicht sneller
nadeel:
- minder nauwkeurig zoeken (WHERE postcode='XXX' is geen optie -> toch?)

OPTIE 2. Naast de klant-tabel met basisgegevens, komt een aanvullende tabel die er ongeveer zo uitziet:

MySQL:

CREATE TABLE `client_data` (
  `client_dataID` int(11) NOT NULL auto_increment,
  `clientID` int(11) NOT NULL default '0',
  `fieldname` varchar(50) NOT NULL,
  `fieldlabel` varchar(50) NOT NULL,
  `fieldvalue` varchar(150) NOT NULL,
)

Daarin is "fieldname" dan vergelijkbaar met de naam van een HTML input veld, fieldlabel is de voor mensen begrijpelijke omschrijving van het veld (bijvoorbeeld voor formulieren, rapporten, etc.) en fieldvalue is de waarde die het veld heeft voor een specifieke client.

voordeel:
- heel flexibel, en toch specifieke mogelijkheden om te zoeken

nadelen:
- veel queries danwel joins nodig om gegevens voor een set van klanten tegelijk op te halen, bij grotere databases leidt dit wellicht tot onaanvaardbare vertraging.
- alle waarden worden als varchar(150) opgeslagen, zelfs als het altijd getallen zijn of heel kleine strings (postcode/huisnummer/etc.).

OPTIE 3. "Iets" doen met VIEWS in MySQL.
Hier heb ik (nog) geen ervaring mee. Wel al eens vluchtig naar gekeken, maar nog onvoldoende. Wellicht is het mogelijk om een VIEW te maken van de database setup beschreven in optie 2, om op die manier de data vrij makkelijk toegankelijk te hebben en ook het aantal joins in queries te kunnen beperken.

4. ???

Ik ben heel benieuwd of jullie hierover specifieke ideeën hebben. Alvast bedankt voor het meedenken.

[HuHu]

OPTIE 4: iets met inheritance.

Je maakt een algemene tabel 'klant', waarin je de naam/adres/woonplaats gegevens opslaat en een klantID. Vervolgens maak je per type klant extra tabellen aan, waarin de aanvullende informatie staat. Dus zo:

KLANT:
id
naam
adres
woonplaats

KLANT_MEDISCH
klant_id
medicijn
ziekte
arts

KLANT_FINANCIEEL
klant_id
saldo
adviseur

Je moet dus wel van te voren vastleggen welke typen klanten je hebt en welke informatie daar bij hoort. Vervolgens haal je uit de algemene tabel de n.a.w. gegevens op (die er voor elke klant zijn), je bepaalt het type van de klant en gaat vervolgens de aanvullende informatie uit de andere tabel(len) halen.

[gvanh]

Je maakt een algemene tabel 'klant', waarin je de naam/adres/woonplaats gegevens opslaat en een klantID. Vervolgens maak je per type klant extra tabellen aan, waarin de aanvullende informatie staat.

Dank voor je reactie. Ik ben bang dat dat niet flexibel genoeg is. Het idee is dat er veel verschillende klanttypes gaan komen - met onderling kleine of grote afwijkingen - en dat de velden per klant via een beheer-omgeving ingesteld kunnen worden. Het maken van een eigen tabel voor ieder type klant is dus helaas geen optie. Dat zou het geheel overigens een stuk makkelijker maken.

[Janoz]

Kleine toevoeging aan optie 2: Definitie van de waarde en de daadwerkelijke waarde niet in dezelfde tabel. Er zullen immers meerdere clienten binnen 1 project zijn die die eigenschappen hebben.

Maar eigenlijk vind ik dat je het jezelf onnodig lastig maakt door dit allemaal perse in één database wilt doen. Een CRM voor een financiele instelling is nu eenmaal wat anders dan een CRM voor een medische instelling. Er zal altijd een stukje maatwerk aan te pas gaan komen, dus waarom niet de DB tailoren voor de specifieke toepassing?

Wat ik eventueel eerder zou overwegen is om een soort basis schema te maken waarin de core functionaliteit zit en daarnaast aparte tabellen (met eventueel een 1 op 1 relatie met de core tabellen) waarin je de project specifieke onderdelen opslaat. Dat maakt het upgraden van bestaande installaties (vanuit de DB gezien) wat overzichtelijker.

[gvanh]

Janoz schreef op woensdag 27 mei 2009 @ 09:17:
Kleine toevoeging aan optie 2: Definitie van de waarde en de daadwerkelijke waarde niet in dezelfde tabel. Er zullen immers meerdere clienten binnen 1 project zijn die die eigenschappen hebben.

Even de rest van het verhaal eigenwijs negerend, ben ik even aan het doorpuzzelen op basis van een setup met drie tabellen. Eén tabel is dan simpelweg de clients tabel, redelijk recht-toe-recht-aan.

Daarnaast heb ik de volgende twee tabellen staan:

MySQL:

CREATE TABLE `cms_client_data_fieldnames` (
  `client_data_fieldnameID` int(11) NOT NULL auto_increment,
  `client_data_fieldname` varchar(50) NOT NULL,
  `client_data_fieldlabel` varchar(150) NOT NULL,
  PRIMARY KEY  (`client_data_fieldnameID`)
);

CREATE TABLE `cms_client_data_fieldvalues` (
  `clientID` int(11) NOT NULL,
  `client_data_fieldnameID` int(11) NOT NULL,
  `fieldvalue` varchar(150) NOT NULL,
  PRIMARY KEY  (`clientID`,`client_data_fieldnameID`)
);

De voor de handliggende join geeft nu ongeveer het volgende resultaat:

code:

clientID  client_name       client_data_fieldname   fieldvalue
19809     Mijn naam         zipcode                 9999 ZZ
19809     Mijn naam         city                    Mijn woonplaats

Maar waar ik nu toe wil, is een view die iets weergeeft als:

code:

clientID  client_name       zipcode       city
19809     Mijn naam         9999 ZZ       Mijn woonplaats

Is er een manier om zoiets voor elkaar te krijgen. Mijn abstractievermogen schiet volgens mij even tekort nu.

[Janoz]

gvanh schreef op woensdag 27 mei 2009 @ 12:29:
Even de rest van het verhaal eigenwijs negerend, ben ik even aan het doorpuzzelen op basis van een setup met drie tabellen. Eén tabel is dan simpelweg de clients tabel, redelijk recht-toe-recht-aan.

Leuk voor de oefening, maar je negeert inderdaad nu het belangrijkste deel van mijn post.

Daarnaast heb ik de volgende twee tabellen staan:

MySQL:

CREATE TABLE `cms_client_data_fieldnames` (
  `client_data_fieldnameID` int(11) NOT NULL auto_increment,
  `client_data_fieldname` varchar(50) NOT NULL,
  `client_data_fieldlabel` varchar(150) NOT NULL,
  PRIMARY KEY  (`client_data_fieldnameID`)
);

CREATE TABLE `cms_client_data_fieldvalues` (
  `clientID` int(11) NOT NULL,
  `client_data_fieldnameID` int(11) NOT NULL,
  `fieldvalue` varchar(150) NOT NULL,
  PRIMARY KEY  (`clientID`,`client_data_fieldnameID`)
);

Wat ik nog een beetje mis is de koppeling met project.

De voor de handliggende join geeft nu ongeveer het volgende resultaat:

code:

clientID  client_name       client_data_fieldname   fieldvalue
19809     Mijn naam         zipcode                 9999 ZZ
19809     Mijn naam         city                    Mijn woonplaats

Beter zul je het ook niet gaan krijgen

Maar waar ik nu toe wil, is een view die iets weergeeft als:

code:

clientID  client_name       zipcode       city
19809     Mijn naam         9999 ZZ       Mijn woonplaats

Is er een manier om zoiets voor elkaar te krijgen. Mijn abstractievermogen schiet volgens mij even tekort nu.

Ja, beetje rondhacken in de viewcode en flink wat subqueries (1 per extra kolom) en je komt een heel eind. Ik hoop echter dat je een beetje begint te beseffen dat er bij maatwerk altijd maatwerk zit. Het enige dat je aan het doen bent is de plek van het maatwerk naar een steeds onlogischere en minder efficiente plek aan het verplaatsen. Effectief ben je nu bezig met het database in een database anti pattern. Je hebt 1 feature heilig verklaard. Alles moet er voor wijken. Je zou jezelf gewoon eens af moeten vragen of de feature nu daadwerkelijk zo belangrijk is, of dat er misschien wel een betere tradeoff mogelijk is.

[winkbrace]

Persoonlijk zou ik dat laatste oplossen in de omgeving/tool waarin je het weergeeft.
als je de inhoud van client_data_fieldname niet weet, kun je er geen kolomnaam van maken in sql.

Hoewel je wel zoiets kunt benaderen.
Je hebt dan een extra kolom nodig met een volgnummer waarop je de uiteindelijke kolommen bepaald. Je moet dan wel weten hoeveel kolommen er maximaal kunnen zijn.

code:

clientID  Volgnr  client_name       client_data_fieldname   fieldvalue
19809     1       Mijn naam         zipcode                 9999 ZZ
19809     2       Mijn naam         city                    Mijn woonplaats

SQL:

select id
,      naam
,      max(case when volgnr = 1 then fieldname else null end) veld1 
,      max(case when volgnr = 2 then fieldname else null end) veld2
from   tmp_test
group by id
,      naam
union all
select id
,      naam
,      max(case when volgnr = 1 then fieldvalue else null end) veld1 
,      max(case when volgnr = 2 then fieldvalue else null end) veld2
from   tmp_test
group by id
,      naam

Dit gezegd hebbende, moet je dit niet zo algemeen willen oplossen. De vorm van een database is natuurlijk afhankelijk van de inhoud. Zoals Janoz benadrukt.

[gvanh]

Nog even een kleine update. Ik heb nu zoiets staan:

MySQL:

SELECT C.clientID, C.client_name,
  GROUP_CONCAT( fn.client_data_fieldname
SEPARATOR '#' ) AS client_data_fieldname,
  GROUP_CONCAT( fv.fieldvalue
SEPARATOR '#' ) AS fieldvalue
  FROM (cms_clients AS C)
LEFT JOIN cms_client_data_fieldvalues AS fv ON fv.clientID = C.clientID
LEFT JOIN cms_client_data_fieldnames AS fn ON fn.client_data_fieldnameID = fv.client_data_fieldnameID
GROUP BY C.clientID

Dat levert de veldnamen en veldwaarden beide (los van elkaar) in één kolom op. Daarmee zou ik dan toch met een enkele query alle benodigde gegevens uit de database kunnen trekken. In PHP kan ik ze vervolgens splitten (misschien moet ik die SEPARATOR nog vervangen door iets dat nog minder waarschijnlijk is).

Resultaat:

MySQL:

clientID   client_name  client_data_fieldname  fieldvalue
1234       Mijn naam    zipcode#city          9999 ZZ#Plaats
4321       Andere naam  zipcode#city          8412 GZ#Arnhem

Is er qua snelheid of grootte van de mogelijke inhoud van de kolommen een heel prangende reden om deze aanpak niet te volgen?

Overigens gaan alle updates en inserts niet op deze manier ... daar gaat het immers om wijzigingen per client. Alleen voor het opvragen van overzichten is het wel erg prettig om alle gegevens direct in één rij beschikbaar te hebben.

[ Voor 12% gewijzigd door gvanh op 27-05-2009 17:23 . Reden: Resultaat-voorbeeld toegevoegd ]

[RobIII]

Waarom zou je uberhaupt gaan concatten? Wat is er mis met beide velden teruggeven? (Los van 't feit dat je de beroemde DB-in-een-DB aan 't bouwen bent, waarvan je nu al de gevolgen begint te ondervinden... en trust me; dit is nog maar het topje van de ijsberg )

[ Voor 26% gewijzigd door RobIII op 27-05-2009 17:22 ]

[gvanh]

Concatten doe ik in dit geval om ervoor te zorgen dat gegevens die normaal gesproken over verschillende rijen verdeeld zijn, nu in één rij binnen te halen zijn.

Zou ik dat niet doen, dan moet ik dus voor iedere client die ik uit de database haal een extra query doen om de daaraan gekoppelde gegevens op te halen. Stel, ik heb een overzicht van 30 clienten, dan moet ik dus voor een overzicht waarin ik de postcode en plaats wil weergeven 1 x een query doen op de clients table en vervolgens 30 queries (voor iedere client nog eens los 1). In dit geval kost het me maar 1 query. De joins op de basis query zijn verder vrijwel hetzelfde als op mijn GROUP_CONCAT-versie, dus ook daar ga ik geen winst behalen.

Met betrekking tot Db-in-een-Db ... is dat een bekend verschijnsel. Zo ja ... dan zal het onderliggende probleem ook wel bekend zijn. Is er een keyword waarop ik kan Googelen om daar eens wat meer over te lezen? Ik heb gezocht op "db-in-a-db", maar dat leverde niets (nuttigs) op.

Verder zie ik het werkelijk niet zitten om voor ieder client-type een aparte tabel in te richten. Het systeem gaat waarschijnlijk een flink aantal verschillende projecten (=klanttypes) bevatten, en ik heb niet zo'n behoefte om dat per project te gaan inregelen. Bovendien is één van de (beoogde) toegevoegde waarden van het systeem dat dat ook juist ook allemaal zelf geregeld kan worden. Een aparte database per project is al helemaal geen optie.

Oh jee ... het is echt een breinbreker zo.

[RobIII]

gvanh schreef op woensdag 27 mei 2009 @ 18:03:
Concatten doe ik in dit geval om ervoor te zorgen dat gegevens die normaal gesproken over verschillende rijen verdeeld zijn, nu in één rij binnen te halen zijn.

Zou ik dat niet doen, dan moet ik dus voor iedere client die ik uit de database haal een extra query doen om de daaraan gekoppelde gegevens op te halen.

Of je lost 't op in je presentatielaag

[gvanh]

RobIII schreef op woensdag 27 mei 2009 @ 18:05:
[...]

Of je lost 't op in je presentatielaag

Vertel me meer! Hoe zie je dat?
Ergens moet die data toch opgehaald worden?
Mis ik iets heel nuttigs dat dit alles op zijn kop zet?

[RobIII]

gvanh schreef op woensdag 27 mei 2009 @ 18:09:
[...]


Vertel me meer! Hoe zie je dat?
Ergens moet die data toch opgehaald worden?
Mis ik iets heel nuttigs dat dit alles op zijn kop zet?

Als je zoiets terug krijgt:

code:

ID Name     FieldName   Value
----------------------------------------------------
1  Jantje   Email       [e-mail]
1  Jantje   Phone       [phone]
1  Jantje   BloodType   A
2  Pietje   Email       [e-mail]
2  Pietje   Phone       [phone]
2  Pietje   BloodType   B

Dan kun je dat, in en door je presentatielaag, toch prima als 1 record presenteren?

code:

current = 0
For each rec in recordset
  if current != rec.id
    current = rec.id
    newline
    print rec.name + " >> "
  end if
  print rec.fieldname + ":" + rec.value + " "
Next

Text output:

Jantje >> Email:[e-mail] Phone:[phone] BloodType:A
Pietje >> Email:[e-mail] Phone:[phone] BloodType:B

Kwestie van wat formattering toepassen (of gebruik een listview in een Winforms project, of...of...) en klaar.

[ Voor 22% gewijzigd door RobIII op 27-05-2009 18:19 ]

[gvanh]

Ja, dat kan natuurlijk.

Maar waarom zou ik dat foreach loopje niet laten regelen door MySQL, zodat ik het resultaat als een enkele rij binnenkrijg en naadloos mijn PHP-objecten-framework kan inschieten, met een tweetal simpele aanroepen naar de één of andere split-functie?

Wat zou daar op tegen zijn, als het puur gaat om het vullen van objecten. Zoals gezegd ga ik data verder niet manipuleren, ik ga ook zeker geen "LIKE" vergelijkingen doen op de CONCAT-velden of zoiets raars. Het is alleen een nabewerking van de gevonden rijen. Als ik dat met een slimme subquery opzet, dan hoeft dat CONCAT-verhaal ook alleen toegepast te worden op de rijen die voldoen aan verdere criteria, dus hoeft er niet een volledige tabel geconcat te worden.

Als ik het zo breng, is er dan nog steeds iets falikant mis met deze insteek?

[RobIII]

gvanh schreef op woensdag 27 mei 2009 @ 18:29:
Als ik het zo breng, is er dan nog steeds iets falikant mis met deze insteek?

Laat ik het zo stellen: post even als je project gestrand is of als je spijt hebt van je keuze 1 van beiden is zo goed als gegarandeerd

De weg die je nu ingeslagen bent brengt gewoon veel "offers" met zich mee en is gedoemd te stranden. Je kunt geen zinnige indexes maken, kun geen typechecking gebruiken, geen constraints opleggen of relaties leggen, niet (zinnig) sorteren en/of zoeken wegens verkeerde type (alles is 'string') enz. enz. enz.

Wat wél de juiste oplossing is, is moeilijk te zeggen. Dat is nogal afhankelijk van de situatie, de voor handen zijnde infrastructuur, platform, projecttype (web/desktop app./etc.), mogelijkheden voor het gebruiken van andere (externe) software enz. Zaken als DDL gebruiken (ook ranzig in 9 v.d. 10 gevallen maar, mits goed uitgevoerd, het dichts bij wat jij wil en je blijft toch bij je RDBMS), binary en/of externe files etc. etc. zijn allemaal mogelijkheden. Maar dat is ook nogal afhankelijk van de eisen die je stelt aan de "dynamische" data, de hoeveelheid etc. etc..

gvanh schreef op woensdag 27 mei 2009 @ 18:03:
Met betrekking tot Db-in-een-Db ... is dat een bekend verschijnsel.

Jep
Bedenk het volgende eens: waar denk je dat je RDBMS (o.a.) de tabeldefinitie in opslaat? Juist, in een master table. Wat jij nu aan 't bouwen bent is een wannabe-mastertable

[ Voor 45% gewijzigd door RobIII op 27-05-2009 18:59 ]

[DamadmOO]

Nog een andere (lelijke) oplosing is de manier te gebruiken die onder andere in SuperOffice gebruikt wordt. Daar wordt gebruik gemaakt van 1 tabel voor de contact personen, 1 tabel (contact_extended) waarin de volgende velden zijn: 50* varchar, 50* numeric, 50* boolean. En dan een generieke database tabel met: id, TableName, ColumnName, ColumnDescription.

Dit is ook verre van een nette oplossing, maar het is in ieder geval beter dan db-in-a-db antipattern achtige dingen.

RobIII schreef op woensdag 27 mei 2009 @ 18:32:
[...]

Laat ik het zo stellen: post even als je project gestrand is of als je spijt hebt van je keuze 1 van beiden is zo goed als gegarandeerd

Hier kan ik het alleen maar mee eens zijn. Bij het bedrijf waar ik nu werk is er ook zo'n db-in-a-db applicatie speciaal voor ze ontwikkeld met totale kosten van een € 12M. En deze applicatie is nooit live gekomen door de manier waarop het is opgezet. Het geluk is nu wel dat ik hier mijn baan aan heb te danken

[ Voor 38% gewijzigd door DamadmOO op 27-05-2009 18:41 ]

[DamadmOO]

gvanh schreef op woensdag 27 mei 2009 @ 18:03:
Met betrekking tot Db-in-een-Db ... is dat een bekend verschijnsel. Zo ja ... dan zal het onderliggende probleem ook wel bekend zijn. Is er een keyword waarop ik kan Googelen om daar eens wat meer over te lezen? Ik heb gezocht op "db-in-a-db", maar dat leverde niets (nuttigs) op.

http://www.scribd.com/doc/2670985/SQL-Antipatterns

En dan het gedeelte Entity-Attribute-Value. Maar lees ook gerust de rest van het document door

[cariolive23]

Tip van de dag: Gebruik géén database.

Waarom? Omdat je dan ook niet de illusie hebt dat je een database gebruikt en daar dus niets van gaat verwachten. Mocht je wel een database gebruiken, ga je zomaar verwachten dat deze foutloos werkt (hoewel, MySQL....), snel werkt en vooral bruikbaar is. Bovenstaande bak stront, hard maar waar, kan onmogelijk aan jouw verwachtingen voldoen, tenzij je verwacht dat jouw systeem gaat stinken en mislukken.

Dit is vast niet wat je horen wilt en anderen hebben het ook al gezegd, maar het is niet anders. Ga normaliseren en ga hier vooral heel zorgvuldig mee om. Dit is de basis van een goed, veilig en snel systeem. Een arts of een financieel adviseur, beide zijn het contactpersonen. Adressen? Eenvoudig te normaliseren, hoef je echt niet op één grote hoop te gooien. Normaliseren is geen doel op zich maar helpt je wel enorm om jouw doelen te bereiken.

Succes!

[Remus]

DamadmOO schreef op woensdag 27 mei 2009 @ 18:59:
[...]


http://www.scribd.com/doc/2670985/SQL-Antipatterns

En dan het gedeelte Entity-Attribute-Value. Maar lees ook gerust de rest van het document door

Dit is ook wel een mooi voorbeeld: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

[Aham brahmasmi]

DamadmOO schreef op woensdag 27 mei 2009 @ 18:59:
[...]


http://www.scribd.com/doc/2670985/SQL-Antipatterns

En dan het gedeelte Entity-Attribute-Value. Maar lees ook gerust de rest van het document door

Bedankt voor die link. Interessant, want ik heb ook wel eens met sommige van die anti-patterns geklooid.

[MrBucket]

RobIII schreef op woensdag 27 mei 2009 @ 18:32:
[...]

Wat wél de juiste oplossing is, is moeilijk te zeggen. Dat is nogal afhankelijk van de situatie, de voor handen zijnde infrastructuur, platform, projecttype (web/desktop app./etc.), mogelijkheden voor het gebruiken van andere (externe) software enz. Zaken als DDL gebruiken (ook ranzig in 9 v.d. 10 gevallen maar, mits goed uitgevoerd, het dichts bij wat jij wil en je blijft toch bij je RDBMS), binary en/of externe files etc. etc. zijn allemaal mogelijkheden. Maar dat is ook nogal afhankelijk van de eisen die je stelt aan de "dynamische" data, de hoeveelheid etc. etc..

De vetgedrukte optie is hoe MS Dynamics CRM het oplost: voor elke entiteit heb je een basistabel met de vaste attributen (primary key, laatst bijgewerkt, eigenaar, etc.), en een extension tabel met een foreign key naar de primary tabel, en voor de rest de custom attributen. De attributen voor een entiteit kun je in een beheeronderdeel van de applicatie aanpassen, met als gevolg dat het onderliggende database-schema voor de extension tabel wordt aangepast. Daarnaast wordt er voor elke entiteit ook nog een view gedefinieerd die de data uit de basistabel en de extensiontabel combineert, zodat het als 1 'tabel' te gebruiken is.
(En daarnaast wordt er nog allerlei metadata bijgehouden en andere spannende dingen gedaan, maar dit is ong. de basis).

Het voordeel is dat je geen DB in een DB zit te programmeren, dat queries performant uit te voeren zijn en dat unique en FK constraints goed af te dwingen zijn.

[eghie]

Met de SQL commando's DESCRIBE en ALTER TABLE moet je een eind kunnen komen om de structuur te kunnen beheren. Daarbij kun je ook nog kolom types, constraints, indexes, etc, opgeven. Dan lever je toch niet zoveel in op snelheid en beheersbaarheid en houdt het toch flexibel.

[RobIII]

eghie schreef op zondag 31 mei 2009 @ 22:14:
Met de SQL commando's DESCRIBE en ALTER TABLE moet je een eind kunnen komen om de structuur te kunnen beheren. Daarbij kun je ook nog kolom types, constraints, indexes, etc, opgeven. Dan lever je toch niet zoveel in op snelheid en beheersbaarheid en houdt het toch flexibel.

Dat is dus DDL

MrBucket schreef op zondag 31 mei 2009 @ 15:53:
[...]

De vetgedrukte optie is hoe MS Dynamics CRM het oplost

Ik ben niet bekend met de interne werking van MS Dynamics CRM, maar het kan prima waar zijn wat je zegt. De pest is dat het vaak, dus niet altijd, gaar uitgevoerd in een gemiddeld (a-la "MKB") project(je). Uitgaande van de omvang van MS D CRM ga ik er even van uit dat het daar wél snor zit.

[ Voor 33% gewijzigd door RobIII op 31-05-2009 22:38 ]

[cariolive23]

Met DDL-statements in je applicatie is beveiliging wel een heel erg belangrijk issue. Wanneer je dat niet goed voor elkaar hebt, kunnen veel te veel (database-) users de database naar de bliksem helpen. Zorg dus voor de juiste rollen in de database en zorg er voor dat alle rechten zijn ingetrokken, tenzij een rol een bepaald recht echt nodig heeft. De DDL-statements verdienen hierbij extra aandacht, hiermee kan heel veel schade worden aangericht.

Uiteraard moet de applicatie hier goed op inspelen en de juiste rol aannemen bij het uitvoeren van queries.

MS Dynamics CRM ken ik niet, maar ik weet wel dat vele (ook zeer grote en kostbare) software pakketten de beveiliging met de database niet goed voor elkaar hebben. En vele daarvan gebruiken nog geen eens DDL-statements...

[MrBucket]

RobIII schreef op zondag 31 mei 2009 @ 22:34:
[...]
Ik ben niet bekend met de interne werking van MS Dynamics CRM, maar het kan prima waar zijn wat je zegt. De pest is dat het vaak, dus niet altijd, gaar uitgevoerd in een gemiddeld (a-la "MKB") project(je). Uitgaande van de omvang van MS D CRM ga ik er even van uit dat het daar wél snor zit.

Er zitten wel wat beperkingen aan:

• Alleen de meest gangbare datatypes worden ondersteund, maar Guids bijvoorbeeld niet.
• Het is niet mogelijk om een n:m relatie te modelleren zonder expliciet een (voor de gebruiker zichtbare) koppelentiteit te introduceren.
• Het is niet mogelijk een optionele enkelvoudige relatie ((1)..(0, 1)) te modelleren, alleen 1..(0..n).
• Transacties worden niet ondersteund vanuit webservice calls en plugins; je zult zelf compensating actions moeten maken om te kunnen "rollbacken".

cariolive23 schreef op maandag 01 juni 2009 @ 11:57:
MS Dynamics CRM ken ik niet, maar ik weet wel dat vele (ook zeer grote en kostbare) software pakketten de beveiliging met de database niet goed voor elkaar hebben. En vele daarvan gebruiken nog geen eens DDL-statements...

MS CRM 4 werkt volledig op basis van domeinaccounts. Alleen accounts met beheerdersrechten kunnen entiteitdefinities veranderen. Daarnaast bestaan er op de database naast de reguliere views (die de base en extension tabel combineren) ook nog zgn. filtered views, die op basis van het domeinaccount van de huidige gebruiker alleen de rijen retourneren die hij ook mag zien. Dit is met name handig voor reporting doeleinden waarbij queries rechtstreeks op de database worden uitgevoerd.

Dus al met al vind ik het zeker goed in elkaar zitten.

[cariolive23]

Alleen accounts met beheerdersrechten kunnen entiteitdefinities veranderen

En zijn deze accounts dan wel goed ingericht? Wanneer iedereen zo'n account kan overnemen, dan heeft het weinig toegevoegde waarde. En wanneer zo'n account in de applicatie in de database een superuser-account vereist, dan heb je op zijn zachts gezegd wel een probleem.

Dus zonder de details van het account te kennen, is er niet zomaar te zeggen of het veilig is of niet. Een superuser account op de database zou in elk geval gee goed plan zijn, dat soort accounts zijn er uitsluitend voor de DBA's en nooit en te nimmer voor andere gebruikers en/of applicaties.

Ps. Ik ben geen MS-kenner.

[PolarBear]

cariolive23 schreef op maandag 01 juni 2009 @ 13:49:
[...]
Ps. Ik ben geen MS-kenner.

Dat is wel duidelijk. Als iets wel het geval is bij veel Microsoft producten is het een hele fijn granulariteit wat rechten betreft. Of dit nu op fileniveau (NTFS), register niveau, AD of SQL Server is enorm veel zaken af te schermen of te delegeren. Ook MS CRM zit wat dat betreft goed in elkaar. Ook de gekozen oplosrichting van MS CRM is goed, ik denk zelfs dat het een van de weinige is die echt goed kan werken.

Als je dan ook nog met een SQL 2008 server kan implementeren (kan je in de custom tabel gebruik maken van sparse columns).