[Alg] Database Ontwerp Probleem

[Verwijderd]

Een bedrijf heeft 3 vestigingen:

- Amsterdam
- Den Haag
- Rotterdam

Al deze vestigingen hebben meerdere 'virtuele' voorraden:

- Hoofdmagazijn: De hoofdvoorraad om te verkopen
- Returned: Teruggebrachte goederen wegens gebreken
- Showroom: Producten die zich in de showroom bevinden
- Transit: Producten die verstuurd en onderweg zijn naar andere vestigingen

Het zou mooi zijn om dynamisch vestigingen en magazijnen (per vestiging) te kunnen toevoegen en verwijderen. Maar de performance moet ook nog een beetje ok zijn. Tot nu toe heb ik twee oplossingen gevonden waar ik beide niet echt tevreden mee ben.

De Eerste:

code:

Product
    name
    price
    stock->Stock

Location
    name
    stocktypes->StockType

StockType
    name
    location->Location

Stock
    value
    location->Location
    stocktype->StockType
    product->Product

Zo krijg ik een enorme Stock tabel die als het goed is een unieke combinatie bevat van locatie,stocktype,product en waar een amount uit komt rollen. Dit zijn enorm veel tabellen en leveren een hele hoop queries op bij bijvoorbeeld een zoekopdracht naar een product met 100 resultaten. Ook kan er een bug ontstaan waardoor dezelfde combinatie twee keer in de tabel voorkomt.

De Tweede:

code:

Product
    name
    price
    stock->StockAmsterdam (of StockRotterdam etc)

StockAmsterdam
    main
    returned
    showroom
    transit
    product->Product

Op deze manier is de performance een stuk beter want ik kan RIGHT JOIN gebruiken, maar voor elke locatie zal ik een tabel moeten toevoegen en voor elke voorraad een kolom in de tabel. Niet echt netjes dus.

De Derde:

code:

Product
    name
    price
    productstock->ProductStock

ProductStock
    name
    price
    order->Order
    stock->Stock

Stock
    name
    productstock->ProductStock

Deze optie maak voor ELK product dat de organisatie binnen komt een row aan en houdt bij in welke voorraad of order het zich bevindt. Zo is het makkelijk selecties maken, en producten op serienummer tracen binnen een bedrijf. Maar het heeft als nadeel dat als er heel veel kleine producten worden verkocht zonder serienummers de tabel enorm groeit met eigenlijk identieke rows.

[whoami]

De 2de oplossing is absoluut niet goed. Wat ga je doen als het bedrijf nog een vestiging in een andere (of in dezelfde) stad creeërt ?

Zelf vind ik de eerste oplossing de beste (qua idee). Waarom is iedereeen altijd zo bang om veel rijen in z'n DB te hebben ?
Er bestaan dingen zoals indexen hoor....

Echter, dat is imo geen datamodel voor een relationele DB.

[ Voor 11% gewijzigd door whoami op 20-10-2005 17:18 ]

[Wacky]

Zo krijg ik een enorme Stock tabel die als het goed is een unieke combinatie bevat van locatie,stocktype,product en waar een amount uit komt rollen. Dit zijn enorm veel tabellen en leveren een hele hoop queries op bij bijvoorbeeld een zoekopdracht naar een product met 100 resultaten. Ook kan er een bug ontstaan waardoor dezelfde combinatie twee keer in de tabel voorkomt.

Die unieke combinatie creeer je dus door een PK te maken van locatie,stocktype,product en eventueel aantal. Zo voorkom je je dubbele combinaties.

Ok ik zou kiezen voor de 1e optie, soms kun je beter kiezen voor performance dan voor "mooi" ...

[Verwijderd]

Zoals ik het nu denk zal de eerste toch ook de beste zijn met in een extra veld waarin ik een array maak van gecachde waarden van verkoopbare-producten en producten-totaal-in-voorraad van het hoofdmagazijn. Die getallen update ik dan alleen wanneer er een product afgereken of besteld wordt. Zo kan ik de queries ook een beetje beperken. Ik ben niet zo'n fan van gecachede waarden in een database en array's in een veld, maar als ik een search doe op een product en ik krijg er honderd terug, dan is het echt gekkenwerk om voor elke rij de database live uit te laten rekenen hoeveel producten er zijn minus de in totaal aantal bestelde producten.

