DB Modelierung und Query Problem

Hallo,

ich bin dabei für eine Vortrag eine kleine Testdatenbank
zusammenzustellen. In einem Auftrag sollen Artikel (mit und ohne
Seriennummern) aufgeführt werden. Dazu habe ich folgende Datenstruktur
entworfen.

Es gibt Artikel, manche davon können zusätzliche Serienummern haben
(z.b. Bosch Bohrhammer)
Ein Auftrage hat mehrere Positionen, jede Position kann momentan nur ein
Artikel sein, ggfs mit S/N. Der Auftrag ist einem Käufer (Kontakt)
zugeordnet.


CREATE TABLE xx_artikel (
id int(10) unsigned NOT NULL auto_increment,
artnr varchar(20) NOT NULL,
kurzbezeichnung varchar(250) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='v1.00';
INSERT INTO xx_artikel (id, artnr, kurzbezeichnung) VALUES
(1, '0815', 'Zollstock'),
(2, '4711', 'Bosch Bohrhammer');

CREATE TABLE xx_auftraege (
id int(10) unsigned NOT NULL auto_increment,
kontakt_id int(11) NOT NULL,
aunr int(11) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='v1.00';
INSERT INTO xx_auftraege (id, kontakt_id, aunr) VALUES
(1, 1, 1000),
(2, 2, 1001);

CREATE TABLE xx_auftraege_positionen (
id int(10) unsigned NOT NULL auto_increment,
auftrag_id int(11) NOT NULL,
artikel_id int(11) NOT NULL,
seriennummer_id int(11) NOT NULL,
menge float NOT NULL,
vkpreis double NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='v1.00';
INSERT INTO xx_auftraege_positionen (id, auftrag_id, artikel_id,
seriennummer_id, menge, vkpreis) VALUES
(1, 1, 1, 0, 2, 1),
(2, 1, 2, 1, 1, 150);

CREATE TABLE xx_kontakte (
id int(10) unsigned NOT NULL auto_increment,
vorname varchar(30) NOT NULL,
nachname varchar(30) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='v1.00';
INSERT INTO xx_kontakte (id, vorname, nachname) VALUES
(1, 'Hans', 'Musterman'),
(2, 'Theo', 'Tester');

CREATE TABLE xx_seriennummern (
id int(10) unsigned NOT NULL auto_increment,
artikel_id int(11) NOT NULL,
seriennummer varchar(32) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='v1.00';
INSERT INTO xx_seriennummern (id, artikel_id, seriennummer) VALUES
(1, 2, '0012345'),
(2, 2, '987600');

- Problematisch finde ich momentan die zweistufige Artikelzuordnung in
den Positionen.
mal mit Seriennummern_id (wenn s/N vorhanden) oder ohne (falls der
Artikel keine SN hat, z.b Zollstock)
- man muß die S/N Artikel für jede SN einzeln aufführen

Gibt es da schon was zu verbessern?

Wenn ich mir jetzt anzeigen lassen will, was von einem Bohrhammer schon
verkauft wurde

select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis
from xx_seriennummern s
left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id
, xx_artikel a
where a.id = 2

4711, Bosch Bohrhammer, 0012345, 150
4711, Bosch Bohrhammer, 987600, 0

wie komme ich aber in der query an den Käufers (kontakt_id)
über die gejointen auftragspositionen geht es nicht

select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis,
k.vorname, k.nachname
from xx_seriennummern s
left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id
, xx_artikel a, xx_auftraege au, xx_kontakte k
where a.id = 2
and ap.auftrag_id = au.id
and au.kontakt_id = k.id

4711, Bosch Bohrhammer, 0012345, 150, Hans, Mustermann

zeigt mit alle verkauften artikel, aber nicht die unverkauften.

Tipps für mich?

thanks
stefan

Re: DB Modelierung und Query Problem

Stefan Kilp wrote:
> ich bin dabei für eine Vortrag eine kleine Testdatenbank
> zusammenzustellen.

Was für ein Vortrag? Was willst Du zeigen? SQL? Relationale Datenbanken?
3. Normalform?

> In einem Auftrag sollen Artikel (mit und ohne
> Seriennummern) aufgeführt werden. Dazu habe ich folgende Datenstruktur
> entworfen.
[...]
> - Problematisch finde ich momentan die zweistufige Artikelzuordnung in
> den Positionen.
> mal mit Seriennummern_id (wenn s/N vorhanden) oder ohne (falls der
> Artikel keine SN hat, z.b Zollstock)
> - man muß die S/N Artikel für jede SN einzeln aufführen
>
> Gibt es da schon was zu verbessern?

Was Du Auftrag nennst, enthält doch auch durchgeführte Aufträge, oder?
Wenn sich mal Kundendaten oder Artikeldaten ändern, dann ändert sich
auch nachträglich der Auftrag.

Darum solltest Du Dir überlegen an dieser Stelle die Daten aus
xx_artikel und xx_kontakte zu kopieren.

Eventuell könnten die Fremdschlüssel zu den Kontakten und den Artikel
optional sein. Wenn ein Artikel aus dem Sortiment genommen wird, wurde
der Auftrag ja trotzdem mal durchgeführt.

> Wenn ich mir jetzt anzeigen lassen will, was von einem Bohrhammer schon
> verkauft wurde

Meinst Du nun das Modell oder das Gerät?

xx_artikel ist doch die Liste mit den Modellen. Dies sind keine
konkreten Artikel die Ihr auf Lager habt.

Die Tabelle xx_seriennummern würde ich streichen. Für vorhandenes
Material eine eigene Inventarverwaltung vorsehen. Die kann gerne bei
größeren Artikeln diese einzeln mit Seriennummer listen und bei
kleineren nur die Menge. Wird vermutlich mehr als eine Tabelle.

Die Seriennummern werden dann als String aus der Inventarverwaltung in
die Auftragsposition kopiert.

Dies ist so nicht ganz 3NF, aber es ist pragmatischer. Es besteht die
Möglichkeit von Redundanz und damit von Fehlern. Das selbe Problem
besteht bei Deinem aktuellen Ansatz. Die Position selbst könnte auf
einen anderen Artikel verweisen als die benutzte Seriennummer.

> select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis
> from xx_seriennummern s

inner join xx_artikel as a on ...

> left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id

streichen: > , xx_artikel a

left join xx_auftraege as auf on ...


> where a.id = 2
>
> 4711, Bosch Bohrhammer, 0012345, 150
> 4711, Bosch Bohrhammer, 987600, 0
>
> wie komme ich aber in der query an den Käufers (kontakt_id)
> über die gejointen auftragspositionen geht es nicht

siehe oben

> select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis,
> k.vorname, k.nachname
> from xx_seriennummern s
> left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id
> , xx_artikel a, xx_auftraege au, xx_kontakte k
> where a.id = 2
> and ap.auftrag_id = au.id
> and au.kontakt_id = k.id
>
> 4711, Bosch Bohrhammer, 0012345, 150, Hans, Mustermann
>
> zeigt mit alle verkauften artikel, aber nicht die unverkauften.

Ja, weil das kein left join ist.

Versuche Deine SQL-Ausdrücke ohne Kreuzprodukte zu schreiben. Verwende
lieber nur Joins. Ich glaube es wird leichter zu lesen, zu durchschauen
und eher zum Ziel führen.

Heiko
--
http://portal.richler.de/ Namensportal zu Richler
http://www.richler.de/ Heiko Richler: Computer - Know How!
http://www.richler.info/ private Homepage

Re: DB Modelierung und Query Problem

Heiko Richler schrieb:
> Stefan Kilp wrote:
>
>>ich bin dabei für eine Vortrag eine kleine Testdatenbank
>>zusammenzustellen.
>
>
> Was für ein Vortrag? Was willst Du zeigen? SQL? Relationale Datenbanken?
> 3. Normalform?
>
Datenbankmodellierung (Praxis <-> Modell), welche Auswirkung die
Modellierung hat.
>
>>In einem Auftrag sollen Artikel (mit und ohne
>>Seriennummern) aufgeführt werden. Dazu habe ich folgende Datenstruktur
>>entworfen.
>
> [...]
>
>>- Problematisch finde ich momentan die zweistufige Artikelzuordnung in
>>den Positionen.
>>mal mit Seriennummern_id (wenn s/N vorhanden) oder ohne (falls der
>>Artikel keine SN hat, z.b Zollstock)
>>- man muß die S/N Artikel für jede SN einzeln aufführen
>>
>>Gibt es da schon was zu verbessern?
>
>
> Was Du Auftrag nennst, enthält doch auch durchgeführte Aufträge, oder?
> Wenn sich mal Kundendaten oder Artikeldaten ändern, dann ändert sich
> auch nachträglich der Auftrag.
>
> Darum solltest Du Dir überlegen an dieser Stelle die Daten aus
> xx_artikel und xx_kontakte zu kopieren.
das ist richtig, um die Daten "einzufrieren". Im Model habe ich es
weggelassen, weil es sonst zu vile wird.
>
> Eventuell könnten die Fremdschlüssel zu den Kontakten und den Artikel
> optional sein. Wenn ein Artikel aus dem Sortiment genommen wird, wurde
> der Auftrag ja trotzdem mal durchgeführt.
ACK
>
>
>>Wenn ich mir jetzt anzeigen lassen will, was von einem Bohrhammer schon
>>verkauft wurde
>
>
> Meinst Du nun das Modell oder das Gerät?
Artikel beschreibt den Gegenstand (Bohrhammer, Laserdrucker, ...) das
spezielle Gerät wird über die Serienummer identifiziert.
Mich hat es aus sich des Gegansandes (Artikel) interessiert
>
> xx_artikel ist doch die Liste mit den Modellen. Dies sind keine
> konkreten Artikel die Ihr auf Lager habt.
doch:
in Artikel sind z.B.
HP LaserJet 4L
HP LaserJet 5P
usw.
alle dann mit Serienummern

>
> Die Tabelle xx_seriennummern würde ich streichen. Für vorhandenes
> Material eine eigene Inventarverwaltung vorsehen. Die kann gerne bei
> größeren Artikeln diese einzeln mit Seriennummer listen und bei
> kleineren nur die Menge. Wird vermutlich mehr als eine Tabelle.

Inventar gibts auch noch, hat aber damit nix zu tun. Da die
Inventarliste auch Dinge enthalten wird (Bürostuhl, Frankiermaschine,
....) die nie in die Artikel aufgenommen werden,
>
> Die Seriennummern werden dann als String aus der Inventarverwaltung in
> die Auftragsposition kopiert.
Nein, soll in Tabelle bleiben, da die Geräte ggfs auch verliehen werden
können. Und ich dort einen eindeutigen Verweis auf das spezielle Gerät
haben will.
>
> Dies ist so nicht ganz 3NF, aber es ist pragmatischer. Es besteht die
> Möglichkeit von Redundanz und damit von Fehlern. Das selbe Problem
> besteht bei Deinem aktuellen Ansatz. Die Position selbst könnte auf
> einen anderen Artikel verweisen als die benutzte Seriennummer.
Das ist richtig. Die Lösung mit der seriennummer_id hat zusätzlich noch
den Nachteil, nur genau ein Gerät pro Auftragsposition zuzuordnen zu
können. Das wird in eine N:M Tabelle mit (Auftragspostition /
Seriennummer) geändert.
>
>
>>select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis
>>from xx_seriennummern s
>
>
> inner join xx_artikel as a on ...
>
>
>> left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id
>
>
> streichen: > , xx_artikel a
>
> left join xx_auftraege as auf on ...
>
>
>
>>where a.id = 2
>>
>>4711, Bosch Bohrhammer, 0012345, 150
>>4711, Bosch Bohrhammer, 987600, 0
>>
>>wie komme ich aber in der query an den Käufers (kontakt_id)
>>über die gejointen auftragspositionen geht es nicht
>
>
> siehe oben
>
>
>>select a.artnr, a.kurzbezeichnung, s.seriennummer, ap.vkpreis,
>>k.vorname, k.nachname
>>from xx_seriennummern s
>> left join xx_auftraege_positionen ap on ap.seriennummer_id = s.id
>> , xx_artikel a, xx_auftraege au, xx_kontakte k
>>where a.id = 2
>>and ap.auftrag_id = au.id
>>and au.kontakt_id = k.id
>>
>>4711, Bosch Bohrhammer, 0012345, 150, Hans, Mustermann
>>
>>zeigt mit alle verkauften artikel, aber nicht die unverkauften.
>
>
> Ja, weil das kein left join ist.
>
> Versuche Deine SQL-Ausdrücke ohne Kreuzprodukte zu schreiben. Verwende
> lieber nur Joins. Ich glaube es wird leichter zu lesen, zu durchschauen
> und eher zum Ziel führen.
>
> Heiko

danke, das ist ein guter Hinweis. Das stelle ich um.


Vielen Dank für die ausführlichen Kommentare.

gruss
stefan