Aufbau Datenmodell für Stücklistenkalkulation

ukulele · 28 November 2023

Wenn ich deinen ersten Screenshot richtig interpretiere dann musst du als erstes die Menge mal Preis für jede Position ausrechnen und dann davon die Summe bilden, nicht umgekehrt. Wenn du 4 Baugruppe1 hast die jeweils 4 Baugruppe2 beinhalten die wiederrum mehrere Einzelteile beinhalten und am Ende steht der Preis 2500 dann musst du die Mengen alle miteinander multiplizieren und dann Menge * Preis rechnen und darüber die Summe. Das ist eigentlich nicht schwer sondern nur viel Code.

Problematisch ist der LEFT JOIN wenn die Struktur unterschiedlich tief ist. Also z.B. Baugruppe1 enthält ein Einzelteil und gleichzeitig zwei oder mehr Baugruppen2. Das Einzelteil kommt in eine eigene Spalte, so oft wie noch Baugruppen2 dazu kommen. Du kannst hier also nicht einfach eine Summe der Einzelteile bilden, die wäre verfälscht. Sollten in deinem Datenbestand solche Fälle vorkommen, das nicht Einzelteile immer nur auf der selben Ebene zugeordnet werden, musst du dir dafür was einfallen lassen. Vermutlich wirst du nicht drum herum kommen eine Abfrage pro Ebene mit Join auf Einzelteile zu machen und die Ergebnisse mit UNION zu verbinden.

Dein Datenmodell ist sehr komplex und hat vermutlich auch Fehler, ich kann mir zumindest auf Einzelteile nicht immer einen Reim machen. Du bist scheinbar in einer Lernphase in der du selbst noch nicht verstehst, was du da tust, da muss man durch :-)

Ich würde nicht nur das Datenmodel anders aufbauen sondern auch ein echtes DBMS verwenden. Woher kommen eigentlich die vielen Klammern um die Joins, macht Access das automatisch?

Ron787878 · 28 November 2023

ukulele schrieb:
Wenn ich deinen ersten Screenshot richtig interpretiere dann musst du als erstes die Menge mal Preis für jede Position ausrechnen und dann davon die Summe bilden, nicht umgekehrt. Wenn du 4 Baugruppe1 hast die jeweils 4 Baugruppe2 beinhalten die wiederrum mehrere Einzelteile beinhalten und am Ende steht der Preis 2500 dann musst du die Mengen alle miteinander multiplizieren und dann Menge * Preis rechnen und darüber die Summe. Das ist eigentlich nicht schwer sondern nur viel Code.

Problematisch ist der LEFT JOIN wenn die Struktur unterschiedlich tief ist. Also z.B. Baugruppe1 enthält ein Einzelteil und gleichzeitig zwei oder mehr Baugruppen2. Das Einzelteil kommt in eine eigene Spalte, so oft wie noch Baugruppen2 dazu kommen. Du kannst hier also nicht einfach eine Summe der Einzelteile bilden, die wäre verfälscht. Sollten in deinem Datenbestand solche Fälle vorkommen, das nicht Einzelteile immer nur auf der selben Ebene zugeordnet werden, musst du dir dafür was einfallen lassen. Vermutlich wirst du nicht drum herum kommen eine Abfrage pro Ebene mit Join auf Einzelteile zu machen und die Ergebnisse mit UNION zu verbinden.

Dein Datenmodell ist sehr komplex und hat vermutlich auch Fehler, ich kann mir zumindest auf Einzelteile nicht immer einen Reim machen. Du bist scheinbar in einer Lernphase in der du selbst noch nicht verstehst, was du da tust, da muss man durch Ich würde nicht nur das Datenmodel anders aufbauen sondern auch ein echtes DBMS verwenden. Woher kommen eigentlich die vielen Klammern um die Joins, macht Access das automatisch?

Vielen Dank für die schnelle Rückmeldung

. Wie du bereits erwähnt hast bin ich noch in der Lernphase.... und hatte es mal nur ein Semester lang .... an der Hochschule.... mal schauen wie lange es dauert bis ich einiger Maßen kann.