Koen

[curry684]

Wacky schreef op donderdag 20 oktober 2005 @ 17:24:
[...]

Die unieke combinatie creeer je dus door een PK te maken van locatie,stocktype,product en eventueel aantal. Zo voorkom je je dubbele combinaties.

Primary key hoeft niet eens, voor dit soort situaties heeft een beetje fatsoenlijke database gewoon een unique constraint aan boord. Een PK is niets anders dan een speciale unique constraint. In dit geval is een clustered PK overigens best goed te verdedigen (meestal zijn ze lelijk, overbodig en traag) maar dan moet je er aantal natuurlijk niet in tiefen, dat is geen uniek identificerend element van de entiteit en bovendien rampzalig traag als je hem wijzigt. De andere velden zijn in principe constant na eenmalige insert.

[edit]

D'r klopt trouwens geen hout van model 1. Wat de n**k doet die 1..1 relatie tussen produkt en stock in godesnaam? Mag je hetzelfde produkt niet op meer plaatsen voeren of zo? Of in dit geval zelfs niet in magazijn en showroom tegelijk?

En die enorme tabel is geen probleem overigens, daar is het een database voor. Ga je pas zorgen maken bij meer dan 10 miljoen records in 1 tabel, maar voor die tijd niet.

[edit2]
En waarom koppel je StockType aan een location? Ik mag toch aannemen dat iedere vestiging dezelfde types voorraad heeft binnen 1 en hetzelfde bedrijf?

[ Voor 30% gewijzigd door curry684 op 20-10-2005 17:52 ]

[Verwijderd]

gewoon ff normaliseren .. kom je vanzelf op het meest efficiente ontwerp uit.

[Wacky]

Verwijderd schreef op donderdag 20 oktober 2005 @ 17:55:
gewoon ff normaliseren .. kom je vanzelf op het meest efficiente ontwerp uit.

Grapjas Iedere situatie is op meerdere manieren op te lossen, en de genormaliseerde oplossing is niet perse de juiste. Daar gaat dit topic nou juist over ....

[DDemolition]

Noem je de tabellen zoals in de startpost?
Ik vind het altijd het fijnste programmeren als je de volgende structuur gebruikt:

tblProduct
PRO_Name
PRO_Price
PRO_Stock->Stock
PRO_CustomerID (als er bv. een tblCustomers aanhangt)

Dit scheelt hele lappen SQL statement (PRO_Name is altijd uniek) en maakt het naar mijn idee overzichterlijker.

[ Voor 5% gewijzigd door DDemolition op 20-10-2005 18:09 ]

[Boss]

Alleen krijg je zo weer lange veldnamen en gaat de 'leesbaarheid' van je veldnamen omlaag. Ik heb er persoonlijk geen enkel probleem mee om in query ook de tabelnaam in te typen. En qua normaliseren is het natuurlijk overbodig om je veldnamen allemaal nog van een tabelnaam te voorzien

[Verwijderd]

Ik ga nu dit testen. Ik voeg dus nog aan Product een key stockcache toe met een array waarin voor elke vestiging het verkoopbare aantal (totaal aantal - aantal in order) en het totaal aantal in vooraad is. Die update ik op database niveau elke keer wanneer iemand een order maakt, verwijderd of aanpast.

code:

class Location(SQLObject):
    name = UnicodeCol()
    stocks = MultipleJoin('Stock')

class Product(SQLObject):
    code = UnicodeCol()
    name = UnicodeCol()
    price = CurrencyCol()
    stocks = MultipleJoin('Stock')

class Stock(SQLObject):
    main = IntCol()
    showroom = IntCol()
    transit = IntCol()
    returned = IntCol()
    product = ForeignKey('Product')
    location = ForeignKey('Location')

[curry684]

Lees anders even mijn post nog een keer voor je iets doet, er klopt nu echt geen bal van.

