[SQL] Prijstabel

[Snippo]

Mijn vraag
...
Ik wil een tabel maken waar ik de prijs per datum bij kan houden van een aantal categorieën (bijv A, B, C, D).
Op dit moment heb ik een tabel met drie kolommen: categorie, prijs en datum.
Dat werkt in principe prima, maar ik wil graag per datum de prijs kunnen invoeren of opvragen.
Wat het handigste lijkt is dan een tabel met kolommen: datum, A, B, C en D waarin per record vervolgens de datum en prijs is ingevoerd. Dit werkt echter ook niet omdat ik het dan niet voor elkaar krijg om van een product met categorie A vervolgens de bijbehorende prijs te krijgen (met bijv JOIN). Daarnaast is het volgens mij een slecht design, maar ik heb er verder weinig verstand van.

Ik vraag me dus af hoe ik een tabel maak waarin ik per datum de prijs kan invullen of opvragen, en daarbij ook de prijs per categorie kan "joinen" op een andere tabel. Volgens mij is het of simpel, of niet mogelijk, maar ik kom er niet uit .

Relevante software en hardware die ik gebruik
...
MySQL

Wat ik al gevonden of geprobeerd heb
...
Zie vraag.

[Ventieldopje]

Gewoon de tabel zoals je nu hebt: categorie prijs datum.

SQL:

SELECT * FROM jouwtabel WHERE datum = ...

In je code verder gewoon overheen loopen..

Dan als je de prijzen in wil geven doe je gewoon voor elke categorie een INSERT. Mist er dan een prijs voor een categorie, dan insert je die dus ook niet. Voordeel is dat je heel flexibel bent al kost het wel veel records. Afhankelijk van de interval en aantal categorieën misschien niet erg efficient.

Je zou voor veel data ook kunnen kijken naar MongoDB bijvoorbeeld

[Snippo]

Dat gaat ook werken inderdaad. Die had ik zelf ook bedacht maar ik dacht dat er misschien wel een functie zou zijn die het 'automatisch' deed.
Het zijn overigens maar een paar records (prijs verandert ieder jaar ongeveer), dus qua performance maakt het weinig uit.

Bedankt .

[sig69]

Slecht design: ja. Ik lees categorieën, in een goed design zou je op zijn minst een categorie tabel hebben. Zodra je dingen gaat doen als kolom A, B, C of 1, 2, 3 zou je al even na moeten gaan denken.

Ventieldopje schreef op vrijdag 07 oktober 2016 @ 18:45:
Gewoon de tabel zoals je nu hebt: categorie prijs datum.

SQL:

SELECT * FROM jouwtabel WHERE datum = ...

In je code verder gewoon overheen loopen..

Dan als je de prijzen in wil geven doe je gewoon voor elke categorie een INSERT. Mist er dan een prijs voor een categorie, dan insert je die dus ook niet. Voordeel is dat je heel flexibel bent al kost het wel veel records. Afhankelijk van de interval en aantal categorieën misschien niet erg efficient.

Je zou voor veel data ook kunnen kijken naar MongoDB bijvoorbeeld

Ja natuurlijk, gewoon lekker "select * from" voor alles. En waarom kom je ineens met mongodb aan? Het "beste antwoord" had in dit geval niet slechter kunnen zijn volgens mij.

Snippo schreef op vrijdag 07 oktober 2016 @ 19:11:
Dat gaat ook werken inderdaad. Die had ik zelf ook bedacht maar ik dacht dat er misschien wel een functie zou zijn die het 'automatisch' deed.
Het zijn overigens maar een paar records (prijs verandert ieder jaar ongeveer), dus qua performance maakt het weinig uit.

Bedankt .

Dat het om weinig records gaat wil niet zeggen dat je het niet goed hoeft te doen.

[ Voor 86% gewijzigd door sig69 op 08-10-2016 10:41 ]

[Ventieldopje]

sig69 schreef op zaterdag 08 oktober 2016 @ 10:28:
Slecht design: ja. Ik lees categorieën, in een goed design zou je op zijn minst een categorie tabel hebben. Zodra je dingen gaat doen als kolom A, B, C of 1, 2, 3 zou je al even na moeten gaan denken.

[...]

Ja natuurlijk, gewoon lekker "select * from" voor alles. En waarom kom je ineens met mongodb aan? Het "beste antwoord" had in dit geval niet slechter kunnen zijn volgens mij.

[...]

Dat het om weinig records gaat wil niet zeggen dat je het niet goed hoeft te doen.

Opdracht voor de lezer nietwaar? Maar volgens mij is er een met het verkeerde been uit bed gestapt.

[Snippo]

sig69 schreef op zaterdag 08 oktober 2016 @ 10:28:
Slecht design: ja. Ik lees categorieën, in een goed design zou je op zijn minst een categorie tabel hebben. Zodra je dingen gaat doen als kolom A, B, C of 1, 2, 3 zou je al even na moeten gaan denken.

[...]

