[MySQL] 1 project, 3 verschillende fieldsets

[Verwijderd]

Ik heb een `project` table. Hierin staan alle projecten die via een webbased applicatie beheerd worden.

Per project worden bepaalde gegevens bijgehouden, oftewel een fieldset van verschillende formfields. Echter, afhankelijk van het type klant dat aan het project gekoppeld wordt, verschilt de fieldset.

In totaal zijn er 3 verschillende soorten klanten en dus 3 verschillende soorten fieldsets. Iedere fieldset heeft ongeveer tussen de 40 en 70 verschillende velden.

Wat is de beste methode om deze fieldsets aan het project te koppelen? Zelf dacht ik aan een aparte table per fieldset en vervolgens in de project table 2 extra kolommen: `fieldset_name` (verwijzen naar naam van table) en `fieldset_id` (verwijzend naar primary key in de betreffende table).

Echter, dat voelt gewoon niet goed.... Zijn er geen betere oplossingen voor te bedenken?

[JefSnare]

Heb je een structuur of een overzicht hoe je het nu zou doen? Je wilt dus nu koppelen met of zonder koppeltabel?

[WouZz]

Hier zijn verschillende oplossingen voor. Zoek even op inheritance rdbms

[Verwijderd]

WouZz schreef op dinsdag 08 december 2009 @ 16:29:
Hier zijn verschillende oplossingen voor. Zoek even op inheritance rdbms

Met dank aan die tip kwam ik uit op de volgende pagina. Ik neig naar de eerstgenoemde oplossing:
http://stackoverflow.com/...php-inheritance-and-mysql

Wat vinden jullie?

[ Voor 49% gewijzigd door Verwijderd op 08-12-2009 22:48 ]

[Verwijderd]

Ho eens, da's een nogal falende DB structuur. Als ik jou was zou ik eerst wat inlezen in normalisatie e.d.

[Verwijderd]

Zoeken op 'MySQL inheritance' levert me vooralsnog weinig op. Daarnaast leek 'Class Table Inheritance' me ook in de juiste richting te sturen, maar ook hier kan ik niet écht goede voorbeelden over vinden.

Dit is wat ik momenteel in gedachten heb:

SQL:

SELECT col1, col2, etc...,
    CASE
        WHEN i.id IS NOT NULL THEN 'individual'
        WHEN o.id IS NOT NULL THEN 'organisation'
    END as customer_type
FROM projects p
LEFT JOIN individuals i ON i.project_id = p.id
LEFT JOIN organisations o ON o.project_id = p.id
WHERE p.id = %u -- prepared statement (unsigned integer)

Vervolgens zorg ik er dan middels een MySQL trigger voor dat er per project maar 1 subtable (fieldset-table) gevuld kan worden.

Is dit een goede oplossing? Of zit ik al op de goede weg? Is er iemand die hier ervaring mee heeft?

[ Voor 156% gewijzigd door Verwijderd op 08-12-2009 22:53 . Reden: nadere specificatie ]

[Verwijderd]

Op het eerste gezicht, zou ik het volgende ERD maken:

[Project] 1 - 1 [FieldSet]

[FieldSet] N - N [FormField]

In mijn database krijg ik dan de volgende tabellen:

tblProject

tblFieldSet

tblFormField

tblFieldSetFields

Op deze manier kun je 'oneindig' verschillende fieldssets construeren uit bestaande form fields en die koppelen aan projecten voor 'oneindig' verschillende klanten.

[Verwijderd]

Verwijderd schreef op woensdag 09 december 2009 @ 11:51:
Op het eerste gezicht, zou ik het volgende ERD maken:

[Project] 1 - 1 [FieldSet]

[FieldSet] N - N [FormField]

Bedoel je niet:

[Project] N - 1 [FieldSet]

Immers, een FieldSet kan aan verscheidene projecten toebehoren. Anders kan je de FormFields net zo goed direct aan je project koppelen...

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 09:03:
[...]


Bedoel je niet:

[Project] N - 1 [FieldSet]

Immers, een FieldSet kan aan verscheidene projecten toebehoren. Anders kan je de FormFields net zo goed direct aan je project koppelen...

Zat ik inderdaad ook over te twijfelen, maar omdat ik niet meer info heb leek het me logisch dat een FieldSet specifiek voor een project wordt opgesteld.

Dus in mijn 'visie' maak je een nieuw project aan, construeer je een FieldSet uit een verzameling FormFields en koppel je deze aan je project.