[ecageman]

Ik moet curry684 gelijk geven. Het ziet er zo niet goed uit. Bedenk eerst eens welke objecten er zijn en wat voor relaties ze met elkaar kunnen hebben. Bijvoorbeeld een 1 op N relatie, of een N op N relatie. De N op N relaties zul je dan op kunnen lossen met relatie tabellen, zodat je de N op N relatie omzet naar een (1 op N) + (N op 1) relatie.

Location
id
naam
...
...

Stock
id
naam
type
...
...

Stock_Locatie
loc_id
stock_id

(... staan voor de rest van de gegevens die bij een object horen)

Natuurlijk kan het ook op andere manieren opgelost worden, maar op deze manier kom je denk ik wel een stuk verder.

[whoami]

curry684 schreef op donderdag 20 oktober 2005 @ 17:45:
[...]

Primary key hoeft niet eens, voor dit soort situaties heeft een beetje fatsoenlijke database gewoon een unique constraint aan boord. Een PK is niets anders dan een speciale unique constraint. In dit geval is een clustered PK overigens best goed te verdedigen (meestal zijn ze lelijk, overbodig en traag) maar dan moet je er aantal natuurlijk niet in tiefen, dat is geen uniek identificerend element van de entiteit en bovendien rampzalig traag als je hem wijzigt. De andere velden zijn in principe constant na eenmalige insert.

Waarom is een clustered PK in dat geval goed te verdedigen ?
Een clustered PK is een goede optie als je column een incrementing set is. Waarom zou je een clustered index op de combo van die velden zetten ?
Begrijp ik je nu verkeerd, of bedoel je in dit geval met clustered PK een PK over meerdere velden (een composite key dus), das nl. wat anders dan een 'clustered' PK (maar dat weet je wel).

D'r klopt trouwens geen hout van model 1.

Wat ik ook al zei.
't Is alleszins de beste
manier om het datamodel te maken, maar hetgeen getoond wordt, is gewoon geen (goed) datamodel.

DDemolition schreef op donderdag 20 oktober 2005 @ 18:07:
Noem je de tabellen zoals in de startpost?
Ik vind het altijd het fijnste programmeren als je de volgende structuur gebruikt:

tblProduct
PRO_Name
PRO_Price
PRO_Stock->Stock
PRO_CustomerID (als er bv. een tblCustomers aanhangt)

Dit scheelt hele lappen SQL statement (PRO_Name is altijd uniek) en maakt het naar mijn idee overzichterlijker.

Dat vind ik zelf onzinnig, en het maakt het boeltje er ook moeilijk leesbaarder op.
Als je hele lappen sql statement wilt vermijden, dan kan je nog altijd gebruik maken van aliassen.
(maar goed, daar gaat dit topic niet over )

Verwijderd schreef op donderdag 20 oktober 2005 @ 18:35:
Ik ga nu dit testen. Ik voeg dus nog aan Product een key stockcache toe met een array waarin voor elke vestiging het verkoopbare aantal (totaal aantal - aantal in order) en het totaal aantal in vooraad is. Die update ik op database niveau elke keer wanneer iemand een order maakt, verwijderd of aanpast.

code:

class Location(SQLObject):
    name = UnicodeCol()
    stocks = MultipleJoin('Stock')

class Product(SQLObject):
    code = UnicodeCol()
    name = UnicodeCol()
    price = CurrencyCol()
    stocks = MultipleJoin('Stock')

class Stock(SQLObject):
    main = IntCol()
    showroom = IntCol()
    transit = IntCol()
    returned = IntCol()
    product = ForeignKey('Product')
    location = ForeignKey('Location')

Ik zou toch eerst eens even m'n datamodel gaan maken, ipv nu al deze classes te maken.

[ Voor 41% gewijzigd door whoami op 20-10-2005 20:30 ]

[curry684]

whoami schreef op donderdag 20 oktober 2005 @ 20:27:
[...]

