Probleem met de database structuur

[Frenkpie]

Programma:
Access database gekoppeld aan een Delphi Applicatie.

Bedoeling van het systeem:
producten kunnen invoeren en uitvoeren.
Bijhouden Welke leverancier wanneer welke producten levert en hoeveel.
Bijhouden Welke werknemer wanneer welke leveringen invoert.
Bijhouden wanneer, hoeveel van welk product, aan welke klant wordt geleverd.
Weergeven van de huidige voorraad.
Screenshot van de data structuur:

http://img82.imageshack.us/img82/5026/ibseg5.jpg


Probleem:
Weergeven van de huidige voorraad:

Ik heb de volgende query geschreven om de voorraad weer te geven:

SELECT ip.productNr, p.Omschrijving, sc.Omschrijving, OpslagPlek, (ip.Aantal - up.Aantal) AS Aantal, SerieNr
FROM Ingevoerd_Product AS ip, Uitgevoerd_Product AS up, Product AS p, Sub_Categorie AS sc
WHERE ip.ProductNr = up.ProductNr

AND ip.ProductNr = p.productNr
AND (ip.Aantal - up.Aantal) > 0
AND p.SubcategorieNr = sc.subcategorieNr;


Dit werkt dus niet helemaal zoals ik wil. Want:

- Wanneer er producten zijn geleverd (dus er zijn producten in "Ingevoerd_Product") maar nog geen producten zijn uitgeleverd ("Uitgevoerd_Product"). Laat ie dat niet zien.