Indien je meerdere typen projecten heb, zo je inderdaad een FieldSet kunnen koppelen aan meerdere projecten.

Overigens, ik neem aan dat je dan wel na invoeren van de veld waarden specifiek voor dit project, je de hele boel opslaat in één of meerdere result records in de database. De FieldSets gebruik je dan meer als metadata.

[ Voor 21% gewijzigd door Verwijderd op 10-12-2009 10:41 . Reden: Extra opmerking ]

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 10:38:
[...]
Overigens, ik neem aan dat je dan wel na invoeren van de veld waarden specifiek voor dit project, je de hele boel opslaat in één of meerdere result records in de database. De FieldSets gebruik je dan meer als metadata.

Ben je dan niet weer terug bij af? Dan zou je namelijk tables moeten creëren met kolommen voor in principe alle fields die je hebt??? Het idee is juist om efficienter te werkt te gaan en alleen kolommen te hebben voor data die je ook werkelijk op wilt slaan. Ervan uitgaande dat een gemiddelde fieldset 60 fields bevat; zou je dus bij 3 fieldsets een table krijgen met 180 columns, waarin telkens maar maximaal 60 fields beschreven worden...

Alternatief is om elke kolom als apart record op te slaan, maar dat zou weer een gigantische overhead opleveren.

Kortom, beide oplossingen hebben te grote bezwaren. Waar sla ik nu de plank mis?

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 11:13:
[...]

Ben je dan niet weer terug bij af? Dan zou je namelijk tables moeten creëren met kolommen voor in principe alle fields die je hebt??? Het idee is juist om efficienter te werkt te gaan en alleen kolommen te hebben voor data die je ook werkelijk op wilt slaan. Ervan uitgaande dat een gemiddelde fieldset 60 fields bevat; zou je dus bij 3 fieldsets een table krijgen met 180 columns, waarin telkens maar maximaal 60 fields beschreven worden...

Alternatief is om elke kolom als apart record op te slaan, maar dat zou weer een gigantische overhead opleveren.

Kortom, beide oplossingen hebben te grote bezwaren. Waar sla ik nu de plank mis?

Je ontkomt er volgens mij niet aan om alle waarden van een FormField ergens op te slaan. Als je die in de tabel met mogelijke FormFields zou opslaan, zou dit veld voor elk project overschreven worden toch?

Voorbeeldje:

Project -> één set met drie FormFields: Name, Due, Description.

Dan heb je een setje tabellen die de metadata van dit project vormen (hoe is het formulier opgebouwd). Stel de gebruiker vult dan in: "ProjectX", "12-12-2009", "Blaat". Dan zijn dit project specifieke waarden, die je dan weer in een tabel apart op moet slaan.

Ik zou inderdaad niet aanraden om voor alle mogelijke FormFields in de resultaat tabel een kolom te maken. Sowieso ben je dan op twee plaatsen hetzelfde bij aan het houden (stel we voegen toe: FormField StartDate, dan moet je in je resultaattabel ook een kolom StartDate toevoegen).

Je zou dan een tabel met FormFieldResults krijgen oid. Daarin hou je dan per ProjectID en FormFieldID een waarde bij.

Waarom zou dat overhead geven? Je hebt m.i. nu te maken met een Master-Detail structuur. Stel je hebt een offerte met offerte regels, dan dienen deze regels ook apart opgeslagen te worden.

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 11:52:
[...]Waarom zou dat overhead geven? Je hebt m.i. nu te maken met een Master-Detail structuur. Stel je hebt een offerte met offerte regels, dan dienen deze regels ook apart opgeslagen te worden.

Op zich neig ik er naartoe om iedere waarde als inderdaad als aparte record in de database op te slaan. Het is een correcte structuur en klopt wat normalisatie betreft ook prima. Ookal heb ik het al vaker gedaan, toch voelt het vreemd om domweg 60 records aan te maken in een table om alleen de door de gebruiker ingevoerde waarden in op te slaan. Die table krijgt dan in een niet al te lange periode al tienduizenden records te verorberen....

Het is InnoDB met uiteraard een goede indexstructuur enzo, dus op zich zou het snel genoeg moeten blijven. Zelfs met miljoenen records, toch?

[Woy]

tienduizenden records is geen reden om het niet te doen. Je moet alleen oppassen dat je het niet te flexibel wil maken en je bezig bent met het database in database anti-pattern.

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 12:26:
[...]