Waarom is een clustered PK in dat geval goed te verdedigen ?
Een clustered PK is een goede optie als je column een incrementing set is. Waarom zou je een clustered index op de combo van die velden zetten ?
Begrijp ik je nu verkeerd, of bedoel je in dit geval met clustered PK een PK over meerdere velden (een composite key dus), das nl. wat anders dan een 'clustered' PK (maar dat weet je wel).

Uhm composite ja We zijn het dus eens ja

Verder zoekt TS volgens mij gewoon dit:

code:

Department
  DepartmentId int primary key
  DepartmentName varchar(x)

StockType
  StockTypeId int primary key
  StockTypeName varchar(x)

Product
  ProductId int primary key
  ProductName varchar(x)
  ProductPrice money

Stock
  StockId int primary key
  ProductId foreign key references Product
  DepartmentId foreign key references Department
  StockTypeId foreign key references StockType
  Amount int

Ik heb zelf een schurfthekel aan composite PK's dus ik frot er een surrogate in, maar dat kun je naar believen aanpassen. Let er wel op dat je nu geen historische prijsdata hebt, en het kan best wel eens handig zijn om prijs te dupliceren in Stock zodat je niet meteen enorme voorraden devalueert als je een prijs verandert (dat zal je CFO niet zo leuk vinden), en dan heb je meteen een surrogate nodig omdat je dan in hetzelfde Department meerdere Producten op dezelfde StockType kunt hebben van een verschillende prijs. Maar dat mag je allemaal zelf overwegen

[ Voor 48% gewijzigd door curry684 op 21-10-2005 00:39 ]

[MMUilwijk]

Typisch gevalletje waarin een sterschema handig kan zijn. Voorstel van Curry684 is daar een leuk voorbeeld van, waarbij ik dan inderdaad wel een datum zou bijhouden (historische overzichten) en een prijs in de Stock tabel (de feitentabel).

[Verwijderd]

curry684 schreef op vrijdag 21 oktober 2005 @ 00:30:
Let er wel op dat je nu geen historische prijsdata hebt, en het kan best wel eens handig zijn om prijs te dupliceren in Stock zodat je niet meteen enorme voorraden devalueert als je een prijs verandert (dat zal je CFO niet zo leuk vinden), en dan heb je meteen een surrogate nodig omdat je dan in hetzelfde Department meerdere Producten op dezelfde StockType kunt hebben van een verschillende prijs. Maar dat mag je allemaal zelf overwegen

héhé, sinds wanneer mag een developer dat zelf overwegen? Dat lijkt me deel uit te maken van specificaties die de klant oplegt. Indien die er (nog) niet zijn begin je beter niet met ontwikkelen want het zal toch nooit goed zijn (en je wil niet echt achteraf zwaar in je DB model gaan klooien).

[curry684]

Het ruikt, smaakt en oogt als huiswerk, dus dan mag de developer het zelf overwegen Anders waren er idd wel solide functional specs geweest en had de architect de DB al ontworpen

[ Voor 37% gewijzigd door curry684 op 21-10-2005 10:05 ]

[ikke007]

Computer analyst tegen de programmeur: "Begin maar alvast met code kloppen dan ga ik wel uitzoeken wat de klant wil"

Als je het datamodel dus goed wilt modeleren zal je eerst alle eisen goed op papier moeten zetten en daar vanuit pas gaan redeneren. De JBF methode is een beetje achterhaald in de ICT wereld (Jan Boeren Fluitjes)

[ Voor 49% gewijzigd door ikke007 op 21-10-2005 10:24 ]

[pjonk]

curry684 schreef op donderdag 20 oktober 2005 @ 17:45:
En die enorme tabel is geen probleem overigens, daar is het een database voor. Ga je pas zorgen maken bij meer dan 10 miljoen records in 1 tabel, maar voor die tijd niet.

Toch even een kanttekening maken. Deze benadering geldt voor een professioneel DBMS. Access gaat al over z'n nek als je 150.000 records in 1 tabel hebt.

[curry684]

We hebben het hier over databases, niet over Access

[KopjeThee]

Lijkt idd typisch huiswerk, maar zoiets (losse notatie) lijkt mij (ook):