Ja natuurlijk, gewoon lekker "select * from" voor alles. En waarom kom je ineens met mongodb aan? Het "beste antwoord" had in dit geval niet slechter kunnen zijn volgens mij.

[...]

Dat het om weinig records gaat wil niet zeggen dat je het niet goed hoeft te doen.

Als je nu even uitlegt hoe het wel moet dan weet ik dat ook weer. Dat is ook de reden dat ik het vraag...
Dan kan het "beste antwoord" ook aangepast worden.

[DJMaze]

Ik denk dat je de vraag verkeerd stelt.

Je bedoelt eerder:

SQL:

SELECT prijs FROM jouwtabel
WHERE category_id = 1
  AND vanaf_datum >= CURRENT_DATE
ORDER BY vanaf_datum
LIMIT 1

Want je vertelt in een latere post dat de prijs heel weinig wordt aangepast. Een tabel met 365 records waarin de prijs 365x het zelfde is (1 januari t.m. 31 december) is dan zinloos.

[ Voor 3% gewijzigd door DJMaze op 08-10-2016 15:27 ]

[sig69]

Snippo schreef op zaterdag 08 oktober 2016 @ 14:22:
[...]

Als je nu even uitlegt hoe het wel moet dan weet ik dat ook weer. Dat is ook de reden dat ik het vraag...
Dan kan het "beste antwoord" ook aangepast worden.

Een select * geeft alle kolommen terug, ook wat je niet nodig hebt. Zal in dit geval waarschijnlijk niet veel uitmaken, maar ik kom wel eens queries tegen.. Een tabel waarin bestanden opgeslagen worden als extreem voorbeeld, als je dan select * gebruikt trek je dus elke keer ook die bestanden uit de database terwijl je die wellicht helemaal niet nodig hebt.

[Snippo]

DJMaze schreef op zaterdag 08 oktober 2016 @ 15:26:
Ik denk dat je de vraag verkeerd stelt.

Je bedoelt eerder:

SQL:

SELECT prijs FROM jouwtabel
WHERE category_id = 1
  AND vanaf_datum >= CURRENT_DATE
ORDER BY vanaf_datum
LIMIT 1

Want je vertelt in een latere post dat de prijs heel weinig wordt aangepast. Een tabel met 365 records waarin de prijs 365x het zelfde is (1 januari t.m. 31 december) is dan zinloos.

De datum is inderdaad een 'vanaf_datum', dat was misschien niet helemaal duidelijk inderdaad.
Als de prijs op 1 januari veranderd komt er dus maar één record bij.
Qua tabel lijkt het dus goed te zijn, maar dan moet ik alleen nog een makkelijke manier zien te vinden om de waardes op te vragen en toe te voegen in de tabel (waarbij ik dus per datum de nieuwe prijzen in voer).
Dit voelt alleen zo omslachtig aan omdat je niet met één record alles tegelijk kan toevoegen of opvragen. Ik had gehoopt dat er iets mogelijk was met JOIN oid.

sig69 schreef op zaterdag 08 oktober 2016 @ 15:38:
[...]

Een select * geeft alle kolommen terug, ook wat je niet nodig hebt. Zal in dit geval waarschijnlijk niet veel uitmaken, maar ik kom wel eens queries tegen.. Een tabel waarin bestanden opgeslagen worden als extreem voorbeeld, als je dan select * gebruikt trek je dus elke keer ook die bestanden uit de database terwijl je die wellicht helemaal niet nodig hebt.

Dat begrijp ik, maar wat is er verder mis in dit specifieke geval? Wat jij beschrijft is meer een algemeen 'probleem'. Hoe zou ik de database moeten designen om het goed te doen? Net als een "SELECT *" is er waarschijnlijk een makkelijke en een juiste manier, ik zoek dus de juiste manier.

[Ventieldopje]

sig69 schreef op zaterdag 08 oktober 2016 @ 15:38:
[...]

Een select * geeft alle kolommen terug, ook wat je niet nodig hebt. Zal in dit geval waarschijnlijk niet veel uitmaken, maar ik kom wel eens queries tegen.. Een tabel waarin bestanden opgeslagen worden als extreem voorbeeld, als je dan select * gebruikt trek je dus elke keer ook die bestanden uit de database terwijl je die wellicht helemaal niet nodig hebt.

Dus dan fijn maar het even je gal spuwen? Goed bezig

[The Eagle]

Ah, effective dating. Slechts 10 jaar met dit soort dingen gespeeld. De truc: maak de datum een sleutelveld van je tabel, naast het artikelnummer uiteraard. Zo maak je de datum verplicht bij het invoeren van gegevens. Kan default gewoon de sysdate zijn natuurlijk. En voor iedere wijziging voeg je gewoon een rij toe met de bestaande en gewijzigde gegevens en een nieuwe datum.

Bij het ophalen van de datum en andere details van het artikel kun je dan automagisch op de meest recente datum selecteren, terwijl je ook toekomstige wijzigingen alvast op kunt slaan.
Ophalen van de op enig huidig moment geldige artikelgegevens:

SQL:

Select artnr, descr, comments from artikel a
Where a.date =
  (Select max (ad.date) from artikel ad
    where ad.artnr = a.artnr
    And ad.date <= sysdate)

[ Voor 22% gewijzigd door The Eagle op 08-10-2016 16:25 ]

[Snippo]

Met sleutelveld bedoel je een PRIMARY KEY? Zo ja, hoe voer ik dan voor verschillende categorieën op dezelfde datum de nieuwe prijs in? Aangezien de PRIMARY KEY geen duplicates mag hebben.

[The Eagle]

Primary key ja. Die mag best samengesteld zijn
Lees in mijn voorbeeld even categorie waar nu artnr staat.
Uitgaand van de velden
1. Categorie
2. Datum
3. Prijs

Waarbij 1 en 2 dan de samengestelde primary key vormen, want zoals je al zegt is de categorie unike, en de datum per categorie dus ook. Dat is je eerste tabel.

In een evt andere tabel kun je dan heel simpel je categorie opnemen. Vervolgens moet je idd gaan joinen op categorie en die effective dating clausule meegeven. Dat wordt een leuke sql om te schrijven, dus als je slim bent maak je een view die van de hele categorieentabel de effdt ophaalt zoals ik boven omschreef. Is feitelijk de tabeldefinitie met de datumclausule. En die view join je dan in je select, heb je altijd de juiste geldende prijs

[Snippo]

Ik wist niet dat dat ook mogelijk was. Er komen al wat termen voorbij waarvan ik maar amper weet wat het is, dus ik ga er mee aan de slag. Bedankt .

[Cartman!]

Ventieldopje schreef op vrijdag 07 oktober 2016 @ 18:45:
Je zou voor veel data ook kunnen kijken naar MongoDB bijvoorbeeld

Of niet

http://cryto.net/~joepie9...er-ever-ever-use-mongodb/

[Snippo]

Die link kwam ik toevallig net tegen op Google, moest ik ook meteen aan het advies van Ventieldopje denken .
Heb het overigens niet gelezen want ik ga het (nu) sowieso niet gebruiken.

[Blokker_1999]

The Eagle schreef op zaterdag 08 oktober 2016 @ 16:22:
Ah, effective dating. Slechts 10 jaar met dit soort dingen gespeeld. De truc: maak de datum een sleutelveld van je tabel, naast het artikelnummer uiteraard. Zo maak je de datum verplicht bij het invoeren van gegevens. Kan default gewoon de sysdate zijn natuurlijk. En voor iedere wijziging voeg je gewoon een rij toe met de bestaande en gewijzigde gegevens en een nieuwe datum.

Bij het ophalen van de datum en andere details van het artikel kun je dan automagisch op de meest recente datum selecteren, terwijl je ook toekomstige wijzigingen alvast op kunt slaan.
Ophalen van de op enig huidig moment geldige artikelgegevens:

SQL:

Select artnr, descr, comments from artikel a
Where a.date =
  (Select max (ad.date) from artikel ad
    where ad.artnr = a.artnr
    And ad.date <= sysdate)

Heeft het doen van een nieuwe query voor de laatste datum een voordeel op het simpelweg sorteren op datum en het resultaat beperken tot 1?

[Ventieldopje]

Cartman! schreef op zaterdag 08 oktober 2016 @ 17:10:
[...]

Of niet

http://cryto.net/~joepie9...er-ever-ever-use-mongodb/

Het ging mij om het NoSQL principe. Dit is net als zeggen dat MySQL Server evil is en je mariadb moet pakken. Moeten we alles voorkauwen?

[Snippo]

Ik heb overigens een manier gevonden om de data te laden zoals ik beschreef in de eerste post:

code:

SELECT date,
   MAX(CASE WHEN category = 'A' THEN price END) A,
   MAX(CASE WHEN category = 'B' THEN price END) B,
   MAX(CASE WHEN category = 'C' THEN price END) C,
   MAX(CASE WHEN category = 'D' THEN price END) D
   FROM Prices
GROUP BY date;

Zijn er nog bezwaren om het niet zo te doen, of is dit prima zo?

[Cartman!]

Ventieldopje schreef op zaterdag 08 oktober 2016 @ 17:24:
Het ging mij om het NoSQL principe.

Dan zeg je dat toch ipv specifiek 1 smaakje er tussenuit te pakken?

Dit is net als zeggen dat MySQL Server evil is en je mariadb moet pakken. Moeten we alles voorkauwen?

Ben ik het niet helemaal mee eens. MySQL heeft zich al bewezen en als je voorkeur uit gaat naar MariaDB dan is dat een redelijk gelijkwaardig alternatief maar dat maakt MySQL nog geen slechte keus. Meestal worden PostgreSQL en Oracle ook los genoemd.

---

Sowieso zie ik niet waarom je NoSQL zou willen voorstellen in dit scenario, dit is vrij duidelijk werk voor een RDBMS.