- Wanneer bijv. bij een levering van maandag 10 toetsenborden binnenkomen en bij levering van woensdag weer 5 dezelfde toetsenborden binnenkomen, worden die apart weergegeven (dit komt omdat leveringNr + ProductNr de primairy key is.

Probleem 1: hoe kan ik in SQL aangeven dat ie alleen IP.Aantal - UP.Aantal moet doen wanneer er ook producten van dat productNr zijn uitgevoerd.

Probleem 2: hoe kan ik dezelfde producten met een verschillend leveringNr samenvoegen?


Moet ik alleen mijn query aanpassen? of is er iets mis met de datastructuur die ik heb ontworpen?

[ Voor 5% gewijzigd door Frenkpie op 19-07-2006 11:39 ]

[lier]

In ieder geval zal je verschillende aanleveringen moeten sommeren, dit kan je uiteraard gewoon in SQL doen (SUM).

Daarnaast zou ik het datamodel in ieder geval nog eens goed bekijken. Ik zou de Uitgevoerd_product oplossing aanpassen in een Order tabel en een Orderregels tabel. Naamgeving is uiteraard vrij.

Tot slot is het erg lastig om te beoordelen of je datamodel juist is (zo op het eerste oog ziet het er redelijk valide uit). Probeer daarvoor (voor jezelf) de database te normaliseren, ik denk in ieder geval dat je op de goede weg bent !

[ Voor 28% gewijzigd door lier op 19-07-2006 11:45 ]

[Tukk]

Probleem 1
Gaan de queries niet te zwaar worden indien je niet in een tabel bij houd hoeveel producten er nu daadwerkelijk op dit moment aanwezig zijn? Nu moet je elke keer door alles tranacties heen om de hoeveelheid te vinden.
Een tabel met daarin alle momenteel aanwezige producten lijkt mij handig om twee redenen:
- De stock opvragen zal zeer vaak gebeuren, dat is dan een snelle simpele query ipv een resourcevreter.
- Indien iemand queried wat er nog is, moet je OF een lock op de tabellen zetten, of accepteren
dat transacties tussen de query door kunnen gebeuren waardoor je mogelijk incorrecte data zal kunnen verwachten (hoe druk is de DB?)

Probleem 2
Door een look-up tabel te maken waarin je aangeeft hoe de verschillende producten aan elkaar mag knopen.

[MrBucket]

Frenkpie schreef op woensdag 19 juli 2006 @ 11:36:
Probleem 1: hoe kan ik in SQL aangeven dat ie alleen IP.Aantal - UP.Aantal moet doen wanneer er ook producten van dat productNr zijn uitgevoerd.

Kijk eens naar het UNION keyword, hiermee kun je de resultaten van 2 select statements combineren. Je zou een select kunnen schrijven voor alle producten die alleen zijn ingevoerd, en deze kunnen combineren met een select voor alle producten die zowel zijn in- als uitgevoerd.

Probleem 2: hoe kan ik dezelfde producten met een verschillend leveringNr samenvoegen?

Dit zou op te lossen moeten zijn met een GROUP BY op het productnr.

[Frenkpie]

Zou ik dan niet in het product tabel een veld bij kunnen maken: "AantalOpVoorraad" en dan een SELECT Query maken waar ik als voorwaarde bij zet:

SELECT * FROM product WHERE AantalOpVoorraad > 0


Dan zou ik delphi zo kunnen maken dat wanneer iemand een prduct van een levering invoert, het veldje "AantalOpVoorraad" ophoogt met het aantal dat ingevoerd wordt in het invoer tabel.

[lier]

@MrBucket: Ook al werkt het wel, ik denk dat je het veel te moeilijk maakt...

Probleem 1

Som aantal aangeleverde producten - Som uitgeleverde producten

Probleem 2

Maakt het uit welke wat de leveringen zijn. Volgens mij wil je alleen het aantal weten en kan je dus met een SUM(aantal) al klaar zijn.

@Frenkpie: We hebben het hier (voor zover ik kan overzien) niet over een Enterprise omgeving... Gewoon de gegevens "dynamisch"ophalen en tijdens de verwerking van een order controleren of de voorraad toereikend is.

@TS: Voor berekeningen is het verstandig om gebruik te maken van de ISNULL() methode in je query.

[ Voor 36% gewijzigd door lier op 19-07-2006 12:00 ]

[Frenkpie]

MrBucket schreef op woensdag 19 juli 2006 @ 11:53:
[...]

Kijk eens naar het UNION keyword, hiermee kun je de resultaten van 2 select statements combineren. Je zou een select kunnen schrijven voor alle producten die alleen zijn ingevoerd, en deze kunnen combineren met een select voor alle producten die zowel zijn in- als uitgevoerd.

Oke met de UNION functie is dus het eerste probleem opgelost. Code:

SELECT ip.productNr, p.Omschrijving, sc.Omschrijving, OpslagPlek, (ip.Aantal-up.Aantal) AS Aantal, SerieNr
FROM Ingevoerd_Product AS ip, Uitgevoerd_Product AS up, Product AS p, Sub_Categorie AS sc
WHERE ip.ProductNr=up.ProductNr And ip.ProductNr=p.productNr And (ip.Aantal-up.Aantal)>0 And p.SubcategorieNr=sc.subcategorieNr
UNION
SELECT ip.productNr, p.Omschrijving, sc.Omschrijving, OpslagPlek, Aantal, SerieNr
FROM Ingevoerd_Product AS ip, Product AS p, Sub_Categorie AS sc
WHERE ip.ProductNr=p.productNr And p.SubcategorieNr=sc.subcategorieNr

Dit zou op te lossen moeten zijn met een GROUP BY op het productnr.

Hoe kan ik in deze SQL code een group by toepassen? Want als ik GROUP BY ip.ProductNr, slikt access dat (natuurlijk) niet.

[MrBucket]

lier schreef op woensdag 19 juli 2006 @ 11:56:
@MrBucket: Ook al werkt het wel, ik denk dat je het veel te moeilijk maakt...

Tja, wat is te moeilijk?

Het lijkt erop dat dit een schoolopdracht is (alhoewel, werken die nog met Delphi?), en daar is men over het algemeen nogal gecharmeerd van 'theoretisch correcte' oplossingen, d.w.z.: liever een goed genormaliseerd datamodel dan een wat meer gedenormaliseerd datamodel wat goed performt. Met die insteek lijkt me het datamodel van TS goed te voldoen.

Bovendien maakt men gebruik van Access. Voor zover ik weet biedt die geen ondersteuning voor stored procedures, triggers en transacties(?). Het wordt dan erg lastig om bijv. een extra tabel bij te houden met de huidige voorraad van elk product, omdat je dan na elke wijziging in ingevoerd_product of uitgevoerd_product ook de tabel met huidige voorraad bij moet werken. Dit zijn 2 operaties, waarvan niet gegarandeerd kan worden dat ze atomair worden uitgevoerd.

Wat evt. nog wel kan is een view maken die de huidige voorraad van elk product toont. Dan heb je a.h.w. toch je huidige_voorraad-tabel waar je je queries op uit kan voeren, maar deze hoef je dan niet apart te updaten.

[lier]

MrBucket schreef op woensdag 19 juli 2006 @ 12:16:
Tja, wat is te moeilijk?

Ik zie niet in wat die UNION moet doen...naar mijn idee kan je deze er helemaal uit laten met behulp van een ISNULL (wordt toch wel in Access ondersteund ?).

Voor de rest (goed verhaal) ben ik het helemaal met je eens !

[ Voor 12% gewijzigd door lier op 19-07-2006 12:22 ]

[MrBucket]

Frenkpie schreef op woensdag 19 juli 2006 @ 12:05:
SELECT ip.productNr, p.Omschrijving, sc.Omschrijving, OpslagPlek, Aantal, SerieNr
(...)
Hoe kan ik in deze SQL code een group by toepassen? Want als ik GROUP BY ip.ProductNr, slikt access dat (natuurlijk) niet.

Het idee is dat je Access vertelt: "oke, alle rijen die in deze kolom(men) dezelfde waarden hebben, moet je samenvoegen". In jouw geval zou je dat kunnen doen met productNr, p.Omschrijving, en sc.Omschrijving omdat deze velden aan een product hangen: hetzelfde product betekent dezelfde waarden van deze kolommen.

Dan moet je Access vertellen op welke manier hij de andere kolommen moet behandelen als een aantal rijen worden samengevoegd. Voor aantal wil je natuurlijk SUM gebruiken: hij moet alle regels van hetzelfde product samen nemen, en de aantallen bij elkaar optellen.

Dan blijven nog over opslagplek en serienummer. Omdat de primary key van de ingevoerd_product tabel niet alleen bestaat uit een productnummer, maar ook een leveringsnummer zullen de waarden voor deze kolommen per rij verschillen. En er is geen zinnige manier om deze gegevens te combineren.

Ik zou opslagplek en serienummer laten vervallen uit je query.

[Frenkpie]

Ik heb het toch wat simpeler opgelost: ik heb een veld "aantalopVoorraad" bijgemaakt in het product tabel.

Wanneer er dan een product wordt ingevoerd (in Delphi), wordt het aantal van dat bepaald productNr ook opgeteld in het product tabel. Zelfde geld voor het uitvoeren alleen dan andersom.


Misschien is het niet de beste oplossing maar voor dit systeem is het voldoende. Ik weet wel dat ik nog veel te leren heb ik zal vanavond dit topic even doorspitten en kijken of ik misschien op een beter idee kan komen.

Bedankt voor de snelle replies!

[ Voor 3% gewijzigd door Frenkpie op 19-07-2006 12:33 ]

[MrBucket]

Frenkpie schreef op woensdag 19 juli 2006 @ 12:32:
Ik heb het toch wat simpeler opgelost: ik heb een veld "aantalopVoorraad" bijgemaakt in het product tabel.

Wanneer er dan een product wordt ingevoerd (in Delphi), wordt het aantal van dat bepaald productNr ook opgeteld in het product tabel. Zelfde geld voor het uitvoeren alleen dan andersom.

Let op:

Bovendien maakt men gebruik van Access. Voor zover ik weet biedt die geen ondersteuning voor stored procedures, triggers en transacties(?). Het wordt dan erg lastig om bijv. een extra tabel bij te houden met de huidige voorraad van elk product, omdat je dan na elke wijziging in ingevoerd_product of uitgevoerd_product ook de tabel met huidige voorraad bij moet werken. Dit zijn 2 operaties, waarvan niet gegarandeerd kan worden dat ze atomair worden uitgevoerd.

En mocht dit een schoolopdracht zijn, dan zou ik oppassen met het opslaan van afgeleide gegevens (zoals de huidige aantallen op voorraad), omdat dit gezien kan worden als redundantie (lees: niet goed genormaliseerd).

[Frenkpie]

MrBucket schreef op woensdag 19 juli 2006 @ 12:16:
[...]

Tja, wat is te moeilijk?

Het lijkt erop dat dit een schoolopdracht is (alhoewel, werken die nog met Delphi?), en daar is men over het algemeen nogal gecharmeerd van 'theoretisch correcte' oplossingen, d.w.z.: liever een goed genormaliseerd datamodel dan een wat meer gedenormaliseerd datamodel wat goed performt. Met die insteek lijkt me het datamodel van TS goed te voldoen.

het heeft idd wat weg van een school opdracht. Maar dat is het niet.

Ik heb MBO ICT gedaan en afgelopen jaar mijn P HBO bedrijfskundige Informatica gehaald. We zijn afgelopen jaar dus veel bezig geweest met modelleren van databases/access/ en Delphi.

Nu werk ik bij een klein IT bedrijfje (vakantiewerk) en ik ben (als enige hier) bezig met het ontwerpen van programma's in access en nu wilde ik proberen om een voorraad systeem te maken in Access i.c.m. Delphi.

Ik ben dus nog niet echt ervaren maar ik leer er wel veel van

/off-topic achtergrondinformatie

[ Voor 6% gewijzigd door Frenkpie op 19-07-2006 12:46 ]

[Frenkpie]

MrBucket schreef op woensdag 19 juli 2006 @ 12:37:
[...]

Let op:

[...]

En mocht dit een schoolopdracht zijn, dan zou ik oppassen met het opslaan van afgeleide gegevens (zoals de huidige aantallen op voorraad), omdat dit gezien kan worden als redundantie (lees: niet goed genormaliseerd).

Dat heb ik opgevangen in Delphi: de voorraad in het product tabel wordt automatisch bijgewerkt zodra er een product wordt in of uitgevoerd.

[MrBucket]

Frenkpie schreef op woensdag 19 juli 2006 @ 12:45:
[...]


Dat heb ik opgevangen in Delphi: de voorraad in het product tabel wordt automatisch bijgewerkt zodra er een product wordt in of uitgevoerd.

Ik zou je toch willen aanraden om dit soort constructies niet te gebruiken, tenzij je de beschikking hebt over stored procedures of triggers (voor zover ik weet niet beschikbaar in MS Access) die dit op een nette manier voor je kunnen afhandelen.

Het probleem is dat je ten alle tijde erop moet kunnen vertrouwen dat de huidige_voorraad overeenkomt met de rijen in Ingevoerd_Product en Uitgevoerd_Product. Op het moment dat er tijdens het updaten van de product-tabel, Ingevoerd_Product of Uitgevoerd_Product iets mis gaat, of wanneer iemand een keer buiten de Delphi-applicatie om iets wijzigt in de database (die niet bekend is met deze afhankelijkheid) dan heb je de poppen aan het dansen.

Als je je queries wil vergemakkelijken, dan zou je bijv. een view kunnen maken met de huidige voorraad per product. In je queries kun je een view net zo gebruiken als ware het een tabel, maar omdat het onder water niets anders is dan een query op de Ingevoerd_Product en Uitgevoerd_Product tabellen, bevat het altijd de correcte waarden zonder dat je een extra update hoeft te doen.