Op zich neig ik er naartoe om iedere waarde als inderdaad als aparte record in de database op te slaan. Het is een correcte structuur en klopt wat normalisatie betreft ook prima. Ookal heb ik het al vaker gedaan, toch voelt het vreemd om domweg 60 records aan te maken in een table om alleen de door de gebruiker ingevoerde waarden in op te slaan. Die table krijgt dan in een niet al te lange periode al tienduizenden records te verorberen....

Het is InnoDB met uiteraard een goede indexstructuur enzo, dus op zich zou het snel genoeg moeten blijven. Zelfs met miljoenen records, toch?

Ik snap wel wat je bedoelt, maar zoals hierboven gemeld is dat geen reden om het niet te doen. Die informatie is van belang voor de gebruiker, en dat moet dus opgeslagen worden. Daarnaast heb je het niet over honderden kolommen per FormFieldResult record, maar een koppeltabel record met drie velden (projectId, formFieldId, waarde).

De vraag is overigens niet eens of de database miljoenen records aan kan (lijkt me in grote server parken geen probleem) maar of de database dit gaat bereiken?

Je kunt er ook voor kiezen om de projecten database na een tijd op te schonen, waarom zou je afgeronden projecten na drie jaar nog willen vasthouden? (om maar iets te noemen, natuurlijk zijn er redenen te bedenken om het wel te doen ).

Daarnaast zou je alternatief zijn om het niet in rijen op te slaan, maar in kolommen. Dan snij je jezelf al in de eerste weken in de vingers omdat de gebruiker gaat vragen om een veldje toe te voegen.

Woy schreef op donderdag 10 december 2009 @ 12:32:
het database in database anti-pattern.

Net even hierop gegoogled, maar niks relevants kunnen vinden. Kun je hier iets meer over vertellen?

[ Voor 11% gewijzigd door Verwijderd op 10-12-2009 13:27 ]

[Woy]

Verwijderd schreef op donderdag 10 december 2009 @ 13:24:
[...]
Net even hierop gegoogled, maar niks relevants kunnen vinden. Kun je hier iets meer over vertellen?

Ik heb even geen tijd om een uitgebreid verhaal te typen, maar hier gaat het er ook over: \[MySQL/Alg] - Database flexbiliteit door variabele velden

[Verwijderd]

Verwijderd schreef op dinsdag 08 december 2009 @ 18:08:

SQL:

SELECT col1, col2, etc...,
    CASE
        WHEN i.id IS NOT NULL THEN 'individual'
        WHEN o.id IS NOT NULL THEN 'organisation'
    END as customer_type
FROM projects p
LEFT JOIN individuals i ON i.project_id = p.id
LEFT JOIN organisations o ON o.project_id = p.id
WHERE p.id = %u -- prepared statement (unsigned integer)

Als ik een record INSERT in de table 'individuals' met daarin een project_id die al voorkomt in de table 'organisations', hoe kan ik deze INSERT dan annuleren middels een trigger?

Zoektochten brachten mij tot een "BEFORE INSERT ON" trigger gecombineerd met een RAISE_ERROR. Maar genoeg info om tot de juiste syntax te komen kon ik nergens vinden. Toevallig iemand hier die me kan helpen?

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 14:31:
[...]


Als ik een record INSERT in de table 'individuals' met daarin een project_id die al voorkomt in de table 'organisations', hoe kan ik deze INSERT dan annuleren middels een trigger?

Zoektochten brachten mij tot een "BEFORE INSERT ON" trigger gecombineerd met een RAISE_ERROR. Maar genoeg info om tot de juiste syntax te komen kon ik nergens vinden. Toevallig iemand hier die me kan helpen?

Waarom zou je dat krijgen? Misschien heb ik meer info over de context nodig. Maar een project wordt toch alleen aan of een individual of een organisatie toegekend? Maw, je hebt toch maar één project eigenaar? Of bedoel je iets anders?

Je zou zoiets ook op kunnen lossen in een transactie, als iets fout gaat (PK violation in jou geval) dan kun je middels rollback de INSERT (en alles wat er verder gebeurd is) terugdraaien.

Woy schreef op donderdag 10 december 2009 @ 13:44:
[...]

Ik heb even geen tijd om een uitgebreid verhaal te typen, maar hier gaat het er ook over: \[MySQL/Alg] - Database flexbiliteit door variabele velden

