Databasemodel productonderhoud

[Mister GT]

Hoi allen,

Ik probeer een fatsoenlijk databasemodel te maken voor het volgende geval:

Ik werk bij een bedrijf wat medische apparatuur verkoopt. Zodoende wordt van elk verkocht artikel het serienummer bijgehouden (en evt. lot-nummer voor producten die dat hebben). Normaliter verlaten deze producten via de pakketdienst ons magazijn. Nu leveren wij ook onderhoudscontracten aan klanten die dit wensen. Een van onze installateurs komt op locatie om alles te installeren, en daarna wordt het contract getekend. Daarna komen wij jaarlijks langs om in onderhoud te voorzien.

Alle verkochte producten komen in de "product" tabel. Producten die daarnaast ook in onderhoud zijn komen in "maintained_product" waarin in een link staat naar de "product" tabel.

Nu is dit an sich niet lastig, maar er zijn een paar extra regels die het voor mij wat moeilijker maken:
- niet alle producten die geïnstalleerd worden zijn onderhoudsproducten
- nieuwe producten kunnen ook geïnstalleerd worden tijdens een onderhoudsbeurt.

Ik twijfel over hoe ik dit nu het beste kan opslaan. Op het moment heb ik een tabel "work_sheet" wat een klantenbezoek vertegenwoordigd. Een work sheet heeft meerdere regels, uit de tabel "work_sheet_line". In deze tabel wil ik opslaan:

- Uitgevoerde werkzaamheden (korte beschrijving: bijv. product geinstalleerd of product onderhouden)
- Het product waar de werkzaamheden voor zijn uitgevoerd

Alleen.. soms betekent "product" dus een product wat in onderhoud is --> "maintained_product" tabel, maar het kan net zo goed gaan om een product wat eenmalig wordt geïnstalleerd en waar wij verder geen onderhoud voor verlenen --> "product" tabel.

Een mogelijke oplossing die ik heb is om twee "work_sheet_line" tabellen te maken, waarbij de eerste tabel een link heeft naar "maintained_product" en de tweede naar "product", maar ik vraag me af of het ook ander/beter kan. (nog niet in onderstaande schema verwerkt)

Schema:


En voor de volledigheid ook wat code. Ik schrijf het in PHP en maak gebruik van Doctrine. Dit is voor als ik twee verschillende tabellen met work lines zou gebruiken.

PHP:

/**
 * @ORM\Entity
 * @ORM\Table
 */
class WorkSheet
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue (strategy="AUTO")
     * @ORM\Column (type="integer")
     */
    protected $id;

    /**
     * @ORM\ManyToOne (targetEntity="Administration\Model\Location")
     * @ORM\JoinColumn(name="location_id", referencedColumnName="id", nullable=false)
     */
    protected $location;

    /**
     * @ORM\ManyToOne (targetEntity="Administration\Model\Relation")
     * @ORM\JoinColumn(name="relation_id", referencedColumnName="id", nullable=false)
     */
    protected $relation;

    /**
     * @ORM\OneToMany(targetEntity="MaintainedProductWorkLine", mappedBy="workSheet")
     */
    protected $maintainedProductLines;

    /**
     * @ORM\OneToMany(targetEntity="ProductWorkLine", mappedBy="workSheet")
     */
    protected $productLines;
}

Iemand die me te hulp kan schieten?

[EvilWhiteDragon]

Lijkt mij het makkelijkst om of in work_sheet_line een kolom te maken welke aangeeft of het om een maintained product gaat dan wel verkoop. Indien beide van toepassing kunnen zijn kan je ook gewoon een kolom met maintained_product_id en een tweede met product_id maken, zoveel extra ruimte zal dat niet kosten. Daarnaast maakt het relaties leggen ook niet onnodig ingewikkeld.

[NNF]

Simpel: de tabel 'maintained_product' schrappen en de eigenschappen 'maintained' (int(1): 1=onderhoudscontract, 0=geen onderhoudscontract) en 'installed_on' (brrr... doe dan 'install_date', maar dat is misschien persoonlijk ) als kolommen toevoegen aan 'product'. Het zijn immers gewoon eigenschappen van een product, dus dat is prima.

[ Voor 16% gewijzigd door NNF op 11-08-2016 23:56 ]

[ocf81]