Zu deinem zweiten Abschnitt des Textes, genau vor diesem Problem stehe ich aktuell.... es kann nämlich immer unterschiedlich tief und eine unterschiedliche Anzahl besitzen, wodurch der erste Abschnitt bzw. deine Idee nicht in Frage kommt. Die Frage ist dann beim union, wie suche ich beim zweiten select nur die z.B. Baugruppen der zweiten 2 Ebene zum Produkt. Weil die kenn ich ja garnicht.... Hast du dafür eine Idee ? Wie würdest du das Datenmodell aufbauen? Aktuell steht nur ACCESS zur Verfügung und die Klammern im Select sind für ACCESS .... ohne funktionieren die Joins bei mir nicht.... kenne es aber anders aus der Oracle DB.

Beste Grüße

ukulele · 28 November 2023

Also wenn ich das richtig vermute dann gibt es einen Preis ja nur wenn eine Zuordnung n:m mit Einzelteile besteht. Ich habe jetzt hier mal zwar langes aber SQL mäßig maximal simples Beispiel angelegt, wie folgt:

Code:

CREATE TABLE tblEndprodukt(
    ID INT NOT NULL
    );
INSERT INTO tblEndprodukt VALUES(0);

CREATE TABLE tblBaugruppe1(
    ID INT NOT NULL,
    tblEndprodukt_ID INT NOT NULL,
    menge INT NOT NULL
    );
INSERT INTO tblBaugruppe1 VALUES(10,0,1);
INSERT INTO tblBaugruppe1 VALUES(11,0,2);

CREATE TABLE tblBaugruppe2(
    ID INT NOT NULL,
    tblBaugruppe1_ID INT NOT NULL,
    menge INT NOT NULL
    );
INSERT INTO tblBaugruppe2 VALUES(100,10,3);
INSERT INTO tblBaugruppe2 VALUES(101,10,4);
INSERT INTO tblBaugruppe2 VALUES(102,11,5);
INSERT INTO tblBaugruppe2 VALUES(103,11,6);

CREATE TABLE tblEinzelteil(
    ID INT NOT NULL,
    preis MONEY NOT NULL
    );

INSERT INTO tblEinzelteil VALUES(10000,19.90);
INSERT INTO tblEinzelteil VALUES(20000,29.90);
INSERT INTO tblEinzelteil VALUES(30000,39.90);
INSERT INTO tblEinzelteil VALUES(40000,49.90);

CREATE TABLE tblEinzelteilZuordnung1(
    tblBaugruppe1_ID INT NOT NULL,
    tblEinzelteil_ID INT NOT NULL,
    menge INT NOT NULL
    );

INSERT INTO tblEinzelteilZuordnung1 VALUES(10,10000,1);
INSERT INTO tblEinzelteilZuordnung1 VALUES(10,20000,2);
INSERT INTO tblEinzelteilZuordnung1 VALUES(11,10000,1);
INSERT INTO tblEinzelteilZuordnung1 VALUES(11,20000,2);
INSERT INTO tblEinzelteilZuordnung1 VALUES(11,30000,3);

CREATE TABLE tblEinzelteilZuordnung2(
    tblBaugruppe2_ID INT NOT NULL,
    tblEinzelteil_ID INT NOT NULL,
    menge INT NOT NULL
    );

INSERT INTO tblEinzelteilZuordnung2 VALUES(100,10000,1);
INSERT INTO tblEinzelteilZuordnung2 VALUES(100,20000,2);
INSERT INTO tblEinzelteilZuordnung2 VALUES(101,10000,1);
INSERT INTO tblEinzelteilZuordnung2 VALUES(101,20000,2);
INSERT INTO tblEinzelteilZuordnung2 VALUES(101,30000,3);
INSERT INTO tblEinzelteilZuordnung2 VALUES(103,30000,1);
INSERT INTO tblEinzelteilZuordnung2 VALUES(103,40000,2);

Es gibt die Ebene 0, das ist das Produkt. Jedes Produkt kann aus mehreren Baugruppen bestehen, die widerrum aus Baugruppen bestehen können (zwei Ebenen unter Produkt). Baugruppen können mehrfach in jedem Produkt bzw. jeder Baugruppe vorkommen, daher eine Menge*. Jede Baugruppe, egal welche Ebene, kann aus beliebig vielen Einzelteilen bestehen, wiederrum in beliebiger Menge (quasi die tiefste Ebene, n:m Zuordnung zur Baugruppe, nicht 1:n wie Produkt zu Baugruppe zu Baugruppe).