Dank je. Dat kan hier inderdaad ook gebeuren als je teveel gaat specificeren van een bepaald FormField dat het bijna een database op zichzelf wordt.

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 15:54:
[...]
Waarom zou je dat krijgen? Misschien heb ik meer info over de context nodig. Maar een project wordt toch alleen aan of een individual of een organisatie toegekend? Maw, je hebt toch maar één project eigenaar? Of bedoel je iets anders?
[...]

Wat ik wil bereiken is dat het onmogelijk wordt om een record (voorzien van project_id=y) te inserten in `individuals`, als er in `organisations` al een record bestaat met project_id=y. En uiteraard moet het andersom dan ook niet kunnen.

Paar dagen geleden kwam ik het ideale voorbeeld tegen op een website die gebruik maakte van een trigger, maar kan het na uren googlen niet meer terugvinden. En laat m'n browser z'n history nou net opschonen als ie afgesloten wordt

Volgens mij was het zoiets; alleen dit werkt dus niet :(:

SQL:

CREATE TRIGGER prevent_doubles
BEFORE INSERT ON individuals
FOR EACH ROW
IF (new.project_id IN (
    SELECT project_id
    FROM organisations
    WHERE project_id = new.project_id
)) THEN
    CALL RAISE_ERROR();
END IF;

[ Voor 14% gewijzigd door Verwijderd op 10-12-2009 17:18 ]

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 14:31:
[...]
Als ik een record INSERT in de table 'individuals' met daarin een project_id die al voorkomt in de table 'organisations', hoe kan ik deze INSERT dan annuleren middels een trigger?

Heb je je relaties niet verkeerd om liggen? Je hebt nu vanuit 'individuals' en vanuit 'organisations' een verwijzing naar een project. Maar tegelijkertijd wil je dat maar één van de twee naar een bepaald project kan wijzen. Het lijkt mij makkelijker om de verwijzing andersom te leggen: dus in 'project' een foreign key naar individuals en een foreign key naar organisations opnemen. Het is een stuk makkelijker te controleren dat maar één van de twee velden is ingevuld.

Gevolg van deze aanpak is natuurlijk wel dat een individual of een organisation meer projecten kan hebben; ik weet niet of dit een probleem vormt.

[Verwijderd]

Verwijderd schreef op donderdag 10 december 2009 @ 16:49:
[...]

Wat ik wil bereiken is dat het onmogelijk wordt om een record (voorzien van project_id=y) te inserten in `individuals`, als er in `organisations` al een record bestaat met project_id=y. En uiteraard moet het andersom dan ook niet kunnen.

Helaas heb weinig ervaring met PL/SQL-achtig constructies, maar wat je beschrijft zou ik niet in de database afvangen. Dat is m.i. business logic die je ook in die laag dus af dient te vangen. Wat je feitelijk zegt is "Poject X kan niet toegekend worden aan Individual Y als deze reeds aan Organisation Z toegekend is."

Een transactie (wat ik eerder aangaf) zou ik hiervoor ook niet gebruiken, want dat zou meer een technische oplossing zijn om atomaire acties tegen de database te garanderen.

[Verwijderd]

Verwijderd schreef op vrijdag 11 december 2009 @ 09:10:
[...]


Heb je je relaties niet verkeerd om liggen? Je hebt nu vanuit 'individuals' en vanuit 'organisations' een verwijzing naar een project. Maar tegelijkertijd wil je dat maar één van de twee naar een bepaald project kan wijzen. Het lijkt mij makkelijker om de verwijzing andersom te leggen: dus in 'project' een foreign key naar individuals en een foreign key naar organisations opnemen. Het is een stuk makkelijker te controleren dat maar één van de twee velden is ingevuld.

Gevolg van deze aanpak is natuurlijk wel dat een individual of een organisation meer projecten kan hebben; ik weet niet of dit een probleem vormt.

De structuur lijkt op het oog wat vreemd idd, maar moet (helaas) toch echt zo in elkaar komen te zitten. Eén van de redenen daarvoor is, zoals jij ook al aangaf, dat een project maar 1 individual OF 1 organisation mag hebben.

Verwijderd schreef op vrijdag 11 december 2009 @ 09:11:
[...]


Helaas heb weinig ervaring met PL/SQL-achtig constructies, maar wat je beschrijft zou ik niet in de database afvangen. Dat is m.i. business logic die je ook in die laag dus af dient te vangen. Wat je feitelijk zegt is "Poject X kan niet toegekend worden aan Individual Y als deze reeds aan Organisation Z toegekend is."

Een transactie (wat ik eerder aangaf) zou ik hiervoor ook niet gebruiken, want dat zou meer een technische oplossing zijn om atomaire acties tegen de database te garanderen.

Je hebt helemaal gelijk. Ik ga dit ook volledig in de code afvangen. Toch misstaat het mijns inziens niet om op database niveau nog een extra check uit te voeren... De integriteit van de database valt immers niet 100% te garanderen als je niet op databaseniveau nog zo'n check uitvoert... Beertje zelfde principe dus als dat van de ON DELETE CASCADE referential integrity eigenlijk. Helaas kan ik die hier niet voor gebruiken, dus zal het met een Trigger moeten gebeuren.

[Verwijderd]

Verwijderd schreef op vrijdag 11 december 2009 @ 12:20:
[...]
De structuur lijkt op het oog wat vreemd idd, maar moet (helaas) toch echt zo in elkaar komen te zitten. Eén van de redenen daarvoor is, zoals jij ook al aangaf, dat een project maar 1 individual OF 1 organisation mag hebben.

Andersom, bedoel je. Jouw opzet zorgt ervoor dat een individu maar één project kan hebben; idem voor een organisatie. Het kan nog steeds zo zijn dat verschillende individuen hetzelfde project hebben - met andere woorden, een project kan meer individuen / organisaties hebben.

Door de relaties andersom te leggen, zorg je ervoor dat een individu / organisatie meer projecten kan hebben, maar ieder project maar één individu / organisatie. Een n op 1 relatie tussen individu en project, in plaats van 1 op n.

[Verwijderd]

Verwijderd schreef op vrijdag 11 december 2009 @ 12:20:
Je hebt helemaal gelijk. Ik ga dit ook volledig in de code afvangen. Toch misstaat het mijns inziens niet om op database niveau nog een extra check uit te voeren... De integriteit van de database valt immers niet 100% te garanderen als je niet op databaseniveau nog zo'n check uitvoert... Beertje zelfde principe dus als dat van de ON DELETE CASCADE referential integrity eigenlijk. Helaas kan ik die hier niet voor gebruiken, dus zal het met een Trigger moeten gebeuren.

Ik vraag me altijd af of dat inderdaad het geval is. Feitelijk ontwerp je je business laag atlijd zonder dat je weet dat je een database gaat gebruiken (dat is in 90% van de gevallen natuurlijk wel zo, maar je mag er niet vanuit gaan).

Nu ga je een soortgelijke check ook in de database doorvoeren, met de kans dat wanneer in de toekomst de business rules gaan veranderen of minder strict worden je dus weer op meerdere plaatsen dezelfde wijzigingen moet doorvoeren.

Bij validaties en business rules probeer ik te bepalen hoe de functionaliteit aangesproken kan worden en daarmee te bepalen waar de controles gedaan moeten worden. Het zou zo maar kunnen zijn dat jij je check ook in de database bouwt terwijl deze niet eens van buitenaf aangesproken kan worden. Alle communicatie gaat via de business laag dus de informatie wordt altijd gevalideerd tegen de geldende rules.

Maar inderdaad heb je gelijk dat deze situatie lijkt op on cascade delete. Ik heb in een webproject logica in de business laag gebouwd die de cascade deletions uitvoert, omdat de leidende MySQL versie (van de hosting provider) dit simpelweg niet ondersteund .... Maar feitelijk wil je dit niet in je code, maar in dit de database configureren op foreign key niveau.

[Verwijderd]

Verwijderd schreef op vrijdag 11 december 2009 @ 12:58:
[...]
Andersom, bedoel je. [...]

Het is een 1-op-1 relatie. D.w.z. organisations.contact_id en individuals.contact_id hebben een UNIQUE index.

[ Voor 33% gewijzigd door Verwijderd op 11-12-2009 13:32 ]

[Verwijderd]

Verwijderd schreef op vrijdag 11 december 2009 @ 13:20:
[...]

Het is een 1-op-1 relatie. D.w.z. organisations.contact_id en individuals.contact_id hebben een UNIQUE index.

Leg de relaties dan andersom: geef project een foreign key naar individuals en een naar organisations; geef beide velden een UNIQUE constraint; probleem opgelost. Je kan nu geen projecten meer aanmaken die verwijzen naar een individual of een organisation die al een project hebben. Als mySQL ook CHECK constraints kent, kan je er ook voor zorgen dat een project altijd of een individu, of een organisatie heeft.