[ Voor 9% gewijzigd door Cartman! op 08-10-2016 19:09 ]

[CurlyMo]

Snippo schreef op zaterdag 08 oktober 2016 @ 18:18:
Ik heb overigens een manier gevonden om de data te laden zoals ik beschreef in de eerste post:

code:

SELECT date,
   MAX(CASE WHEN category = 'A' THEN price END) A,
   MAX(CASE WHEN category = 'B' THEN price END) B,
   MAX(CASE WHEN category = 'C' THEN price END) C,
   MAX(CASE WHEN category = 'D' THEN price END) D
   FROM Prices
GROUP BY date;

Zijn er nog bezwaren om het niet zo te doen, of is dit prima zo?

De vraag is hoe flexibel je ontwerp nog is. Wat nu als er ooit een categorie E komt of categorie C komt te vervallen?

Waarom maak je niet gewoon twee tabellen:

Categorieën
- id
- code
- naam

Prijzen
- id
- categorie_id
- prijs
- datum

[Reinier]

Ik zou de prijstabel een begin- en einddatum geven, en evt. een "is_actueel" o.i.d. Dat query't eenvoudiger lijkt me.

[Snippo]

CurlyMo schreef op zaterdag 08 oktober 2016 @ 19:20:
[...]

De vraag is hoe flexibel je ontwerp nog is. Wat nu als er ooit een categorie E komt of categorie C komt te vervallen?

Waarom maak je niet gewoon twee tabellen:

Categorieën
- id
- code
- naam

Prijzen
- id
- categorie_id
- prijs
- datum

Dat is inderdaad een probleem, maar hoe krijg ik met die twee tabellen zo'n zelfde resultaat?
Zoals je zegt is die query niet echt flexibel. Overigens zullen de categorieën waarschijnlijk nooit veranderen, maar het gaat me meer om het principe (stel dat het wel gebeurt, dan kan je alles handmatig aan gaan passen).

@Reinier
Zoals hierboven ook al in een reactie staat krijg je door een query met start_date < datum ORDER BY start_date LIMIT 1 ook de meest actuele prijs, dus twee extra kolommen lijkt me overbodig.

[CurlyMo]

Snippo schreef op zaterdag 08 oktober 2016 @ 19:57:
[...]

Dat is inderdaad een probleem, maar hoe krijg ik met die twee tabellen zo'n zelfde resultaat?
Zoals je zegt is die query niet echt flexibel. Overigens zullen de categorieën waarschijnlijk nooit veranderen, maar het gaat me meer om het principe (stel dat het wel gebeurt, dan kan je alles handmatig aan gaan passen).

Ik kan We kunnen het voorkauwen, maar je leert er meer van als je eerst zelf een poging plaatst. Daarnaast is het handig te weten welke database software je gebruikt.

Reinier schreef op zaterdag 08 oktober 2016 @ 19:41:
Ik zou de prijstabel een begin- en einddatum geven, en evt. een "is_actueel" o.i.d. Dat query't eenvoudiger lijkt me.

Een tijdvak heeft alleen zin als het om gegevens gaat die parallel kunnen bestaan zoals een nationaliteit. Daar kan je het op verschillende momenten in de tijd meer van hebben. Als het echter om seriële gegevens gaat dan is een datum genoeg, zoals bij een woonplaats. De begin datum van een volgend record is dan tegelijk de eind datum van het vorige record. Anders moet je telkens de begin / eind datum synchroniseren tussen records.

[ Voor 36% gewijzigd door CurlyMo op 08-10-2016 21:31 ]

[Jovaro]

Snippo schreef op zaterdag 08 oktober 2016 @ 18:18:
Ik heb overigens een manier gevonden om de data te laden zoals ik beschreef in de eerste post:

code:

SELECT date,
   MAX(CASE WHEN category = 'A' THEN price END) A,
   MAX(CASE WHEN category = 'B' THEN price END) B,
   MAX(CASE WHEN category = 'C' THEN price END) C,
   MAX(CASE WHEN category = 'D' THEN price END) D
   FROM Prices
GROUP BY date;

Zijn er nog bezwaren om het niet zo te doen, of is dit prima zo?

Die query geeft niet het resultaat dat je wil, misschien is dat een bezwaar?
Test maar eens en kijk wat er uit komt.

Ik zou ook voor 2 tabellen gaan, 1 met prijzen (datum, categorieID, prijs) en 1 met categorieën (categorieID, naam, watjijwil). En dan zoiets:

code:

select cat.naam, pr.prijs, pr.datum
from categorie Cat
join prijs pr on pr.categorieID = cat.categorieID
where cat.categorieID = watjewil
and 'iets met de datum, zie hierboven'

[The Eagle]

Blokker_1999 schreef op zaterdag 08 oktober 2016 @ 17:21:
[...]
Heeft het doen van een nieuwe query voor de laatste datum een voordeel op het simpelweg sorteren op datum en het resultaat beperken tot 1?

