Hallo Leute,
ich bastel mir gerade ein paar Abfragen für meine selbstgestrickte
Bauteileverwaltung und komme an einem Punkt einfach nicht weiter.
Und zwar habe ich in einer Tabelle die Bezugsquellen für ein Teil:
int Teile_ID
int Lieferant_ID
varchar Bestell_Bezeichnung
int Preis_ID
date Zuletzt_Aktualisiert
Die Tabelle mit den Staffelpreisen ist wie folgt aufgebaut:
int ID
int Stueckzahl
float Preis
Jetzt kann ich natürlich über die Verknüpfung
Bezugsquelle.Preis_ID->Staffelpreis.ID für eine Stückzahl einen Preis
heraussuchen. Das schaffe ich aber nicht in SQL selbst, sondern das muss
ich in meiner PHP-Logik in einer Schleife selbst machen, in der getestet
wird, zwischen welchen Staffelpreisen die gewünschte Stückzahl liegt.
Was ich gerne hätte, wäre eine SQL-Abfrage, mit der ich direkt den
Gesamtpreis, nicht den Einzelpreis, für eine bestimmte Stückzahl in der
Abfrage herausbekomme, d.h. ich brauche nicht nur eine Multiplikation,
sondern auch eine Entscheidung... Zurück hätte ich auch gerne die zu
bestellende Stückzahl, falls es eine Mindestbestellmenge gibt oder falls
es günstiger ist, 50 Stück zu bestellen als 49.
Als Beispiel gibt es eine Preisstaffelung wie folgt:
ab 10 Stück: 3,99 €
ab 50 Stück: 3,15 €
Das hieße, das man niemals 49 Stück bestellen sollte, und für jede
Stückzahlabfrage unterhalb von 10 sollte der Preis für 10 Stück
zurückgegeben werden (Mindestbestellmenge).
Das Datenbank-Design ist ungünstig.
Jeder Artikel hat genau einen Preis.
Eigentlich macht es so noch nicht einmal sinn, die Preise in eine eigene
Tabelle auszulagern, weil man sicher nicht von mehreren Artikeln auf
einen Preiseintrag verlinken möchte.
ein und der selbe Artikel kann jedoch unterschiedliche Staffelpreise
haben.
der eintrag Preis_ID sollte also weg und stattdessen in die Tabelle
Preis eine Bezugsquelle ID.
dann kann man für einen Artikel nämlich Tatsächlich Staffelpreise
erzeugen.
Gerhard schrieb:> Zurück hätte ich auch gerne die zu> bestellende Stückzahl, falls es eine Mindestbestellmenge gibt oder falls> es günstiger ist, 50 Stück zu bestellen als 49.
Sprich: mit dem Derzeitigem Schema geht es gar nicht.
Gerhard schrieb:> Jetzt kann ich natürlich über die Verknüpfung> Bezugsquelle.Preis_ID->Staffelpreis.ID für eine Stückzahl einen Preis> heraussuchen. Das schaffe ich aber nicht in SQL selbst, sondern das muss> ich in meiner PHP-Logik in einer Schleife selbst machen, in der getestet> wird, zwischen welchen Staffelpreisen die gewünschte Stückzahl liegt.
So wie es aussieht fehlt es dir aber an absoluten Grundkenntnissen in
SQL, die du dir sowiso aneignen solltenst, wenn du sowas vor hast.
Die Abfrage ist simple:
SELECT * FROM Bezugsquellen as b, Preise as p WHERE pr.ID = b.preis_ID;
dies gibt dir eine Liste aller Bezugsquellen, die auch einen Eintrag in
Preise haben.
Eine Multiplikation ist einfach:
SELECT Preis, Stueckzahl, (Preis * Stuckzahl) AS Gesamtpreis FROM
Preise;
Must du halt mit einer entsprechenden Abfrage kombinieren
Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.
Moin Vlad,
danke für deinen hilfreichen Beitrag. Ich habe kein angeborenes Talent
für SQL und mich nur genau so weit eingelesen, wie ich unbedingt musste,
um meine Bauteileverwaltung auf die Beine zu stellen.
> Jeder Artikel hat genau einen Preis.
Jeder Artikel hat einen (Staffel-) Preis pro Bezugsquelle, ja.
> Eigentlich macht es so noch nicht einmal sinn, die Preise in eine eigene> Tabelle auszulagern, weil man sicher nicht von mehreren Artikeln auf> einen Preiseintrag verlinken möchte.
Ok, ich kannte keinen besseren Weg, diese 1:n-Beziehung darzustellen,
denn ein Staffelpreis sind ja mehrere Preise... Hm.
> der eintrag Preis_ID sollte also weg und stattdessen in die Tabelle> Preis eine Bezugsquelle ID. dann kann man für einen Artikel nämlich> Tatsächlich Staffelpreise erzeugen.
Klingt logisch, wenn auch nicht intuitiv (für mich). Ich denke mal
darüber nach.
> Eine Multiplikation ist einfach:> SELECT Preis, Stueckzahl, (Preis * Stuckzahl) AS Gesamtpreis FROM> Preise;
Das ist mir klar, aber gibt es die Möglichkeit, in EINER Abfrage mit
Artikel-ID und gewünschter Stückzahl die günstigste zu bestellende
Anzahl und den Gesamtpreis zurückzugeben?
> Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.
Die brauche ich nirgendwo.
Gerhard schrieb:>> Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.>> Die brauche ich nirgendwo.
egal, jede Tabelle braucht einen Primärschlüssel.
Gerhard schrieb:> Ok, ich kannte keinen besseren Weg, diese 1:n-Beziehung darzustellen,> denn ein Staffelpreis sind ja mehrere Preise... Hm.
du hast jetzt aber keine 1:n Beziehung.
wie willst du denn für einen Eintrag 2 Preise anlegen? -> geht nicht
Gerhard schrieb:> Das ist mir klar, aber gibt es die Möglichkeit, in EINER Abfrage mit> Artikel-ID und gewünschter Stückzahl die günstigste zu bestellende> Anzahl und den Gesamtpreis zurückzugeben?
Aso
folgendes (Minimal-) Schema vorausgesetzt:
Table Bezugsquellen:
ID
Artikel_ID
(rest egal)
Table Preise:
ID
Bezugsquelle_ID
Stueckzahl
Einzelpreis
(rest egal)
Alle Bezugsquellen zu bestimmten Artikel(myArticle):
1
SELECT * FROM Bezugsquellen WHERE Artikel_ID = myArticle;
Alle Bezugsquellen und Preise zu bestimmten Artikel(myArticle)
1
SELECT *
2
FROM Bezugsquellen, Preise
3
WHERE Bezugsquellen.Artikel_ID = myArticle
4
AND Preise.Bezugsquelle_ID = Bezugsquellen.ID
5
;
Alle Preise zu diesen Bezugsquellen mit berechnetem Gesamtpreis zu
bestimmter Anzahl (myCount):
hier muss man natürlich scheuen, ob die Datenbank so eine Art binäre
MAX-Funktion (die das größere der beiden Argumente zurückgibt) oder den
ternären operator (a?b:c) unterstützt.
Ansonsten muss man schauen, wie man selbst Funktionen einbauen kann.
Aber ohne Garantie, hab ich mir jetzt so aus den Fingern gezogen, ohne
es getestet zu haben.
Hallo Vlad,
vielen Dank für deine Unterstützung. Ich habe meine Datenbankstruktur
nicht geändert, die Abfrage aber trotzdem hinbekommen. Momentan sieht es
so aus:
1
SELECT
2
supplier.name,
3
component_source.order_code,
4
IF (price.quantity > $qty, price.quantity, $qty) AS order_quantity,
5
price.price * IF (price.quantity > $qty, price.quantity, $qty)
6
AS price_total
7
FROM
8
component_source,
9
supplier,
10
price
11
WHERE
12
component_id = $partno AND
13
component_source.supplier_id = supplier.id AND
14
component_source.price_id = price.id
15
ORDER BY
16
price_total ASC
17
LIMIT
18
1;
Als Eingabe habe ich dann nur $partno=798 und $qty=20 und bekomme
folgende Ausgabe:
Damit bin ich erstmal zufrieden.
Was ich später noch implementieren muss, ist die Berücksichtigung von
Verpackungseinheiten, so dass beispielsweise auch Reels bestellt werden
können - die haben dann die gleiche Teilenummer, aber eine andere
Bestellbezeichnung.
Gerhard schrieb:> vielen Dank für deine Unterstützung. Ich habe meine Datenbankstruktur> nicht geändert, die Abfrage aber trotzdem hinbekommen. Momentan sieht es> so aus:
hm, ok.
Das was bei dir ID heißt ist scheinbar so was ähnliches, was ich
Bezugsquelle_ID genannt habe, dann habe ich deine Namen
misinterpretiert, was ein Hinweis darauf ist, dass diese ungünstig
gewählt sind.
Ich möchte nur nochmal betonen, dass jede Tabelle ein Primärschlüssel
haben sollte.
Üblicherweise wird dieser ID genannt und hat die Eigenschaften PRIMARY
KEY, (damit impliit: NOT NULL, UNIQUE) sowie AUTOINCREMENT (oder
vergleichbares des entsprechenden SQL-Dialektes)
deine component_source-Tabelle sollte bereits eine eindeutige spalte
haben (ID) somit ist das component_source.preis_id überflüssig.
price.id sollte sich auf die component_source.ID beziehen und nicht auf
ein seperates feld.
Das hat den Vorteil, das automatisch jede component_source eine
eindeutige nummer hat und du dich nicht selbst darum kümmern muss, dass
price_id eindeutig ist (es macht in meinen Augen keinen Sinn, dass
mehrere Artikel den gleichen Staffelpreise haben - sollte das doch Sinn
machen löst man das über Link-Tables)
momentan hast du die Aufgabe selbst eine Zahl zu finden, die weder in
prices.id noch in component_source.price_id existiert.
Fremdschlüssel sollten als solche Erkennbar sein.
dein price.id würde ich also price.component_source_id oder so nennen
Gerhard schrieb:> Die Tabelle price sieht so aus:> mysql> select * from price where id=2;> +----+----------+---------+> | id | quantity | price |> +----+----------+---------+> | 2 | 1 | 7.39000 |> | 2 | 25 | 5.90880 |> | 2 | 100 | 4.98540 |> | 2 | 250 | 4.80080 |> | 2 | 500 | 4.69000 |> | 2 | 1000 | 4.61615 |> +----+----------+---------+> 6 rows in set (0.00 sec)>> Damit bin ich erstmal zufrieden.schauder Das ist ein wirklich schlechtes Datenbankschema. Wie von Vlad
Tepesch schon geschrieben, gibt es keinen echten Primärschlüssel.
Lediglich die Kombination mehrerer Felder (z.B. id, quantity) ist
eindeutig und kann ggf. als Primärschlüssel dienen.
Das ganze ist aber viel zu fehlerträchtig. Es gibt hier überhaupt keine
Notwendigkeit, mit zusammengesetzten Primärschlüsseln zu arbeiten.
So, überzeugt.
price.id heißt ab sofort price.component_source_id und ist ein
Fremdschlüssel auf die Tabelle mit den Beschaffungsquellen, welche einen
eigenen Primärschlüssel bekommt.
Das ganze System muß noch erweitert werden, und dabei wird es ziemlich
kompliziert. So brauche ich noch eine separate Tabelle mit Teilenummern.
Einer Teilenummer kann ich verschiedene Artikel zuordnen, die mechanisch
und elektrisch äquivalent sind. So kann ich beispielsweise auch einen
alten AT90S2313 durch einen Attiny-Nachfolger ersetzen, der
abwärtskompatibel ist.
Dann wird es eine Tabelle "Baugruppen" geben, in denen Teilenummern über
ihren Bezeichner ("R109", "IC507") mit der Baugruppen-ID verknüpft sind.
Das soll dann ermöglichen, Kleinserien von bestimmten Platinen möglichst
wirtschaftlich zu planen. Ich kann dann dem Kunden ziemlich genaue
Angebote machen und vor allem auch erkennen, ab welchen Stückzahlen sich
signifikante Verbesserungen im Einzelpreis ergeben.
Ob ich später die Versandkosten und Mindestbestellmengen von einzelnen
Quellen erfasse, weiß ich noch nicht. Eine Optimierung wäre zwar
denkbar, aber sicherlich nicht einfach zu programmieren:
"Lohnt es sich, das Teil etwas preiswerter bei B zu kaufen, wenn dies
das einzige Teil ist, das bei B gekauft wird und x Euro Versandkosten
hinzukommen?"
"Lohnt es sich, das Teil etwas preiswerter bei B zu kaufen, wenn dies
das einzige Teil ist, das bei B gekauft wird und x Euro Versandkosten
hinzukommen?"
Schritt 1 (Ist nur eine Optimierung, Schritt 2 geht dann schneller):
alle Lieferanten, zu denen es keine Alternative gibt (weil sie ein Teil
haben, das es nirgends sonst gibt): Hier musst du bestellen.
Schritt 2: Für alle anderen Lieferanten: Probier alle Kombinationen
durch. Lieferant dabei, Lieferant nicht dabei... gibt
2^Anzahl_Lieferanten Kombinationen. Sollte mit Brute Force machbar sein
solange du nur ein paar Lieferanten hast (nicht viel mehr als 10, so um
die 20 wirds kritisch...)
Kombinationen wo du ein Teil nicht bekommst fallen raus.
Nimm die Kombination, wo die Summe für Porto+Teile-Kosten minimal ist.
Thread beobachten
Seitenaufteilung abschalten