Op welk niveau begeeft dit systeem zich? Het lijkt om een operationeel systeem te gaan, een systeem dat op de werkvloer gebruikt wordt om de werkzaamheden direct te ondersteunen. In dat geval is 3NF of BCNF de meest geëigende manier om de DB vorm te geven.

Ik mis in je tabelstructuur toch wel wat indirectie. Er is hier een hele directe koppeling die het lastig maakt om achteraf zaken anders te structureren of hermodelleren zonder destructieve changes.

[epic007]

Wat ik mis in je model is een koppeling tussen de klant en het product, dwz welke klant heeft welk product. In deze relatie tabel zou ik dan het type onderhouds contract vastleggen( of in een gerelateerde tabel als er meerdere contracten kunnen zijn). Het geleverde werk kan hier dan ook aan worden gekoppeld.
De maintained_product tabel kan dan geschrapt worden.

[Mister GT]

EvilWhiteDragon schreef op donderdag 11 augustus 2016 @ 22:16:
Lijkt mij het makkelijkst om of in work_sheet_line een kolom te maken welke aangeeft of het om een maintained product gaat dan wel verkoop. Indien beide van toepassing kunnen zijn kan je ook gewoon een kolom met maintained_product_id en een tweede met product_id maken, zoveel extra ruimte zal dat niet kosten. Daarnaast maakt het relaties leggen ook niet onnodig ingewikkeld.

Dat is inderdaad wel het makkelijkste tot nu toe.

NNF schreef op donderdag 11 augustus 2016 @ 23:47:
Simpel: de tabel 'maintained_product' schrappen en de eigenschappen 'maintained' (int(1): 1=onderhoudscontract, 0=geen onderhoudscontract) en 'installed_on' (brrr... doe dan 'install_date', maar dat is misschien persoonlijk ) als kolommen toevoegen aan 'product'. Het zijn immers gewoon eigenschappen van een product, dus dat is prima.

Zou kunnen, maar op moment van schrijven is misschien 20% van alle verkochte artikelen een product met onderhoudscontract. Dat zou betekenen dat dus 80% van de tabel met onnodig veel lege kolommen eindigt. Dat lijkt me niet wenselijk.

ocf81 schreef op donderdag 11 augustus 2016 @ 23:57:
Op welk niveau begeeft dit systeem zich? Het lijkt om een operationeel systeem te gaan, een systeem dat op de werkvloer gebruikt wordt om de werkzaamheden direct te ondersteunen. In dat geval is 3NF of BCNF de meest geëigende manier om de DB vorm te geven.

Ik mis in je tabelstructuur toch wel wat indirectie. Er is hier een hele directe koppeling die het lastig maakt om achteraf zaken anders te structureren of hermodelleren zonder destructieve changes.

Klopt, het gaat om een systeem wat de huidige manier met Excel-sheets en veel e-mails moet gaan vervangen. Wat betreft de normaalvormen, ik ben geen database-specialist pur sang, dus deze gaan mij een beetje de pet te boven. Kun je eventueel een voorbeeld geven?

Hoe zou ik indirectie beter kunnen aanpakken? Minder relaties bijvoorbeeld?

epic007 schreef op vrijdag 12 augustus 2016 @ 10:01:
Wat ik mis in je model is een koppeling tussen de klant en het product, dwz welke klant heeft welk product. In deze relatie tabel zou ik dan het type onderhouds contract vastleggen( of in een gerelateerde tabel als er meerdere contracten kunnen zijn). Het geleverde werk kan hier dan ook aan worden gekoppeld.
De maintained_product tabel kan dan geschrapt worden.

Deze heb ik in mijn schema buiten beschouwing gelaten, maar die is wel aanwezig. Voor reguliere verzendingen heb ik een "shipment" tabel en een koppeltabel "shipment_product". Voor onderhoudscontracten wilde ik hetzelfde doen: een tabel "contract" en een tabel "contract_maintained_product".

[NNF]

Mister GT schreef op vrijdag 12 augustus 2016 @ 10:21:
Zou kunnen, maar op moment van schrijven is misschien 20% van alle verkochte artikelen een product met onderhoudscontract. Dat zou betekenen dat dus 80% van de tabel met onnodig veel lege kolommen eindigt. Dat lijkt me niet wenselijk.

Huh? Die snap ik niet. Je tabel zou er dan zo uitzien:

code:

id      product_type_id     serial_code     maintained      installed_on
1       1                   XXX             0               2026-01-15
2       2                   XXX             0               2026-01-16
3       2                   XXX             0               2026-01-17
4       1                   XXX             1               2026-01-18
5       1                   XXX             1               2026-01-19

Dan is er toch niets leeg?

Wat mij juist onwenselijk lijkt is een splitsing tussen 'maintained_product' en 'product'. Een product is een product, of daar een onderhoudscontract aan hangt of niet is een eigenschap van dat product. Dat moet je niet in een aparte tabel willen opslaan. Daarmee maak je het jezelf onnodig moeilijk.

[Mister GT]

Klopt, daar heb je gelijk in. Alleen zijn er nog meerdere kolommen specifiek aan onderhoudsproducten: geschatte vervangdatum, huidige status (in orde, vervangen, actie vereist), onderhoudspakket (elk product kan tot 3 verschillende onderhoudsniveau's hebben binnen een contract), laatste onderhoudsdatum. Deze heb ik tot nu toe buiten beschouwing gelaten, maar dat is nu wel relevant.

[ Voor 3% gewijzigd door Mister GT op 12-08-2016 14:14 ]

[NNF]

Dan zet je dat in een aparte tabel, maintenance_status oid. I.p.v. 'maintained' geef je het product een kolom 'maintenance_status_id' en klaar is Kees. Bevat de kolom een ID dan gaat het om een product dat onderhouden wordt, is de kolom NULL (of leeg, als je anti-NULL bent ) dan wordt het product niet onderhouden.

[ Voor 6% gewijzigd door NNF op 12-08-2016 20:49 ]

[ocf81]

Mister GT schreef op vrijdag 12 augustus 2016 @ 10:21:

...

Klopt, het gaat om een systeem wat de huidige manier met Excel-sheets en veel e-mails moet gaan vervangen. Wat betreft de normaalvormen, ik ben geen database-specialist pur sang, dus deze gaan mij een beetje de pet te boven. Kun je eventueel een voorbeeld geven?

Hoe zou ik indirectie beter kunnen aanpakken? Minder relaties bijvoorbeeld?

...

Indirectie bereik je door de entiteiten niet direct aan elkaar te koppelen, maar via koppeltabellen te laten lopen. Op deze manier kan je ervoor zorgen dat je makkelijker aanpassingen kan doorvoeren. Tevens kan je op deze manier makkelijker relaties leggen tussen meerdere entiteiten zonder dat je de tabellen van de entiteiten laat exploderen met FKs.

Als je een beter model wil opstellen zou ik zeker eens goed nadenken over welke core business concepts je hebt en hoe ze zich van elkaar onderscheiden en hoe ze zich tot elkaar verhouden.
Op zich heb je daar wel even over nagedacht, maar daar is nog wel ruimte voor verbetering denk ik.
Neem nu de onderdelen bijvoorbeeld. Deze komen zowel terug als op zichzelfstaande entiteiten en als composieten in een service of in een assembly van onderdelen tot een product. Verder lijkt het mij dat een maintained product ergens gekoppeld moet zijn aan een klant via een soort van (onderhouds)contract (al dan niet betaald). Hoewel je nu de producten bijhoudt, zie ik geen koppeling met een onderhoudscontract o.i.d.

Daar zul je toch een kloppende structuur voor moeten opzetten lijkt me.

Anyways, succes

[armageddon_2k1]

Even iets anders. Ik werk bij een bedrijf dat medische apparatuur maakt en wij zijn tot ontzettend veel dingen, terecht, verplicht kwa procedures en regelgeving. Onze verkoopkanalen ook. Een zelfgemaakte database is bij ons dus absoluut een nogo, omdat het een niet gevalideerde tool is. Wie zegt mij dat persoon A ook alleen de dingen kan wijzigen die persoon A mag wijzigen om maar een voorbeeld te noemen. Zien jullie iets over het hoofd of henben jullie andere eisen?

[Mister GT]

NNF schreef op vrijdag 12 augustus 2016 @ 20:22:
Dan zet je dat in een aparte tabel, maintenance_status oid. I.p.v. 'maintained' geef je het product een kolom 'maintenance_status_id' en klaar is Kees. Bevat de kolom een ID dan gaat het om een product dat onderhouden wordt, is de kolom NULL (of leeg, als je anti-NULL bent ) dan wordt het product niet onderhouden.

Ik denk dat ik het zo ongeveer maar ga oplossen. Alleen houd ik de dependency omgekeerd (dus van maintained_status naar product en niet vice versa) om m'n product tabel zo clean mogelijk te houden. Geen anti-NULL maar ik wil graag dat het hele onderhoudssysteem los te koppelen is van de rest. De hele workflow om pakketten te versturen via deze tool staat al enige tijd op poten, en in een later stadium wil ik ook voorraadbeheer (wat nu alleen via ons factureringspakket eigenlijk gebeurd) toevoegen. Zodoende houd ik het liefste alles een beetje gescheiden.

ocf81 schreef op zaterdag 13 augustus 2016 @ 01:17:
[...]

Indirectie bereik je door de entiteiten niet direct aan elkaar te koppelen, maar via koppeltabellen te laten lopen. Op deze manier kan je ervoor zorgen dat je makkelijker aanpassingen kan doorvoeren. Tevens kan je op deze manier makkelijker relaties leggen tussen meerdere entiteiten zonder dat je de tabellen van de entiteiten laat exploderen met FKs.

Als je een beter model wil opstellen zou ik zeker eens goed nadenken over welke core business concepts je hebt en hoe ze zich van elkaar onderscheiden en hoe ze zich tot elkaar verhouden.
Op zich heb je daar wel even over nagedacht, maar daar is nog wel ruimte voor verbetering denk ik.
Neem nu de onderdelen bijvoorbeeld. Deze komen zowel terug als op zichzelfstaande entiteiten en als composieten in een service of in een assembly van onderdelen tot een product. Verder lijkt het mij dat een maintained product ergens gekoppeld moet zijn aan een klant via een soort van (onderhouds)contract (al dan niet betaald). Hoewel je nu de producten bijhoudt, zie ik geen koppeling met een onderhoudscontract o.i.d.

Daar zul je toch een kloppende structuur voor moeten opzetten lijkt me.

Anyways, succes

Helder, ik ga eens spelen of ik dat ergens kan gaan toepassen. Lijkt me inderdaad een goed idee! Bedankt voor je uitleg.

Op dit moment heb ik een product --> part idee opgezet als een self-referencing key in m'n maintained_product tabel. Misschien verhuis ik dit wel naar de gewone product tabel. Alhoewel het daar eigenlijk onnodig is, aangezien het voor ons alleen relevant is om deze relatie bij te houden indien de producten in onderhoud zijn. Anders eigenlijk niet.

Zoals hierboven in een eerder post heb ik al wel een relatie tussen contracten en producten op papier staan.

armageddon_2k1 schreef op zaterdag 13 augustus 2016 @ 09:31:
Even iets anders. Ik werk bij een bedrijf dat medische apparatuur maakt en wij zijn tot ontzettend veel dingen, terecht, verplicht kwa procedures en regelgeving. Onze verkoopkanalen ook. Een zelfgemaakte database is bij ons dus absoluut een nogo, omdat het een niet gevalideerde tool is. Wie zegt mij dat persoon A ook alleen de dingen kan wijzigen die persoon A mag wijzigen om maar een voorbeeld te noemen. Zien jullie iets over het hoofd of henben jullie andere eisen?

Wij verkopen medische apparatuur voor non-professionals. Ik denk dat dat al een stuk uit maakt. Ik heb zelf wel eens contact gehad met de fabrikant en hier is nooit over besproken. Desalniettemin vind ik het wel een goede opmerking en ik ga mijn leidinggevende hier nog even over benaderen.

[ Voor 3% gewijzigd door Mister GT op 13-08-2016 14:44 ]

[armageddon_2k1]

Mister GT schreef op zaterdag 13 augustus 2016 @ 14:42:
[...]
Wij verkopen medische apparatuur voor non-professionals. Ik denk dat dat al een stuk uit maakt. Ik heb zelf wel eens contact gehad met de fabrikant en hier is nooit over besproken. Desalniettemin vind ik het wel een goede opmerking en ik ga mijn leidinggevende hier nog even over benaderen.

Dat is slim, want het maakt overigens niet uit of het een professional is :-) Een apparaat heeft een medische classificatie (volgens MEDDEV in EU en de FDA in US) en daar horen bepaalde procedures bij, ongeacht aan wie je het verkoopt. Het zal allemaal niet zo'n storm lopen vermoed ik, maar kan geen kwaad eens na te vragen. Daarnaast zijn jullie een doorverkoper en geen fabrikant / manufacturer en zouden de eisen dus ook vanuit het bedrijf / de bedrijven moeten komen voor wie jullie verkopen. Die hebben een verplichting het hele proces van creatie tot supply chain onder controle te hebben.