Zeer zeker
Heeft te maken met de query engine en de manier waarop de resultatset gegenereerd wordt.
Sort en limit betekent in principe eerst de hele tabel ophalen, dan die helemaal sorteren en vervolgens alleen de eerste regel teruggeven. En dat terwijl de hele tabel geladen wordt - tenzij je ook een gecombineerde index op de primaire sleutel en de datum zou zetten, want dan kun je die index gebruiken om het zoeken te versnellen. Maar dat wordt er niet bij gezegd. Full table scans dus, en dat wil je eigenlijk nooit.

Gebruik je mijn manier, dus de (samengestelde) primaire sleutel, dan heb je sowieso geen full table scans. Want je hebt voor de sleutel een unique index. Is een van de manieren om uniqueness af te dwingen, maar wordt uiteraard ook bij het zoeken gebruikt. Dan haal kijk je direct in de index en omdat dat in mijn geval een samengestelde sleutel is, kun je direct de gewenste rij ophalen. Scheelt een stuk, zeker bij hele grote tabellen.

Nou zou je kunnen argumenteren dat je dan een extra sorted index op datum naast de primaire index zou kunnen zetten, maar dat 1) moet je DBMS ondersteunen, 2) kost je extra schrijfacties naar de tweede index bij inserts, en extra index lezen bij selects 3) geeft een risico bij inserts omdat de index de uniciteit van de datum niet per definitie forceert, en 4) als je index unusable of corrupt raakt krijg je vertraging, of worst case gewoon functionele fouten

[sig69]

CurlyMo schreef op zaterdag 08 oktober 2016 @ 21:25:
[...]
Een tijdvak heeft alleen zin als het om gegevens gaat die parallel kunnen bestaan zoals een nationaliteit. Daar kan je het op verschillende momenten in de tijd meer van hebben. Als het echter om seriële gegevens gaat dan is een datum genoeg, zoals bij een woonplaats. De begin datum van een volgend record is dan tegelijk de eind datum van het vorige record. Anders moet je telkens de begin / eind datum synchroniseren tussen records.

Er valt wel iets voor te zeggen:
• Makkelijkere query
• Je kan een categorie ook "uit" zetten door een einddatum in te vullen.

[CurlyMo]

sig69 schreef op zondag 09 oktober 2016 @ 02:03:
[...]

Er valt wel iets voor te zeggen:
• Makkelijkere query
• Je kan een categorie ook "uit" zetten door een einddatum in te vullen.

Ik heb in mijn praktijk nog nooit meegemaakt dat een einddatum (in geval van seriële gegevens) het makkelijker maakt (wanneer we ook met historie te maken hebben).

In zou 'uitzetten' niet met een datum veld doen, maar met een derde veld 'inactief' of 'verwijderd'. Een verandering van status van dit veld is dan net zo'n mutatie als elk ander veld en dus met een eigen mutatie datum. Selecteren doe je op dat status veld gesorteerd op datum.

[sig69]

Je kan het dan wel zonder subquery uit de database halen, dat kan een verschil maken in performance.

[The Eagle]

sig69 schreef op zondag 09 oktober 2016 @ 02:03:
[...]

Er valt wel iets voor te zeggen:
• Makkelijkere query
• Je kan een categorie ook "uit" zetten door een einddatum in te vullen.

Een rij actief of inactief is een boolean. A of I. En jij wilt daar een datumveld voor gebruiken? Querywise misschien makkelijker, maar je brengt je functionele logica in gevaar. Einddatum ingevuld is categorie uit? En wat als ik nou een datum in de toekomst invul omdat dat die categorie per aankomende 1 januari inactief wordt? Of mag ik pas na 1 januari invoeren? Dan ben ik nog ff op vakantie en gaan gebruikers dus potentieel wel met die categorie aan de slag
Als funcioneel beheeder zou ik je als developer op zo'n moment gewoon willen meppen

Verder wat info over hoe zoiets met datums en statussen nou werkt: http://peoplesoft.wikidot...ive-dates-sequence-status

[farlane]

The Eagle schreef op zondag 09 oktober 2016 @ 11:03:
Een rij actief of inactief is een boolean. A of I. En jij wilt daar een datumveld voor gebruiken? Querywise misschien makkelijker, maar je brengt je functionele logica in gevaar. Einddatum ingevuld is categorie uit?

Of het de juiste oplossing is zou ik willen betwijfelen, maar ik snap niet wat er niet aan zou werken? Als je queried moet je een datum opgeven en als die datum in een "tijdvak" valt is dat de prijs die er bij hoort.

Als funcioneel beheeder zou ik je als developer op zo'n moment gewoon willen meppen

[Snippo]

Jovaro schreef op zaterdag 08 oktober 2016 @ 21:32:
[...]

Die query geeft niet het resultaat dat je wil, misschien is dat een bezwaar?
Test maar eens en kijk wat er uit komt.

Ik zou ook voor 2 tabellen gaan, 1 met prijzen (datum, categorieID, prijs) en 1 met categorieën (categorieID, naam, watjijwil). En dan zoiets:

code:

select cat.naam, pr.prijs, pr.datum
from categorie Cat
join prijs pr on pr.categorieID = cat.categorieID
where cat.categorieID = watjewil
and 'iets met de datum, zie hierboven'

Wat klopt er niet aan dan, want volgens mij is het wel wat ik wil .
Deze geeft alle prijzen van alle (gespecificeerde) categorieën op alle data terug. De query die jij voorstelt geeft maar één categorie en datum. Overigens werkt de query al wel zoals de database in elkaar steekt, alleen het probleem is dus dat ik een makkelijk overzicht wil krijgen van alle categorieën op alle data.
Het idee is dat ik het dan makkelijk aan kan passen in een compacte tabel.
Maar volgens mij wil ik hier iets dat ongebruikelijk is .

@CurlyMo
Ik gebruik MariaDB voor zover dat nog uitmaakt.

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 13:49:
[...]
Overigens werkt de query al wel zoals de database in elkaar steekt, alleen het probleem is dus dat ik een makkelijk overzicht wil krijgen van alle categorieën op alle data.

Kan je een voorbeeld uitvoer geven?

[The Eagle]

farlane schreef op zondag 09 oktober 2016 @ 11:59:
[...]

Of het de juiste oplossing is zou ik willen betwijfelen, maar ik snap niet wat er niet aan zou werken? Als je queried moet je een datum opgeven en als die datum in een "tijdvak" valt is dat de prijs die er bij hoort.


[...]

Werken doet het wel, maar het is imho gewoon een foute manier van denken.
Daarnaast: jij moet in je select iedere keer een stuk logica inbouwen om te checken of een rij actief is op niet, en een extra kolom ophalen in je resultaatset om een check te doen. Ik niet, ik check gewoon op een primaire sleutel en dat is per definitie sneller (die boolean ook onderdeel maken van de sleutel uiteraard). Bovendien is ook bij backend foutopsporing direct te zien of een rij actief of inactief is op een bepaalde datum. Wat erg handig is bij debuggen

Ander argument: de meeste dbms'en kennen een "between" operator. Alleen is die niet bij elk dbms het zelfde mbt de randwaarden ( < vs <= bijvoorbeeld). Dus dan moet je ook daar weer rekening mee zitten houden

[Snippo]

Hier een voorbeeld van het resultaat dat de query geeft:

code:

+------------+-------+-------+-------+-------+
| date       | A     | B     | C     | D     |
+------------+-------+-------+-------+-------+
| 2016-01-01 | 11.00 | 12.50 | 12.50 | 14.00 |
| 2017-01-01 | 11.50 | 13.00 | 13.00 | 14.50 |
+------------+-------+-------+-------+-------+

Wilde dit er eigenlijk bij posten maar kreeg het zo snel niet voor elkaar om de layout goed te krijgen. Had het dus gewoon kunnen kopiëren .

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 14:19:
Hier een voorbeeld van het resultaat dat de query geeft:

code:

+------------+-------+-------+-------+-------+
| date       | A     | B     | C     | D     |
+------------+-------+-------+-------+-------+
| 2016-01-01 | 11.00 | 12.50 | 12.50 | 14.00 |
| 2017-01-01 | 11.50 | 13.00 | 13.00 | 14.50 |
+------------+-------+-------+-------+-------+

Wilde dit er eigenlijk bij posten maar kreeg het zo snel niet voor elkaar om de layout goed te krijgen. Had het dus gewoon kunnen kopiëren .

En dit is ook wat je graag wilt als uitvoer van je database of is dit wat je in je applicatie wilt zien?

[ Voor 4% gewijzigd door CurlyMo op 09-10-2016 14:24 ]

[Snippo]

In principe als de uitvoer in de applicatie. Maar wanneer de uitvoer zo uit de database komt is het wel een stuk eenvoudiger om weer tegen (een array bevat dan per rij alle data, ipv dat de data uit verscihllende rijen gehaald moet worden om het te laten zien als hierboven).

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 14:58:
In principe als de uitvoer in de applicatie. Maar wanneer de uitvoer zo uit de database komt is het wel een stuk eenvoudiger om weer tegen (een array bevat dan per rij alle data, ipv dat de data uit verscihllende rijen gehaald moet worden om het te laten zien als hierboven).

Dat maakt wel een groot verschil voor je query omdat een tabulated uitvoer geen 'natuurlijke' uitvoer is van een database. De suggestie van Jovaro geeft je dit:

code:

categorie | datum | prijs
A | 2016-01-01 | 11.00
A | 2017-01-01 | 11.50
B | 2016-01-01 | 12.50
B | 2017-01-01 | 13.00

[Snippo]

Ja precies, daarom kreeg ik ook al het gevoel dat ik om iets vroeg wat ongebruikelijk is. Bij de meeste tabellen heb je per record één id en alle daarbij behorende velden in kolommen. Als je dus record 1 laadt heb je meteen alle informatie.