Es gibt nur eine Tabelle Einzelteile, so kann ein Einzelteil immer in allen Baugruppen Verwendung finden ohne das es in zwei Tabellen geflegt werden muss.

*) Das könnte man auch mit Baugruppen machen (die gibt es derzeit in zwei verschiedenen Tabellen). Dann müsste hier die Menge* aber auch wieder in eine Zwischentabelle Baugruppe zu Baugruppe wandern. In dem Moment bekommst du aber einen Selbstbezug, kannst also nicht wirklich verhindern, das beliebig viele Ebenen entstehen. Es können auch Schleifen entstehen, beides ist in Access nicht über SQL umsetzbar.

Code:

SELECT    t.ID,
t.ET_ID,
        sum(pos_preis) AS summe
FROM    (

SELECT    tblEndprodukt.*,
        tblBaugruppe1.ID AS BG1_ID,
        tblBaugruppe1.menge AS BG1_Menge,
        NULL AS BG2_ID,
        NULL AS BG2_Menge,
        tblEinzelteil.ID AS ET_ID,
        tblEinzelteilZuordnung1.menge AS ET_Menge,
        tblEinzelteil.preis AS ET_Preis,
        tblBaugruppe1.menge * tblEinzelteilZuordnung1.menge * tblEinzelteil.preis AS pos_preis
FROM    tblEndprodukt
INNER JOIN tblBaugruppe1
ON        tblEndprodukt.ID = tblBaugruppe1.tblEndprodukt_ID
INNER JOIN tblEinzelteilZuordnung1
ON        tblBaugruppe1.ID = tblEinzelteilZuordnung1.tblBaugruppe1_ID
INNER JOIN tblEinzelteil
ON        tblEinzelteilZuordnung1.tblEinzelteil_ID = tblEinzelteil.ID
UNION ALL
SELECT    tblEndprodukt.*,
        tblBaugruppe1.ID AS BG1_ID,
        tblBaugruppe1.menge AS BG1_Menge,
        tblBaugruppe2.ID AS BG2_ID,
        tblBaugruppe2.menge AS BG2_Menge,
        tblEinzelteil.ID AS ET_ID,
        tblEinzelteilZuordnung2.menge AS ET_Menge,
        tblEinzelteil.preis AS ET_Preis,
        tblBaugruppe1.menge * tblBaugruppe2.menge * tblEinzelteilZuordnung2.menge * tblEinzelteil.preis AS pos_preis
FROM    tblEndprodukt
INNER JOIN tblBaugruppe1
ON        tblEndprodukt.ID = tblBaugruppe1.tblEndprodukt_ID
INNER JOIN tblBaugruppe2
ON        tblBaugruppe1.ID = tblBaugruppe2.tblBaugruppe1_ID
INNER JOIN tblEinzelteilZuordnung2
ON        tblBaugruppe2.ID = tblEinzelteilZuordnung2.tblBaugruppe2_ID
INNER JOIN tblEinzelteil
ON        tblEinzelteilZuordnung2.tblEinzelteil_ID = tblEinzelteil.ID

        ) t
GROUP BY t.ID,ET_ID

Es gibt zwei Abfragen, jede Baugruppen-Ebene wird getrennt mit Einzelteile gejoint. Jede Position (ein Einzelteil) errechnet eine Summe, wie oft das Einzelteil gebraucht wird und wieviel das kostet.

Alle Einzelteile aus Baugruppe1 und Baugruppe2 werden per UNION verbunden. Ein Einzelteil kann mehrfach in der Positionsliste vorkommen, wenn es z.B. in Baugruppe1 und in Baugruppe2 vorkommt. Daher wird am Ende auf diesen Select (t) eine Aggregation durchgeführt, wahlweile auf Einzelteile oder komplett auf das Produkt. Das ist der Preis aller verbauten Einzelteile.

Jede weitere Ebene in Form einer Baugruppe erfordert eine Tabelle tblBaugruppeX und eine Zuordnungstabelle Baugruppe zu Einzelteil. Außerdem ein weiteres UNION und einen weiteren Select.

