Databasedesign samples

[Hoeloeloe]

Voor een verzameling samples die ik hier heb wil ik graag een database opstellen waarin ik kan bijhouden welke samples er zijn, welke eigenschappen ze hebben, welke behandelingen ze hebben ondergaan, etc.,.etc. Als ik dan een sample in handen heb wil ik een overzicht kunnen genereren opdat duidelijk wordt wat de geschiedenis is van dat sample.

Waar ik mee zit is het bijhouden van de behandelingen. Dit kan een reiniging zijn, een experiment, een fysieke aanpassing. Kortom, allerlei verschillende behandelingen met hun eigen parameters.

Mijn huidige idee:
tabel 1 bevat alle samples met een uniek Id, naam, aanmaakdatum en andere niet veranderende gegevens
tabel 2 bevat alle kolommen nodig om behandeling X te beschrijven inclusief behandeldatum en een kolom voor het sample id
tabel 3 bevat alle kolommen nodig om behandeling Y te beschrijven inclusief behandeldatum en een kolom voor het sample id
etc.

Om dan een overzicht te krijgen voor sample i ga ik dan alle tabellen met behandelingen langs, verkrijg de rijen over sample i en sorteer de verzameling op behandeldatum om een geschiedenis te creëren.

Mijn probleem is dat hierdoor voor elk type behandeling die toegevoegd wordt de query weer groter wordt. Is hier een oplossing voor om deze geschiedenis automatisch te genereren, ook als er een nieuwe tabel met een behandeling bijgevoegd wordt?
Daarnaast: is dit de juiste aanpak? is hier een standaardaanpak voor? En welke zoektermen kan ik daar voor gebruiken?

[lordprimoz]

is het volgende niet handiger:
tabel1 (Samples):
+SampleID(pkey int)
-name (varchar)
-invoer(datetime)
-etc...

tabel2(Operation):
+operationID
-operationName
-etc...

tabel3(OperationParameters):
+operationParameterID
-operationID
-operationParameterName
-value

tabel4(Process):
+processID
-sampleID
-operationID
-handleDate


waarbij de verschillende tabellen onderling aan elkaar gelinked worden via de keys.


Hierdoor kan je naar hartelust behandelingen(operations) toevoegen aan de database met de bijhorende parameters. de Samples tabel houd bij welke samples je allemaal hebt en de Process tabel alle processen die je Sample heeft gehad.

[ Voor 22% gewijzigd door lordprimoz op 13-10-2017 14:14 ]

[Flythe]

Waar je naar op zoek bent heet waarschijnlijk provenance of data lineage, dat is een heel onderzoeksveld op zich. Zie bijvoorbeeld dit paper waar op pagina 8 een relational schema voor data provenance staat.

[eric.1]

Teken het eens uit zou ik zeggen, let dan vooral op de onderlinge relaties.

Dit klinkt eerlijk gezegd (zoals ik het nu lees) als een niet heel ingewikkelde many-to-many relatie tussen sample en behandeling.

[kwaakvaak_v2]

Flythe schreef op vrijdag 13 oktober 2017 @ 16:02:
Waar je naar op zoek bent heet waarschijnlijk provenance of data lineage, dat is een heel onderzoeksveld op zich. Zie bijvoorbeeld dit paper waar op pagina 8 een relational schema voor data provenance staat.

Zo dan... laat het maar aan de wetenschap over om van iets relatief simpels als een logboek++ iets super ingewikkelds te maken


Voor de TS, het magische woord wat je denk ik zoekt is een polymorphic pivot table

[ Voor 8% gewijzigd door kwaakvaak_v2 op 13-10-2017 17:20 . Reden: poly... table. ]

[Mugwump]