[BarôZZa]

Volgens mij kan je het best even de databasestructuur van een willekeurig (gratis) ecommerce platform bekijken. Volgens mij is dat zelfs prima geschikt om intern voor dit doeleinde te gebruiken: je maakt eenvoudig verschillende soorten producten aan met bepaalde attributen, geeft ze voorraad, maakt klanten aan en vervolgens orders voor de klant die uit 1 of meer producten bestaan. Ook handig om klanten/orders op te zoeken, mails te versturen, offertes op te stellen, rapporten te genereren etc..

De products tabel van jou lijkt meer op een sales/order tabel. In een productentabel zou ik de verschillende soorten producten verwachten die jullie aanbieden, deze hebben dan weer voorraad die bestaat uit exemplaren van dat product met een unieke seriecode en deze worden dan uiteindelijk verkocht (sales/order tabellen)

De planned_installation tabel lijkt me ook overbodig, dat zijn de appointments. Anders moet je voor elke soort afspraak een aparte tabel erin fietsen (want behalve installation heb je dus ook service en mogelijk meer). Ik weet niet of die service afhankelijk is van het aantal/soort producten, want dan zou ik die aan de verkochte producten hangen. Anders kom je mogelijk in de knoei als een klant meerdere keren hetzelfde product bestelt en je niet weet waar de installatie of onderhoud voor is.