Ron787878 · 28 November 2023

ukulele schrieb:
Also wenn ich das richtig vermute dann gibt es einen Preis ja nur wenn eine Zuordnung n:m mit Einzelteile besteht. Ich habe jetzt hier mal zwar langes aber SQL mäßig maximal simples Beispiel angelegt, wie folgt:

Code:

CREATE TABLE tblEndprodukt( ID INT NOT NULL ); INSERT INTO tblEndprodukt VALUES(0); CREATE TABLE tblBaugruppe1( ID INT NOT NULL, tblEndprodukt_ID INT NOT NULL, menge INT NOT NULL ); INSERT INTO tblBaugruppe1 VALUES(10,0,1); INSERT INTO tblBaugruppe1 VALUES(11,0,2); CREATE TABLE tblBaugruppe2( ID INT NOT NULL, tblBaugruppe1_ID INT NOT NULL, menge INT NOT NULL ); INSERT INTO tblBaugruppe2 VALUES(100,10,3); INSERT INTO tblBaugruppe2 VALUES(101,10,4); INSERT INTO tblBaugruppe2 VALUES(102,11,5); INSERT INTO tblBaugruppe2 VALUES(103,11,6); CREATE TABLE tblEinzelteil( ID INT NOT NULL, preis MONEY NOT NULL ); INSERT INTO tblEinzelteil VALUES(10000,19.90); INSERT INTO tblEinzelteil VALUES(20000,29.90); INSERT INTO tblEinzelteil VALUES(30000,39.90); INSERT INTO tblEinzelteil VALUES(40000,49.90); CREATE TABLE tblEinzelteilZuordnung1( tblBaugruppe1_ID INT NOT NULL, tblEinzelteil_ID INT NOT NULL, menge INT NOT NULL ); INSERT INTO tblEinzelteilZuordnung1 VALUES(10,10000,1); INSERT INTO tblEinzelteilZuordnung1 VALUES(10,20000,2); INSERT INTO tblEinzelteilZuordnung1 VALUES(11,10000,1); INSERT INTO tblEinzelteilZuordnung1 VALUES(11,20000,2); INSERT INTO tblEinzelteilZuordnung1 VALUES(11,30000,3); CREATE TABLE tblEinzelteilZuordnung2( tblBaugruppe2_ID INT NOT NULL, tblEinzelteil_ID INT NOT NULL, menge INT NOT NULL ); INSERT INTO tblEinzelteilZuordnung2 VALUES(100,10000,1); INSERT INTO tblEinzelteilZuordnung2 VALUES(100,20000,2); INSERT INTO tblEinzelteilZuordnung2 VALUES(101,10000,1); INSERT INTO tblEinzelteilZuordnung2 VALUES(101,20000,2); INSERT INTO tblEinzelteilZuordnung2 VALUES(101,30000,3); INSERT INTO tblEinzelteilZuordnung2 VALUES(103,30000,1); INSERT INTO tblEinzelteilZuordnung2 VALUES(103,40000,2);

Es gibt die Ebene 0, das ist das Produkt. Jedes Produkt kann aus mehreren Baugruppen bestehen, die widerrum aus Baugruppen bestehen können (zwei Ebenen unter Produkt). Baugruppen können mehrfach in jedem Produkt bzw. jeder Baugruppe vorkommen, daher eine Menge*. Jede Baugruppe, egal welche Ebene, kann aus beliebig vielen Einzelteilen bestehen, wiederrum in beliebiger Menge (quasi die tiefste Ebene, n:m Zuordnung zur Baugruppe, nicht 1:n wie Produkt zu Baugruppe zu Baugruppe).

Es gibt nur eine Tabelle Einzelteile, so kann ein Einzelteil immer in allen Baugruppen Verwendung finden ohne das es in zwei Tabellen geflegt werden muss.

*) Das könnte man auch mit Baugruppen machen (die gibt es derzeit in zwei verschiedenen Tabellen). Dann müsste hier die Menge* aber auch wieder in eine Zwischentabelle Baugruppe zu Baugruppe wandern. In dem Moment bekommst du aber einen Selbstbezug, kannst also nicht wirklich verhindern, das beliebig viele Ebenen entstehen. Es können auch Schleifen entstehen, beides ist in Access nicht über SQL umsetzbar.