Vestiging:
1) Naam
2) Plaats
3) ID
key: 3), of als dat kan 3) weglaten en 1) + 2)

Product:
1) Naam
2) Prijs
3) ID
key: 3), of 3) weglaten en 1) als key (1 prijs/prod lijkt mij)

Stock:
1) VestigingID
2) ProductID
3) Aantal
4) {Hoofdmag, Return, Show, Trans}
key: 1) + 2) + 4)

1) en 2) natuurlijk foreign keys

Edit: Oops.

[ Voor 8% gewijzigd door KopjeThee op 21-10-2005 14:24 ]

[curry684]

Ik mag hopen dat je op geen enkele opleiding een voldoende krijgt als je een PK legt op veranderlijke velden als Aantal en Prijs

[DDemolition]

curry684 schreef op vrijdag 21 oktober 2005 @ 11:08:
We hebben het hier over databases, niet over Access

Misschien dat het me ontgaan is, maar wat voor dbserver wordt het?
DB400, SQL of misschien iets anders?

[Verwijderd]

Ha, het is zeker geen huiswerk. Ik ben een POS systeem aan het maken met cocoa+postgres.

Op zich is het peanuts. Tot aan het voorraad beheer. Er zijn meerdere opties, dus ik test ze een beetje uit. Zoals ik ze nu heb werkt het wel goed en is de performance ok. Ik heb alleen een volgend probleem. Ik heb deze query

code:

SELECT location.name,stock.main-SUM(order_product.qty) AS available,stock.main,stock.returned,stock.showroom,stock.transit
FROM stock
RIGHT JOIN location ON location.id=stock.location_id 
RIGHT JOIN order_product ON stock.product_id=order_product.product_id
WHERE stock.product_id=6030
GROUP BY location.name,stock.main,stock.returned,stock.showroom,stock.transit

Ik moet een tabel overhouden met voor elke vestiging de verschillende voorraaden. Dat is geen probleem. Ik wil een extra row toevoegen die aangeeft hoeveel producten er nog beschikbaar zijn (niet in orders voorkomen). Zoals hierboven krijg ik dat behalve dan dat hij het aantal producten in voorraad van ALLE 'main' voorraaden aftrekt. Ik wil dat dus alleen bij diegene waarvoor order_product.location_id=location.id. Maar ik wil wel alledrie de rows terug, ook als er geen producten in order staan voor een bepaalde locatie.

Was het maar huiswerk :-)

[kmf]

Zoiets zal ik doen.

code:

[Product]
productid
naam
price

[stock]
productid->pk
locationid->pk
baseprice
total

[location]
locationid
extracostsonbaseprice


[invoice]
invoiceid->pl
customerid->fk
productid->fk
locationid->fk
status
calculated_by_location_subtotalprice
subtotal_plus_paycheckforzalm_total_price


[customer]
customerid

Met stock heb je een unieke combo product+location. Total zie je wat je hebt.

invoice is als het product dat de deur uit is. Status weet je wel, verzonden, ontvangen, etc. Als het buiten de deur is moet stock met 1 omlaag gehaald worden etc.
Invoice kan je nog splitten naar element en invoice, als je niet wilt dat het 1 invoice per product is. Element is dan 1 verkocht product, invoice combo van elementen.

Zo kan je wel aan alle info komen die je nodig zal hebben.

Verwijderd schreef op donderdag 20 oktober 2005 @ 17:55:
gewoon ff normaliseren .. kom je vanzelf op het meest efficiente ontwerp uit.

Dat is wat ze je de eerste drie jaar instampen op de HBO. In je vierde jaar gaan ze zeggen dat redundantie en horizontaal of verticaal splitsen van data soms nodig is voor performance.

[curry684]

Verwijderd schreef op vrijdag 21 oktober 2005 @ 20:15:
Ha, het is zeker geen huiswerk. Ik ben een POS systeem aan het maken met cocoa+postgres.

Waar zijn je functional specs dan? Ik mag toch hopen dat je een eisenpakket hebt liggen over hoe je de applicatie dient te ontwerpen zodat deze aan de eisen van de bedrijfsstructuur en -cultuur voldoet?