Maar zoals gezegd denk ik dat je met een standaard platform en wat extensies vrij snel de functionaliteit hebt die je wil. Bij deze structuur vrees ik dat je later tegen problemen aan gaat lopen en je een monster creëert wanneer er later aanpassingen worden gedaan.

[MarcoC]

Ik vind eerlijk gezegd niet zoveel mis met jouw model. Ik denk dat het handiger is om te gaan testen wat handig werkt en wat niet, dan hier eindeloos over blijven nadenken. Als jouw model werkt, dan werkt het.

[Mister GT]

BarôZZa schreef op zondag 14 augustus 2016 @ 14:53:
Volgens mij kan je het best even de databasestructuur van een willekeurig (gratis) ecommerce platform bekijken. Volgens mij is dat zelfs prima geschikt om intern voor dit doeleinde te gebruiken: je maakt eenvoudig verschillende soorten producten aan met bepaalde attributen, geeft ze voorraad, maakt klanten aan en vervolgens orders voor de klant die uit 1 of meer producten bestaan. Ook handig om klanten/orders op te zoeken, mails te versturen, offertes op te stellen, rapporten te genereren etc..

De products tabel van jou lijkt meer op een sales/order tabel. In een productentabel zou ik de verschillende soorten producten verwachten die jullie aanbieden, deze hebben dan weer voorraad die bestaat uit exemplaren van dat product met een unieke seriecode en deze worden dan uiteindelijk verkocht (sales/order tabellen)

De planned_installation tabel lijkt me ook overbodig, dat zijn de appointments. Anders moet je voor elke soort afspraak een aparte tabel erin fietsen (want behalve installation heb je dus ook service en mogelijk meer). Ik weet niet of die service afhankelijk is van het aantal/soort producten, want dan zou ik die aan de verkochte producten hangen. Anders kom je mogelijk in de knoei als een klant meerdere keren hetzelfde product bestelt en je niet weet waar de installatie of onderhoud voor is.

Maar zoals gezegd denk ik dat je met een standaard platform en wat extensies vrij snel de functionaliteit hebt die je wil. Bij deze structuur vrees ik dat je later tegen problemen aan gaat lopen en je een monster creëert wanneer er later aanpassingen worden gedaan.

We hebben reeds een compleet factureringspakket waarin natuurlijk alle functionaliteit zit die je opnoemt. Het is niet mijn doel noch mijn wens om dit te vervangen.

We hebben gekeken naar bestaande oplossingen maar konden niet iets passends vinden.

Appointments zijn generiek en hebben een type. Daarnaast is het natuurlijk belangrijk om te weten welke producten geïnstalleerd moeten worden wat die extra tabel dus nodig maakt. In een latere stadium kan ik hiermee gewenste voorraadniveaus berekenen.

[ Voor 6% gewijzigd door Mister GT op 17-08-2016 07:54 ]