Code:

SELECT t.ID, t.ET_ID, sum(pos_preis) AS summe FROM ( SELECT tblEndprodukt.*, tblBaugruppe1.ID AS BG1_ID, tblBaugruppe1.menge AS BG1_Menge, NULL AS BG2_ID, NULL AS BG2_Menge, tblEinzelteil.ID AS ET_ID, tblEinzelteilZuordnung1.menge AS ET_Menge, tblEinzelteil.preis AS ET_Preis, tblBaugruppe1.menge * tblEinzelteilZuordnung1.menge * tblEinzelteil.preis AS pos_preis FROM tblEndprodukt INNER JOIN tblBaugruppe1 ON tblEndprodukt.ID = tblBaugruppe1.tblEndprodukt_ID INNER JOIN tblEinzelteilZuordnung1 ON tblBaugruppe1.ID = tblEinzelteilZuordnung1.tblBaugruppe1_ID INNER JOIN tblEinzelteil ON tblEinzelteilZuordnung1.tblEinzelteil_ID = tblEinzelteil.ID UNION ALL SELECT tblEndprodukt.*, tblBaugruppe1.ID AS BG1_ID, tblBaugruppe1.menge AS BG1_Menge, tblBaugruppe2.ID AS BG2_ID, tblBaugruppe2.menge AS BG2_Menge, tblEinzelteil.ID AS ET_ID, tblEinzelteilZuordnung2.menge AS ET_Menge, tblEinzelteil.preis AS ET_Preis, tblBaugruppe1.menge * tblBaugruppe2.menge * tblEinzelteilZuordnung2.menge * tblEinzelteil.preis AS pos_preis FROM tblEndprodukt INNER JOIN tblBaugruppe1 ON tblEndprodukt.ID = tblBaugruppe1.tblEndprodukt_ID INNER JOIN tblBaugruppe2 ON tblBaugruppe1.ID = tblBaugruppe2.tblBaugruppe1_ID INNER JOIN tblEinzelteilZuordnung2 ON tblBaugruppe2.ID = tblEinzelteilZuordnung2.tblBaugruppe2_ID INNER JOIN tblEinzelteil ON tblEinzelteilZuordnung2.tblEinzelteil_ID = tblEinzelteil.ID ) t GROUP BY t.ID,ET_ID

Es gibt zwei Abfragen, jede Baugruppen-Ebene wird getrennt mit Einzelteile gejoint. Jede Position (ein Einzelteil) errechnet eine Summe, wie oft das Einzelteil gebraucht wird und wieviel das kostet.

Alle Einzelteile aus Baugruppe1 und Baugruppe2 werden per UNION verbunden. Ein Einzelteil kann mehrfach in der Positionsliste vorkommen, wenn es z.B. in Baugruppe1 und in Baugruppe2 vorkommt. Daher wird am Ende auf diesen Select (t) eine Aggregation durchgeführt, wahlweile auf Einzelteile oder komplett auf das Produkt. Das ist der Preis aller verbauten Einzelteile.

Jede weitere Ebene in Form einer Baugruppe erfordert eine Tabelle tblBaugruppeX und eine Zuordnungstabelle Baugruppe zu Einzelteil. Außerdem ein weiteres UNION und einen weiteren Select.

Du bist Spitze ich schau mir das gleich heute Abend genau an

. Die IT hat ab heute MSSQL genehmigt, mal schauen was der bessere weg ist. Dort könnte man auch rekursive Selects machen, was super wäre. Wenn ich nicht weiter komme melde ich mich bei dir

. Du scheinst ja ein echter Profi zu sein

.

ukulele · 28 November 2023

Ja MSSQL kann ich gut, hat aber viel Schnittmenge zu anderen DBMS. Rekurison ist auch eine schöne Sache, klassische Stücklisten habe ich allerdings noch nicht gemacht. Was wird denn dein Frontend sein?

Ron787878 · 28 November 2023

Mein Frontend ist Tableau also das Business Intelligence Tool von Salesforce. Mein Idee wäre wenn es natürlich mit MSSQL zu verknüpfen wenn die IT es fertig installier hat