Op de manier waarop de prijs tabel in elkaar steekt gaat dat echter niet (tenminste niet zonder de rest van de functionaliteit te verliezen). Ik had dus gehoopt dat er een simpele functie was waarmee het toch mogelijk was, maar die lijkt er niet te zijn. Ik houd het dan maar gewoon bij de query die hier wordt aangedragen en probeer de resultaten zo weer te geven zoals ik het zou willen zien.

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 15:21:
Op de manier waarop de prijs tabel in elkaar steekt gaat dat echter niet (tenminste niet zonder de rest van de functionaliteit te verliezen).

En hoe ziet die tabel er dan uit?

Ik had dus gehoopt dat er een simpele functie was waarmee het toch mogelijk was

Wat wil je dan bereiken wat nu nog niet kan met alles wat is aangedragen?

probeer de resultaten zo weer te geven zoals ik het zou willen zien.

Dat is sowieso het doel van je front-end.

Alleen als je echt hardcore statistiek gaat bedrijven met alleen je database, dan is het handig om tabulated uitvoer te generen. In alle andere gevallen is het gewoon aan je applicatie om gegevens te herschikken voor een goede presentatie.

[Snippo]

CurlyMo schreef op zondag 09 oktober 2016 @ 15:35:
[...]

En hoe ziet die tabel er dan uit?


[...]

Wat wil je dan bereiken wat nu nog niet kan met alles wat is aangedragen?


[...]

Dat is sowieso het doel van je front-end.

Alleen als je echt hardcore statistiek gaat bedrijven met alleen je database, dan is het handig om tabulated uitvoer te generen. In alle andere gevallen is het gewoon aan je applicatie om gegevens te herschikken voor een goede presentatie.

Die ziet er dus uit zoals de uitvoer van de query die ik hierboven heb gepost. Maar ik wil waarschijnlijk iets voor elkaar krijgen waar de software niet voor bedoeld is en zal het dus in de applicatie op de juiste manier moeten weergeven.

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 15:45:
[...]


Die ziet er dus uit zoals de uitvoer van de query die ik hierboven heb gepost. Maar ik wil waarschijnlijk iets voor elkaar krijgen waar de software niet voor bedoeld is en zal het dus in de applicatie op de juiste manier moeten weergeven.

Dan zou ik het geheel eens opnieuw proberen op te zetten met de twee tabellen die ik voorstelde. Lees dan gelijk eens over foreign keys.

Je kan ook eens zoeken op google naar "Commen database mistakes". Dan kom je dingen tegen als deze link waarin jouw 'design fout' wordt toegelicht:
https://www.simple-talk.c...database-design-mistakes/

[ Voor 19% gewijzigd door CurlyMo op 09-10-2016 16:11 ]

[farlane]

The Eagle schreef op zondag 09 oktober 2016 @ 14:01:
Daarnaast: jij moet in je select iedere keer een stuk logica inbouwen om te checken of een rij actief is op niet, en een extra kolom ophalen in je resultaatset om een check te doen. Ik niet, ik check gewoon op een primaire sleutel en dat is per definitie sneller (die boolean ook onderdeel maken van de sleutel uiteraard). Bovendien is ook bij backend foutopsporing direct te zien of een rij actief of inactief is op een bepaalde datum. Wat erg handig is bij debuggen

Ander argument: de meeste dbms'en kennen een "between" operator. Alleen is die niet bij elk dbms het zelfde mbt de randwaarden ( < vs <= bijvoorbeeld). Dus dan moet je ook daar weer rekening mee zitten houden

Ik zie het verschil niet, stel ik doe zoiets ( uit mijn hoofd met beperkte SQL kennis )

SQL:

select a.name, c.price from 
    artikel a
inner join 
    category c
on
    a.cat_id = c.id
where
    c.startdate <= @date and ( c.enddate > @date or c.enddate is null )

- Ik heb niet een kolom meer nodig dan jij
- Ik heb geen last van implementatieverschillen in between
- Ik kan periodes definieren die niet aansluitend zijn (als dat nodig is)

Nadeel is natuurlijk wel dat in het geval dat je periodes alleen aansluitend kunnen zijn dat je onnodig de einddatum moet syncen zoals ergens eerder al werd aangegeven.

[The Eagle]

Je wilt die einddatum gewoon kwijt, en niet bij iedere selectie rekening mee willen houden. Met twee tabellen gaat het goed, met een stuk of 4 en wat onderlingen afhankelijkheden wordt het al een stuk lastiger
Wat mijn query zou zijn:

SQL:

select a.name, c.price from 
    artikel a, effdt_categories_view c
    Where a.category=c.category

En uiteraard is view c dan de effective date view op de core data. Maar verschil in view of onderliggende tabel ophalen merk je niet doordat de index op de onderliggende tabel gebruikt kan worden, want knderdeel primary key.
Ik hoef nergens rekening mee te houden, kan die view overal hergebruiken, weet altijd dat het goed zit. Loop nooit tegen vervelende complexe join criteria aan, ook niet bij joins van tig tabellen. En ook niet tegen performance issues.
Trust me als ik zeg dat dit principe zich bewezen heeft en nog steeds gebruikt wordt in de grote ERP systemen. Peoplesoft werkt er mee, Oracle heeft het in zijn Fusion suite zitten. Beide zowel voor HR als financials. En dat is toch allemaal redelijk wereldmarktleider (iig op HR gebied). Het is gewoon ideaal voor het benaderen en het onderhoud van je stamtabellen.