Als ik het goed begrijp is het enige dat je wilt doen wat betreft interactie het aanmaken van samples, het toevoegen van behandelingen aan samples en de details en behandelhistorie van samples te kunnen bekijken per individueel sample.
Wat dat betreft is de vraag of je per se een relationele database wilt gebruiken, als je daar tenminste niet aan vast zit. Klinkt als een situatie waarin een document database prima past, denk aan iets als MongoDb. Dat laat je veel vrijer in wat je over behandelingen wilt opslaan.
De voorgestelde relationele structuur met een generieke tabel voor behandelingen en parameters werkt ook wel, maar de vraag is in hoeverre je daar ook specifiek op zou willen zoeken.
Heb je naast simpelweg behandelhistorie ook nog de wens om te zoeken op alle samples die behandeling X hebben gehad waarbij parameter Y een specifieke waarde heeft, dan is die relationele structuur wellicht handig.

Ander aandachtspuntje is hoe je data gaat invoeren en opvragen. Gaat dat middels een (zelfgebouwde) applicatie of was je van plan om dat rechtstreeks te doen met database queries?

[DJMaze]

Hoeloeloe schreef op vrijdag 13 oktober 2017 @ 13:58:
Mijn probleem is dat hierdoor voor elk type behandeling die toegevoegd wordt de query weer groter wordt. Is hier een oplossing voor om deze geschiedenis automatisch te genereren, ook als er een nieuwe tabel met een behandeling bijgevoegd wordt?
Daarnaast: is dit de juiste aanpak? is hier een standaardaanpak voor? En welke zoektermen kan ik daar voor gebruiken?

Het is niet de juiste aanpak.
Ja, er is een oplossing en die heet many-to-many

Zoiets kom je dan op uit

SQL:

CREATE TABLE behandelingen (
    behandeling_id,
    behandeling_naam
);

CREATE TABLE samples (
    sample_id,
    sample_naam,
    sample_aanmaakdatum
    sample_andere_gegevens
);

/* associative table */
CREATE TABLE samples_behandelingen (
    sample_id,
    behandeling_id,
    behandeldatum,
    notities
);

Vervolgens kan je dan voor de sample behandel historie een join doen met een order

SQL:

SELECT
    *
FROM samples_behandelingen
LEFT JOIN behandelingen USING (behandeling_id)
WHERE sample_id = 1
ORDER BY behandeldatum DESC;

Of als je wil weten welke samples behandeling 1 hebben gehad

SQL:

SELECT
    *
FROM samples_behandelingen
LEFT JOIN samples USING (sample_id)
WHERE behandeling_id = 1
ORDER BY behandeldatum DESC;

[ Voor 6% gewijzigd door DJMaze op 15-10-2017 00:34 ]

[Hoeloeloe]

lordprimoz schreef op vrijdag 13 oktober 2017 @ 14:08:
is het volgende niet handiger:
...

Dat lijkt inderdaad op een structuur waar ik mee uit de voeten kan.

Flythe schreef op vrijdag 13 oktober 2017 @ 16:02:
Waar je naar op zoek bent heet waarschijnlijk provenance of data lineage, dat is een heel onderzoeksveld op zich. Zie bijvoorbeeld dit paper waar op pagina 8 een relational schema voor data provenance staat.

Inderdaad, de Entity -> Activity -> Attributes relatie is wat ik wil bereiken, bedankt voor het artikel en de zoekterm.

kwaakvaak_v2 schreef op vrijdag 13 oktober 2017 @ 17:18:
[...]
Voor de TS, het magische woord wat je denk ik zoekt is een polymorphic pivot table

Het woord zal ik meenemen in de zoektocht. Dank!

DJMaze schreef op zondag 15 oktober 2017 @ 00:28:
[...]

Het is niet de juiste aanpak.
Ja, er is een oplossing en die heet many-to-many

Aangezien elke gelijke behandeling nog weer zijn eigen parameters kan hebben komt hier dan, als ik het goed begrijp, nog weer een -to-many koppeling aan vast. Deels zoals lordprimoz het voorstelt.

Ik heb in ieder geval genoeg leesvoer en termen om mee aan de slag te gaan. Hartelijk dank.

[ard1998]

probeer je database te virtualiseren met een gratis tool zoals staruml http://staruml.io en instaleer de ddl plugin om jouw visualisatie van je database om te zetten in een script dat de database voor je genereert.