. Dann kann ich auch die rekursiven Abfragen in Tableau integrieren so der Plan. Hast du schon mit Tableau oder PowerBi gearbeitet?

ukulele · 29 November 2023

PowerBI ja, Tableau noch nicht. PowerBI kann viele Sachen nicht, sicherlich keine Rekursion. Das ist aber auch nicht schlimm, du brauchst nur ausreichend Rechte auf dem SQL Server um Views anzulegen etc. Und da die BI Tools eigentlich nur Ausgabe machen, kann man bei Bedarf auch alles im DBMS umsetzen.

Ron787878 · 29 November 2023

Ja genau, das muss ich testen. Ich kann ja in Tableau ein normales SQL Statement ausführen. Blos noch eine Frage vorab, wie würdest du die Verbindung ziehen bei rekursiven Aufrufen wo würdest du die ChildID setzen?

Hatte ja mal so eine Idee aber so wird es noch nicht funktionieren:
https://www.herber.de/bbs/user/164546.png

ukulele · 29 November 2023

Ich würde es ganz flach halten, zwei oder drei Tabellen, dein Ansatz ist zu kompliziert gedacht.

Code:

CREATE TABLE tblObjekt(
    ID INT NOT NULL,
    typ SMALLINT NOT NULL --1=Produkt; 2=Baugruppe; 3=Einzelteil
    );

INSERT INTO tblObjekt VALUES(0,1);
INSERT INTO tblObjekt VALUES(10,2);
INSERT INTO tblObjekt VALUES(11,2);
INSERT INTO tblObjekt VALUES(100,2);
INSERT INTO tblObjekt VALUES(1000,3);

CREATE TABLE tblZuordnung(
    mID INT NOT NULL,
    cID INT NOT NULL,
    menge INT NOT NULL
    );

INSERT INTO tblZuordnung VALUES(0,10,1);
INSERT INTO tblZuordnung VALUES(0,11,1);
INSERT INTO tblZuordnung VALUES(10,100,1);
INSERT INTO tblZuordnung VALUES(10,1000,1);
INSERT INTO tblZuordnung VALUES(11,1000,2);
INSERT INTO tblZuordnung VALUES(100,1000,22);

Rekursion (mit ein bisschen Spaß wie Ebene und Pfad):

Code:

WITH t(ebene,pfad,ID,typ,menge) AS (
    SELECT    1,
            cast(ID AS VARCHAR(8000)),
            ID,
            typ,
            NULL
    FROM    tblObjekt
    WHERE    typ = 1
    UNION ALL
    SELECT    t.ebene + 1,
            t.pfad + '.' + cast(c.ID AS VARCHAR(8000)),
            c.ID,
            c.typ,
            z.menge
    FROM    t
    INNER JOIN tblZuordnung z
    ON        t.ID = z.mID
    INNER JOIN tblObjekt c
    ON        z.cID = c.ID
    WHERE    t.ebene + 1 <= 100
    )
SELECT    t.*
FROM    t
ORDER BY t.pfad

Metadaten wie Eigenschaften eines Einzelteils oder sonstirgendwas belangloses wie z.B. Preis holst du dann aus einer dritten Tabelle mit Attributen zum Objekt. Das joinst du alles auf t wie es dir beliebt, da kannst du dann den Positionspreis ausrechnen, dann aggregieren, eigentlich alles. Preisgestaltung kann ja auch deutlich komplexer werden mit schwankenden Preisen, Wechselkursen, Blackweek...

Ron787878 · 10 Dezember 2023

@ukulele

Servus,

nach langem habe ich nun die MSSQL Datenbank .... jedoch ist mir aufgefallen, dass wenn ich mehrere Produkte habe z.B. Autos wie Skoda Oktavia oder VW Golf usw. diese sich aber aus den gleichen Teilen zusammensetzen weiß die Datenbank doch nicht wieviele Teile wo benötigt werden.

Hier aktuell mein Ansatz:

Daten:

1. Skoada Oktavia 123456 Endprodukt 40.000 € -

