Hallo! Mijn naam is Andrey Semenov, ik ben senior analist bij Sportmaster. In deze post wil ik de kwestie van denormalisatie van databases in ERP-systemen aanroeren. We zullen de algemene voorwaarden bekijken, evenals een specifiek voorbeeld — laten we zeggen, een prachtige taverne-monopolist voor piraten en zeelieden. In deze taverne moeten piraten en zeelieden verschillend worden bediend, omdat hun opvattingen over het mooie en consumentengedrag aanzienlijk verschillen.
Hoe zorg je ervoor dat iedereen tevreden is? Hoe blijf je bij het ontwerpen en onderhouden van zo'n systeem niet gek? Wat te doen als er in de taverne niet alleen de gebruikelijke piraten en zeelieden komen?
Alles onder de kat. Maar laten we ordelijk te werk gaan.
1. Beperkingen en aannames
Alles wat hier is gepresenteerd, betreft alleen relationele databases. Goed gedocumenteerde, ook op het internet, gevolgen van denormalisatie in de vorm van wijzigings-, verwijder- en invoegingsanomalieën worden niet behandeld. Gevallen waarin denormalisatie gemeengoed is, zoals serie- en paspoortnummer, datum en tijd, vallen buiten de publicatie.
In de post worden intuïtief begrijpelijke en praktisch toepasbare definities van normale vormen gebruikt, zonder verwijzingen naar wiskundige termen. In de vorm die kan worden toegepast op het onderzoeken van echte bedrijfsprocessen (BP) en het ontwerpen van industriële software.
Er is een mening dat het ontwerpen van datalakes, hulpmiddelen voor rapportage en integratieovereenkomsten (waarin gebruik wordt gemaakt van een tabelvorm van informatie) verschilt van het ontwerpen van ERP-databases, in die zin dat gebruiksgemak en de toepassing van bewuste denormalisatie prioriteit kunnen hebben boven het waarborgen van de integriteit van gegevens. Ik deel deze mening, en het onderstaand beschrevene geldt uitsluitend voor modellen van masterdata en transactiegegevens in ERP-systemen.
De uitleg van normale vormen is gegeven aan de hand van een voorbeeld dat begrijpelijk is voor de meeste lezers op een alledaags niveau. Echter, als een duidelijke illustratie zijn in punten 4-5 opzettelijk 'verzonnen' problemen gebruikt. Als dit niet gebeurt en er wordt een klassiek voorbeeld genomen, bijvoorbeeld hetzelfde model voor het opslaan van een bestelling uit punt 2, kan het zijn dat de aandacht van de lezer verschuift van het aangeboden proces naar persoonlijke ervaring en hoe dat processen en datamodellen in informatiesystemen zouden moeten worden opgebouwd. Met andere woorden, neem twee gekwalificeerde IT-analisten; laat de ene service verlenen aan logistici die passagiers vervoeren, en de andere aan logistici die machines voor de productie van microchips vervoeren. Vraag hen, zonder vooraf te overleggen over te automatiseren bedrijfsprocessen, een datamodel op te stellen voor het opslaan van informatie over een treinreis.
Er is een niet-nul kans dat je in de voorgestelde modellen zowel een duidelijk andere set attributen als niet-overeenkomende sets van entiteiten zult vinden, omdat elke analist zich zal baseren op de processen en taken die zij gewend zijn. En in zo'n situatie is het onmogelijk om te zeggen welke model 'juist' is, omdat er geen beoordelingscriteria zijn.
2. Normale vormen
De eerste normale vorm van een database vereist dat alle attributen atomair zijn.
Met name als object A niet-sleutelattributen a en b heeft, zodanig dat c=f(a,b) en je de waarde van attribuut c opslaat in de tabel die object A beschrijft, dan is de eerste normale vorm in de database geschonden. Bijvoorbeeld, als in de specificatie van een bestelling de hoeveelheid wordt opgegeven, waarvan de eenheden afhankelijk zijn van het type product: in het ene geval kunnen dit stuks zijn, in het andere liters, en in een derde verpakking, bestaande uit stuks (in het bovenstaande model Good_count_WR), dan is de atomiciteit van attributen in de database geschonden. In dit geval, om te zeggen hoe de set tabellen voor de specificatie van een bestelling moet zijn, heb je een doelbeschrijving van het werkproces in het informatiesysteem nodig, en omdat de processen verschillende kunnen zijn, kunnen er ook veel 'juiste' versies zijn.
De tweede normale vorm van een database vereist het naleven van de eerste normaalvorm en een eigen tabel voor elke entiteit die betrekking heeft op het werkproces in IS. Als in één tabel afhankelijkheden bestaan met=f1(a) en d=f2(b) en er geen afhankelijkheid bestaat met=f3(b), dan is de tweede normaalvorm in de tabel geschonden. In het voorbeeld hierboven bestaat er in de tabel 'Bestelling' geen afhankelijkheid tussen de bestelling en het adres. Wijzig de naam van de straat of de stad, en je zult geen enkele invloed op de essentiële attributen van de bestelling ondervinden.
Derde normaalvorm van de database vereist het naleven van de tweede normaalvorm en de afwezigheid van functionele afhankelijkheden tussen de attributen van verschillende entiteiten. Deze regel kan als volgt worden geformuleerd: "alles wat berekend kan worden, moet berekend worden". Met andere woorden, als er twee objecten A en B zijn. In de tabel die de attributen van object A opslaat, wordt attribuut C aangetoond, terwijl object B een attribuut b heeft, zodat er c=f4(b) bestaat, dan is de derde normaalvorm geschonden. In het onderstaande voorbeeld claimt het attribuut 'Aantal stuks' (Total_count_WR) in de bestelrecord duidelijk een schending van de derde normaalvorm.
3. Mijn benadering van het toepassen van normalisatie
1. Alleen het doelgerichte automatiseerbare bedrijfsproces kan de analist criteria bieden voor het identificeren van entiteiten en attributen bij het creëren van een datamodel. Het creëren van een procesmodel is een vereiste voor het maken van een normale datamodel.
2. Het bereiken van de derde normaalvorm in strikte zin kan onpraktisch zijn in de echte praktijk van het ontwikkelen van ERP-systemen bij het voldoen aan een deel of alle van de volgende voorwaarden:
- automatiseerbare processen zijn zelden onderhevig aan veranderingen,
- de tijdslijnen voor onderzoek en ontwikkeling zijn krap,
- de vereisten voor dataintegriteit zijn relatief laag (potentiële fouten in industriële software leiden niet tot verlies van geld of klanten voor de softwareontwikkelaar)
- enz.
Onder de beschreven omstandigheden kunnen de kosten voor het identificeren en beschrijven van de levenscyclus van sommige objecten en hun attributen economisch niet gerechtvaardigd zijn.
3. Elke consequentie van denormalisatie van het datamodel in een reeds bestaande IS kan worden gecompenseerd door grondig voorafgaand onderzoek van de code en testen.
4. Denormalisatie is een manier om de arbeidsinspanningen van de fase van het onderzoeken van gegevensbronnen en het ontwerpen van bedrijfsprocessen naar de ontwikkelingsfase te verplaatsen, van de implementatiefase naar de ontwikkelingsperiode van het systeem.
5. Het is zinvol om naar de derde normale vorm van de database te streven als:
- De richting van veranderingen in te automatiseren bedrijfsprocessen is moeilijk voorspelbaar.
- Binnen het implementatie- en/of ontwikkelteam is er een zwakke scheiding van taken.
- Systemen die deel uitmaken van het integratiecontour ontwikkelen zich volgens hun eigen plannen.
- Inconsistentie van gegevens kan leiden tot verlies van klanten of geld voor het bedrijf.
6. Het ontwerp van het datamodel moet door een analist worden uitgevoerd uitsluitend in relatie tot de modellen van het doelspecifieke bedrijfsproces en het proces in het informatiesysteem. Als een ontwikkelaar het datamodel ontwerpt, moet hij zich in het onderwerp verdiepen tot op het niveau van het begrijpen van het verschil tussen de waarden van attributen - een noodzakelijke voorwaarde om atomische attributen te выделять. Zo neemt hij functies op zich die niet eigen zijn.
4 Taak ter illustratie
Stel je voor dat je een kleine geautomatiseerde taverne in de haven hebt. Je marktsegment: zeelieden en piraten die de haven binnenkomen en behoefte hebben aan rust. Aan zeelieden verkoop je thee met tijm, en aan piraten rum en botten kammen voor het borstelen van hun baard. De service in de taverne zelf wordt geleverd door een robot-hostess en een robot-barman. Dankzij de hoge kwaliteit en lage prijzen heb je al je concurrenten verdrongen, zodat iedereen die van het schip komt, naar jouw taverne komt, die de enige in de haven is.
Het systeemcomplex van de taverne bestaat uit de volgende software:
- Een vroegtijdig waarschuwingssysteem voor klanten dat hun categorie herkent aan de hand van karakteristieke kenmerken.
- Een systeem voor het beheer van robots-hostessen en robots-barmannen.
- Een systeem voor het beheer van de voorraad en levering aan het verkooppunt.
- Een systeem voor het beheer van relaties met leveranciers (SRM).
Proces:
Het vroegtijdige waarschuwingssysteem herkent mensen die van het schip komen. Als iemand gladgeschoren is, wordt hij als zeeman beschouwd, als de persoon een baard heeft, wordt hij als piraat beschouwd.
Bij het binnenkomen van de taverne hoort de gast een begroeting van de robot-hostess volgens zijn categorie, bijvoorbeeld: "Ho-ho-ho, gewaardeerde piraat, ga aan tafel №..."
De gast loopt naar de aangegeven tafel, waar de robot-barman al producten heeft voorbereid op basis van de categorie. De robot-barman geeft informatie door aan het magazijnsysteem dat de volgende leveringsronde moet worden verhoogd, en het magazijninformatiesysteem vormt op basis van de voorraad een aanvraag voor aankoop in het SUPS.
Laat het vroege waarschuwingssysteem door uw interne IT zijn ontwikkeld, het programma voor het beheer van barrobots is door een externe aannemer speciaal voor uw bedrijf gemaakt. De systemen voor het beheer van het magazijn en de relaties met leveranciers zijn aangepaste off-the-shelf oplossingen van de markt.
5. Voorbeelden van denormalisatie en de impact ervan op de ontwikkeling van software
Bij het ontwerpen van het bedrijfsproces gaven de ondervraagde experts unaniem aan dat piraten over de hele wereld rum drinken en hun baarden borstelen met botten kammen, terwijl zeelieden thee met tijm drinken en altijd gladgeschoren zijn.
Er verschijnt een register van klanttypen met twee waarden: 1- piraten, 2 - zeelieden, gemeenschappelijk voor de gehele informatiecirkel van het bedrijf.
Het klantwaarschuwingssysteem slaat onmiddellijk het resultaat van de beeldverwerking op als de identificatie (ID) van de herkende klant en zijn type: zeeman of piraat.
ID van het herkende object
Klantcategorie
100500
Piraat
100501
Piraat
100502
Zeeman
Laten we nogmaals benadrukken dat
1. Onze zeelieden in werkelijkheid scheren zich
2. Onze piraten zijn eigenlijk mensen met baarden
Welke problemen moeten in dit geval worden opgelost om onze structuur naar de derde normale vorm te laten streven:
- schending van de atomiciteit van het attribuut - Klantcategorie
- vermenging van de geanalyseerde feit en conclusie in dezelfde tabel
- vastgelegde functionele afhankelijkheid tussen attributen van verschillende entiteiten.
In genormaliseerde vorm zouden we twee tabellen krijgen:
- resultaat van identificatie in de vorm van een set vastgestelde kenmerken,
ID van het herkende object
Gezichtshaar
100500
Yes
100501
Yes
100502
No
- resultaat van het bepalen van het klanttype als toepassing van de ingebouwde logica in het systeem voor de interpretatie van de vastgestelde kenmerken
ID van het herkende object
ID van identificatie
Klantcategorie
100500
100001
Piraat
100501
100002
Piraat
100502
100003
Zeeman
Hoe kan een genormaliseerde gegevensopslagorganisatie de ontwikkeling van een IS-systeem vergemakkelijken? Stel, ineens krijgt u nieuwe klanten. Laten we zeggen dat dit Japanse piraten zijn zonder baard, maar met een papegaai op hun schouder, en ecologische piraten; je herkent ze gemakkelijk aan het blauwe profiel van Greta op hun linkerborst.
Ecologische piraten kunnen uiteraard geen botten kammen gebruiken en eisen een alternatief van gerecycled zeeplastic.
U moet de algoritmes van de software aanpassen aan de nieuwe invoer. Als de normalisatieregels waren gevolgd, had u alleen in sommige systemen de invoeren voor bepaalde procesvertakkingen hoeven aan te vullen en nieuwe vertakkingen te creëren alleen voor die gevallen en in die IS-systemen waar de haargroei op het gezicht relevant is. Maar aangezien de regels niet zijn gevolgd, moet u de hele code analyseren in het volledige framework waar gebruik wordt gemaakt van de waarden van de clienttypen en precies vaststellen dat in het ene geval het algoritme rekening moet houden met de professionele activiteit van de klant en in het andere met de fysieke kenmerken.
In de vorm die probeert voor genormaliseerd, zouden we twee tabellen met operationele gegevens en twee databronnen hebben:
- resultaat van identificatie in de vorm van een set vastgestelde kenmerken,
ID van het herkende object
Greta op de linkerborst
Vogel op de schouder
Gezichtshaar
100510
1
1
1
100511
0
0
1
100512
1
0
- het resultaat van het bepalen van het type klant (laten we zeggen dat dit een gebruikersweergave is met beschrijvingen uit de databronnen weergegeven)
Betekent de ontdekte denormalisatie dat systemen niet kunnen worden aangepast aan nieuwe omstandigheden? Natuurlijk niet. Stel je voor dat alle IS-systemen door een team met nul verloop zijn gemaakt, dat de ontwikkeling goed is gedocumenteerd en informatie binnen het team zonder verlies wordt doorgegeven, de benodigde wijzigingen kunnen dan worden aangebracht met verwaarloosbare inspanning. Maar als we teruggaan naar de oorspronkelijke voorwaarden van de taak, zal er alleen al voor het afdrukken van de verslagen van gezamenlijke discussies 1,5 toetsenborden worden versleten en nog eens 0,5 voor de afronding van inkoopprocedures.
In het bovenstaande voorbeeld zijn alle drie de normale vormen geschonden, laten we proberen ze afzonderlijk te schenden.
Schending van de eerste normale vorm:
Stel je voor dat de goederen naar je magazijn worden geleverd vanuit de leveranciersmagazijnen via een 1,5 ton bestelbus die eigendom is van jouw herberg. De omvang van je bestellingen is zo klein ten opzichte van de omzetten van de leveranciers dat deze altijd van één op één worden uitgevoerd, zonder te wachten op productie. Heb je in dit bedrijfsproces aparte tabellen nodig voor: voertuigen, soorten voertuigen, en moet je planning en feit scheiden in je bestellingen die naar de leveranciers zijn gegaan?
Stel je eens voor hoeveel 'overbodige' verbindingen je programmeurs zullen moeten schrijven als ze het onderstaande model gebruiken voor de ontwikkeling van de software.
Stel dat we hebben besloten dat de voorgestelde structuur te complex is, voor onze situatie is het scheiden van planning en feit in de orderregistratie overbodige informatie, en de opgestelde orderspecificatie wordt herschreven op basis van de resultaten van de ontvangst van de aangekomen goederen, zeldzame fouten en levering van goederen van slechte kwaliteit worden buiten het systeem opgelost.
En op een dag zie je hoe de hele zaal van de herberg vol zit met boze en onverzorgde piraten. Wat is er gebeurd?
Het bleek dat met de groei van je bedrijf ook het verbruik toenam. Er was ooit een managementbesluit genomen dat als de bestelbus overbeladen was qua volume en/of gewicht, wat uiterst zeldzaam het geval was, de leverancier prioriteit gaf aan de lading in het voordeel van dranken.
Het niet geleverde product kwam in de volgende bestelling terecht en ging mee in de nieuwe rit, het hebben van een onverzettelijke voorraad in het magazijn van de herberg maakte het mogelijk om niet op te merken wanneer er fouten werden gemeld.
In de haven sloot de laatste concurrent, en de fout van de overbelasting van de bestelbus, die was omzeild door prioritering op basis van het aannemen van voldoende onverzettelijke voorraad en periodieke onderbelading van het voertuig, werd normaal. Het systeem dat was gecreëerd, zal perfect functioneren volgens de algoritmen die erin zijn gelegd en zal elke mogelijkheid missen om systematische niet-naleving van de geplande bestellingen te traceren. Alleen een beschadigde reputatie en ontevreden klanten zullen het probleem kunnen ontdekken.
Een oplettende lezer zal ongetwijfeld opgemerkt hebben dat de bestelde hoeveelheid in de specificatie van de bestelling (T_ORDER_SPEC) in sectie 2 en in sectie 5 zowel kan voldoen aan als niet kan voldoen aan de eis van de eerste normale vorm. Dit hangt er volledig van af of verschillende, wezenlijk verschillende meeteenheden in hetzelfde veld kunnen worden opgenomen, afhankelijk van het geselecteerde productassortiment.
Schending van de tweede normale vorm:
Naarmate uw behoeften toenemen, koopt u nog een paar transportmiddelen van verschillende groottes. In de hierboven beschreven context werd het creëren van een register van transportmiddelen als overbodig beschouwd, waardoor alle gegevensverwerkingsalgoritmen die de behoeften van het vervoer en het magazijn ondersteunen, de verplaatsing van goederen van de leverancier naar het magazijn als een rit van uitsluitend een 1,5 ton grote bestelwagen beschouwen. Dus, samen met het kopen van nieuwe transportmiddelen, creëert u toch een register van transportmiddelen, maar bij het aanpassen moet u de hele code analyseren die verwijst naar de verplaatsing van goederen, om te bepalen of er in elk specifiek geval verwijzingen zijn naar de kenmerken van het voertuig waarmee het bedrijf is begonnen.
Schending van de derde normale vorm:
Op een gegeven moment begint u een loyaliteitsprogramma te creëren en verschijnt er een record van een vaste klant. Waarom zou u bijvoorbeeld tijd besteden aan het creëren van materiële representaties die geaggregeerde gegevens over de verkopen aan een specifieke klant bewaren voor gebruik in rapportage en overdragen aan analysesystemen, als alles wat de klant bij de start van het loyaliteitsprogramma nodig heeft, op het record van de klant zelf kan worden geplaatst? En inderdaad lijkt het in eerste instantie geen zin te hebben. Maar elke keer dat uw bedrijf bijvoorbeeld nieuwe verkoopkanalen aansluit, moet er iemand onder uw analisten zijn die zich herinnert dat er zo'n aggregatie-attribuut bestaat.
Bij het ontwerpen van elk nieuw proces, laten we zeggen, online verkopen en verkopen via distributeurs die zijn aangesloten op het gezamenlijke loyaliteitssysteem, moet iemand in gedachten houden dat alle nieuwe processen ervoor moeten zorgen dat de dataintegriteit op code-niveau gewaarborgd is. Voor een industriële database met duizenden tabellen lijkt dit een nauwelijks haalbare taak.
Een ervaren ontwikkelaar weet natuurlijk hoe hij al deze genoemde problemen kan voorkomen, maar naar mijn mening is het de taak van een ervaren analist om te voorkomen dat ze zich voordoen.
Ik wil mijn dank uitspreken voor de waardevolle feedback bij de voorbereiding van de publicatie aan hoofdontwikkelaar Evgeny Yaruhin.
Literatuur
Connolly Thomas, Begg Caroline. Databases. Ontwerp, implementatie en onderhoud. Theorie en praktijk.
Bron: habr.com