Snippo schreef op zondag 09 oktober 2016 @ 14:58:
In principe als de uitvoer in de applicatie. Maar wanneer de uitvoer zo uit de database komt is het wel een stuk eenvoudiger om weer tegen (een array bevat dan per rij alle data, ipv dat de data uit verscihllende rijen gehaald moet worden om het te laten zien als hierboven).

Eigenlijk wil je dus gewoon een pivot table (draaitabel) in mysql. Verschilt per rdbms over hoe je dat het makkelijkste doet. Leesvoer: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

[ Voor 50% gewijzigd door The Eagle op 09-10-2016 17:33 ]

[CurlyMo]

The Eagle schreef op zondag 09 oktober 2016 @ 17:02:
Eigenlijk wil je dus gewoon een pivot table (draaitabel) in mysql.

Met als uitzondering dat de tabel structuur al een draaitabel is. TS heeft dus zijn database zo ontworpen dat de tabellen overeenkomen met wat hij in de front-end wil zien.

[Snippo]

The Eagle schreef op zondag 09 oktober 2016 @ 17:02:
[...]

Eigenlijk wil je dus gewoon een pivot table (draaitabel) in mysql. Verschilt per rdbms over hoe je dat het makkelijkste doet. Leesvoer: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Dat is als het goed is ook wat de query doet die ik hier postte. Maar zoals iemand al aangaf is die niet heel flexibel en moet die handmatig aangepast worden wanneer er een categorie bij komt.

[The Eagle]

Dan moet je met prepared statements gaan werken
http://stratosprovatopoul...ble-with-dynamic-columns/

[CurlyMo]

The Eagle schreef op zondag 09 oktober 2016 @ 17:37:
Dan moet je met prepared statements gaan werken
http://stratosprovatopoul...ble-with-dynamic-columns/

Help je TS hier nu echt mee? Hij/zij heeft duidelijk geen idee hoe je handig een database schema maakt en dat je het model hoort los te koppelen van de view.

Prepared statements komen een paar jaar later wel.

[Snippo]

Het lost mijn probleem wel op, maar "just because you can doesn't mean you should". Ik ga het dus proberen op te lossen in de front-end .

[The Eagle]

Goede keuze. Pivottables in databases moet je eigenlijk niet teveel willen. Dat los je op in de reportinglaag cq de front end. Wordt het te traag dan vraag je de developer om een fatsoenlijke view

@CurlyMo: snap je opmerking, maar volgens mij baseer je die op interpretatie. Tenzij TS het zelf aangeeft en ik er overheen gelezen heb uiteraard

[CurlyMo]

The Eagle schreef op zondag 09 oktober 2016 @ 18:20:
@CurlyMo: snap je opmerking, maar volgens mij baseer je die op interpretatie. Tenzij TS het zelf aangeeft en ik er overheen gelezen heb uiteraard

@TS, misschien goed om helderheid over te geven. Hoe beginnend ben je met databases?

[Snippo]

Ik ben ooit begonnen met een simpele klantengegevens database in Microsoft Access. Na verloop van tijd kwam ik achter het bestaan van queries (best handig ) en een aantal jaar terug heb ik de boel overgezet naar MySQL.
Nu wil ik de boel dus uitbreiden en dat gaat opzich prima, maar ik mis de basis kennis van databases. Ik heb nu wel geleerd hoe bijv. een JOIN werkt maar er zijn nog genoeg dingen te leren. Ook heb ik de bestaande database al genormaliseerd want alles stond in één tabel waarbij dezelfde waardes vaak ook verschillend waren ingevoerd, een puinhoop dus .

Daarom wil ik het ook graag simpel houden, dan kan er minder fout gaan dan met allerlei complexe constructies waar ik geen verstand van heb.

[CurlyMo]

Snippo schreef op zondag 09 oktober 2016 @ 19:39:
Ook heb ik de bestaande database al genormaliseerd want alles stond in één tabel waarbij dezelfde waardes vaak ook verschillend waren ingevoerd, een puinhoop dus .

Als hij niet te groot is, kan je dat schema dan eens plaatsen?

Daarom wil ik het ook graag simpel houden, dan kan er minder fout gaan dan met allerlei complexe constructies waar ik geen verstand van heb.

Bij databases is het vrijwel altijd zo dat een ogenschijnlijke simpele oplossing nu, in de toekomst een hoop penarie gaat geven, omdat je database schema toch niet zo flexibel was als verwacht.

[Snippo]

Eerste keer dat ik zo'n schema heb gemaakt dus laat het even weten als er iets niet klopt of niet duidelijk is.