2. Türen 489282 Baugruppe 25.000 € 4
3. Türgriff 939039 Rohstoff 10.000 € 4
3. Inneverk. 673673 Baugruppe 10.000 € 4
4. Dämmung 363737 Rohstoff 9.000 € 4
4. Schraube 373873 Rohstoff 1.000 € 10
3. Lautsprecher 672674 Rohstoff 5.000 € 2

2. Motorhaube 676376 Baugruppe 15.000 € 1
3. Blech 478728 Rohstoff 8.000 € 1
3. Scharnier 334242 Baugruppe 6.000 € 1
4. Schraube 373873 Rohstoff 1.000 € 10
4. Halterung 456534 Rohstoff 5.000 € 1
3. Niete 522231 Rohstoff 1.000 € 10

1. VW Golf 234565 Endprodukt 12.500 € -

2. Türen 489282 Baugruppe 12.500 € 2
3. Türgriff 939039 Rohstoff 5.000 € 2
3. Inneverk. 673673 Baugruppe 5.000 € 2
4. Dämmung 363737 Rohstoff 4.500 € 2
4. Schraube 373873 Rohstoff 500 € 5
3. Lautsprecher 672674 Rohstoff 2.500 € 1

Insert & Create Code:


CREATE TABLE tblObjekt(
    Objekt_ID INT NOT NULL,
    objektTyp SMALLINT NOT NULL --1=Produkt; 2=Baugruppe; 3=Einzelteil
    );

INSERT INTO tblObjekt VALUES(123456,1);
INSERT INTO tblObjekt VALUES(489282,2);
INSERT INTO tblObjekt VALUES(673673,2);
INSERT INTO tblObjekt VALUES(676376,2);
INSERT INTO tblObjekt VALUES(334242,2);
INSERT INTO tblObjekt VALUES(939039,3);
INSERT INTO tblObjekt VALUES(363737,3);
INSERT INTO tblObjekt VALUES(373873,3);
INSERT INTO tblObjekt VALUES(672674,3);
INSERT INTO tblObjekt VALUES(478728,3);
INSERT INTO tblObjekt VALUES(456534,3);
INSERT INTO tblObjekt VALUES(522231,3);

INSERT INTO tblObjekt VALUES(234565,1);

CREATE TABLE tblZuordnung(
    Mother_ID INT NOT NULL,
    Child_ID INT NOT NULL,
    menge INT NOT NULL
    );

INSERT INTO tblZuordnung VALUES(123456,489282,4);
INSERT INTO tblZuordnung VALUES(123456,676376,1);
INSERT INTO tblZuordnung VALUES(489282,939039,4);
INSERT INTO tblZuordnung VALUES(489282,673673,4);
INSERT INTO tblZuordnung VALUES(489282,672674,2);
INSERT INTO tblZuordnung VALUES(673673,363737,4);
INSERT INTO tblZuordnung VALUES(673673,373873,10);
INSERT INTO tblZuordnung VALUES(676376,478728,1);
INSERT INTO tblZuordnung VALUES(676376,334242,1);
INSERT INTO tblZuordnung VALUES(676376,522231,10);
INSERT INTO tblZuordnung VALUES(334242,373873,10);
INSERT INTO tblZuordnung VALUES(334242,456534,1);

INSERT INTO tblZuordnung VALUES(234565,489282,2);
INSERT INTO 

CREATE TABLE tblDetaildaten(
    Objekt_ID INT NOT NULL,
    bezeichnung varchar(50) NOT NULL,
    preis INT
    );

INSERT INTO tblDetaildaten VALUES(123456, 'Skoda Oktavia', NULL);
INSERT INTO tblDetaildaten VALUES(489282, 'Tür', NULL);
INSERT INTO tblDetaildaten VALUES(939039, 'Türgriff', 2500);
INSERT INTO tblDetaildaten VALUES(673673, 'Inneverkleidung', NULL);
INSERT INTO tblDetaildaten VALUES(363737, 'Dämmung', 2250);
INSERT INTO tblDetaildaten VALUES(373873, 'Schraube', 100);
INSERT INTO tblDetaildaten VALUES(672674, 'Lautsprecher', 2500);
INSERT INTO tblDetaildaten VALUES(676376, 'Motorhaube', NULL);
INSERT INTO tblDetaildaten VALUES(478728, 'Blech', 8000);
INSERT INTO tblDetaildaten VALUES(334242, 'Scharnier', NULL);
INSERT INTO tblDetaildaten VALUES(456534, 'Halterung', 5000);
INSERT INTO tblDetaildaten VALUES(522231, 'Niete', 100);

INSERT INTO tblDetaildaten VALUES(234565, 'VW Golf', NULL);

Hier der Select Aufruf für den Preis eines VW Golfs:


WITH t(ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT 1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            NULL
    FROM tblObjekt
    WHERE Objekt_ID = 234565
    UNION ALL
    SELECT t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            z.menge
    FROM t
    INNER JOIN tblZuordnung z ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
)

-- Hier wird die Gesamtsumme über preis_berechnet berechnet
SELECT SUM(t.menge * tblDetaildaten.preis) as gesamter_preis_berechnet
FROM t
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID;

Jedoch das Problem ist nun, die Datenbank weiß ja garnicht, dass nur zwei Türen für den Golf benötigt werden.... wie würdest du das machen? Mir ist noch keine schlaue Lösung dazu eingefallen.

Danke für alle Tipps und Ideen.

akretschmer · 10 Dezember 2023

schau mal hier wie es geht: Spaß mit PostgreSQL slide 12

Ron787878 · 10 Dezember 2023

akretschmer schrieb:
schau mal hier wie es geht: Spaß mit PostgreSQL slide 12

Danke, aber wenn ich ein zweites Auto anlege habe ich doch dort auch das selbe Problem.... oder?

akretschmer · 10 Dezember 2023

ja, je nach Produkt mußt du das halt korrekt anlegen. Ist jetzt nicht so überraschend, oder?

Ron787878 · 10 Dezember 2023

akretschmer schrieb:
ja, je nach Produkt mußt du das halt korrekt anlegen. Ist jetzt nicht so überraschend, oder?

Ja, aber schau oben mal mein Beispiel an wie würdest du es da machen? Die Datenbank hat die Werte der Baugrupppen grundsätzlich für den Skoda Oktavia, aber nicht für den VW Golf.

ukulele · 11 Dezember 2023

Code:

WITH t(ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT 1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            NULL
    FROM tblObjekt
    WHERE Objekt_ID = 234565
    UNION ALL
    SELECT t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            z.menge
    FROM t
    INNER JOIN tblZuordnung z ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
)

-- Hier wird die Gesamtsumme über preis_berechnet berechnet
SELECT *,
        t.menge * tblDetaildaten.preis AS position_preis
        --SUM(t.menge * tblDetaildaten.preis) as gesamter_preis_berechnet
FROM t
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID;

ebene pfad Objekt_ID objektTyp menge Objekt_ID bezeichnung preis position_preis
1 234565 234565 1 NULL 234565 VW Golf NULL NULL
2 234565.489282 489282 2 2 489282 Tür NULL NULL
3 234565.489282.673673 673673 2 4 673673 Inneverkleidung NULL NULL
3 234565.489282.939039 939039 3 4 939039 Türgriff 2500 10000
3 234565.489282.672674 672674 3 2 672674 Lautsprecher 2500 5000
4 234565.489282.673673.363737 363737 3 4 363737 Dämmung 2250 9000
4 234565.489282.673673.373873 373873 3 10 373873 Schraube 100 1000

Deine DB weiß sehr wohl das es 2 Türen im Golf gibt, ich habe deine Testdaten 1:1 übernommen. Die Menge fließt auch korekt in die Berechnung ein. Deine DB weiß allerdings nicht, was eine Tür von einem Golf und die Innenverkleidung kosten.

Aufbau Datenmodell für Stücklistenkalkulation

ukulele

Datenbank-Guru

Ron787878

Benutzer

ukulele

Datenbank-Guru

Ron787878

Benutzer

ukulele

Datenbank-Guru

Ron787878

Benutzer

ukulele

Datenbank-Guru

Ron787878

Benutzer

ukulele

Datenbank-Guru

Ron787878

Benutzer

akretschmer

Datenbank-Guru

Ron787878

Benutzer

akretschmer

Datenbank-Guru

Ron787878

Benutzer

ukulele

Datenbank-Guru