Datenbankmodell so richtig ?

[Liistefano]

Hallo

Möchte gern eine Datenbank für den Verein in dem ich Mitglied bin erstellen. Die Datenbank soll für das Lager dienen. Hatte mir im Kopf jetzt ausgedacht eine Tabelle wo alle Artikel die im Lager sind reinkommen und dann noch einzelne Tabellen für die Sachen die was auf dem Lager brauchen.

 

[ukulele]

Was für "Sachen" werden denn gelagert und welche Sachen brauchen was auf dem Lager

 

[Liistefano]

Hab hier mal die sql Datei mit der Datenbank hochgeladen vielleicht wirds dann etwas verständlicher.

 

[Tommi]

Hi,

ich würde hier raten, erst einmal zu prüfen, ob es nicht bereits eine verwendbare Freeware zu einer Lagerverwaltung gibt, die bereits die gewünschten Funktionen abdeckt.
Hier ist mal eine Auswahl:
http://www.freeware.de/programme/lagerverwaltung/

Es ist zwar immer gut, wenn man sich auf neues Gebiet wagt und selber etwas produzieren möchte, aber oftmals passen hier Zeitaufwand von anzueignendem Wissen und Implementierung und dem gewünschten Fertigstellungs-Termin nicht zusammen. Ein Zurückgreifen auf eine bereits existierende Lösung, vielleicht auch nur als Zwischenlösung, bis eine passende Eigenentwicklung einsatzbereit ist, ist oftmals sinnvoller.


Viele Grüße,
Tommi

 

[ukulele]

Ich versteh auch die Logik in deiner SQL Datei nicht. Du hast eine Artikeltabelle, soweit richtig. Dann hast du aber jede Menge andere Tabellen für irgendwelche Medikits etc. die aber immer gleich aufgebaut sind.

CREATE TABLE IF NOT EXISTS `intensivrucksack` (
  `FachNr` int(10) DEFAULT NULL,
  `FachName` char(20) DEFAULT NULL,
  `MaterialNr` int(10) DEFAULT NULL,
  `Dosierung` char(100) DEFAULT NULL,
  `Soll` int(10) DEFAULT NULL,
  `Ist` int(10) DEFAULT NULL,
  `Ablaufdatum` date DEFAULT NULL

Darin sind die Spalten Lagerfach und Lagernummer enthalten, das klingt schonmal nach Redundanz. Abgesehen davon scheint es sinnvoll, eine Tabelle anstatt viele mit gleichen Spalten anzulegen. Außerdem mangelt es noch an einer Zuordnungstabelle in der steht, welcher Artikel in welchem Fach liegt oder in welcher "Zusammenstellung" Medikit, Intensivrucksack, etc. steckt. Ich glaube du solltest dich erstmal mit der Normalisierung auseinander setzen und vieleicht ein ERD Zeichnen.

 

[Liistefano]

Wir haben schon derzeit eine Excel-Liste im Einsatz zur Lagerverwaltung, nun wollte ich aber halt auf SQL umsteigen und dann mit Sendmail in bestimmten Intervallen Mails verschicken in der steht welche Artikel fehlen und ablaufen bzw schon abgelaufen sind.

 

[akretschmer]

Wir haben schon derzeit eine Excel-Liste im Einsatz zur Lagerverwaltung, nun wollte ich aber halt auf SQL umsteigen und dann mit Sendmail in bestimmten Intervallen Mails verschicken in der steht welche Artikel fehlen und ablaufen bzw schon abgelaufen sind.

Genau. Aber dafür hast so wie es jetzt ist ein schlechtes Modell. Korrekt wäre doch eher etwas wie, daß erst mal alle Artikel erfaßt werden. Dann wird der Bestand erfaßt, er verweist auf Artikel und enthält eine Chargennummer und den Bestand. Weiter definierst Du Deine Rucksäcke etc, wie die heißen. Eine weitere Zuordnung dann wo die liegen, dazu einer Lagerplatztabelle, und eine Tabelle, in welchen Rucksack welche Chargen von Deinem Bestand sind, welche Mindestmengen drin sein müssen und welche Mengen aktuell drin sind. Wenn das alles sauber strukturiert ist kann Du mit wenig SQL (vielleicht via VIEW) sehen, in welchen Rucksäcken, die in dem und dem Lagerfach liegen, Artikel mit einer Chargennummer drin sind, die auszutauschen sind. Du kannst auch immer eine aktuelle Inventurliste generieren und abfragen, welche Mengen von welchen Artikel abgelaufen oder wo das in 3 Tagen oder so passiert. Wenn die Rucksäcke aufgefüllt werden, kannst Dir auch direkt anzeigen lassen, welche Chargen noch im Vorratsbestand sind und zuerst verwendet werden sollten etc.

Du verwendest InnoDB, das ist schon mal ein Pluspunkt. Richtig gut wird es, wenn Du auch noch referrentielle Integritätsbedingungen definieren würdest. Momentan hast aber einige Fehler im Modell:

• FachNummer / Fachname ist redundant
• Dosierung gehört in den Artikelstamm oder in die Stammdaten des Rucksackes, so wie es jetzt ist kannst Du für ein und dasselbe Produkt in 2 Rücksäcken derselben Kategorie unterschiedlichste Dosierungen haben. Das ist ganz sicher FAIL.
• verwende BITTE kein Datum '0000-00-00', das ist MySQL-Bullshit. Wenn ein Datum nicht bekannt ist, ist es NULL.

Einzel-Tabellen für Sanbox1 und Sanbox2 sind aber ganz eindeutig falsches Tabellendesign!

Andreas

 

[akretschmer]

...
Andreas

Um mal zu zeigen, wie es gehen könnte. Vorsicht:

• das ist unvollständig, nicht alle Deine Werte sind bei mir vorhanden, z.B. das mit der Dosierung
• ich verwende PostgreSQL, da sind für MySQL einige Datentypen anzupassen, z.B. SERIAL
• in MySQL kannst die CHECK-Constraints auch weglassen. MySQL versteht zwar die Syntax, ist aber zu *****, es zu prüfen...

-- hier definierst Du alle Artikel
create table artikel (
  id serial primary key,
  bezeichnung text
);


-- hier die Grundtypen der Sets wie sanbox, sanrucksatz, ...
create table set_typen (
  id serial primary key,
  bezeichnung text
);


-- hier die konkreten Sets, also sanbox1, sanbox2, ...
create table sets (
  id serial primary key,
  bezeichnung text,
  set_typ int references set_typen
);


-- hier, was drin sein muß
create table set_musthave (
  id serial primary key,
  set int references sets,
  artikel int references artikel,
  menge int
);
 
-- hier die Chargen
create table chargen (
  id serial primary key,
  artikel int references artikel,
  chargen_nummer text,
  haltbar_bis date,
  kaufdatum date,
  kaufmenge int,
  bestand int,
  check (bestand <= kaufmenge),
  check (haltbar_bis >= kaufdatum)
);


-- nun endlich, was *wirklich* in z.B. Rucksack1 drin ist
create table sets_aktueller_inhalt (
  id serial primary key,
  set int references sets,
  charge int references chargen,
  anzahl int
);

Nun Testdaten und eine Abfrage nach abgelaufenen Chargen:

insert into artikel values (1, 'artikel 1');
insert into chargen values (1, 1, 'charge1','2013-08-31','2013-02-01',20,20);
insert into chargen values (2, 1, 'charge2 (alt!)','2012-12-31','2012-01-01',20,10);

insert into set_typen values (1,'Rucksack Typ 1');
insert into sets values (1,'Rucksack Typ 1, Nummer 1',1);


insert into set_musthave values (1,1,1,5);

insert into sets_aktueller_inhalt values (1, 1, 1, 2);
insert into sets_aktueller_inhalt values (2, 1, 2, 2);


select
  *,
  case when c.haltbar_bis < current_date then 'verfallen' else 'okay' end as haltbar
from sets_aktueller_inhalt i left join chargen c on (i.charge=c.id);

Führe ich das aus, bekomme ich:

test=# \i struktur.sql 
psql:struktur.sql:5: NOTICE:  CREATE TABLE will create implicit sequence "artikel_id_seq" for serial column "artikel.id"
psql:struktur.sql:5: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "artikel_pkey" for table "artikel"  
CREATE TABLE                                                                                                            
Time: 10,944 ms                                                                                                         
psql:struktur.sql:12: NOTICE:  CREATE TABLE will create implicit sequence "set_typen_id_seq" for serial column "set_typen.id"
psql:struktur.sql:12: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "set_typen_pkey" for table "set_typen"  
CREATE TABLE                                                                                                                 
Time: 7,197 ms
psql:struktur.sql:20: NOTICE:  CREATE TABLE will create implicit sequence "sets_id_seq" for serial column "sets.id"
psql:struktur.sql:20: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sets_pkey" for table "sets"
CREATE TABLE
Time: 17,517 ms
psql:struktur.sql:29: NOTICE:  CREATE TABLE will create implicit sequence "set_musthave_id_seq" for serial column "set_musthave.id"
psql:struktur.sql:29: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "set_musthave_pkey" for table "set_musthave"
CREATE TABLE
Time: 3,714 ms
psql:struktur.sql:43: NOTICE:  CREATE TABLE will create implicit sequence "chargen_id_seq" for serial column "chargen.id"
psql:struktur.sql:43: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "chargen_pkey" for table "chargen"
CREATE TABLE
Time: 13,858 ms
psql:struktur.sql:52: NOTICE:  CREATE TABLE will create implicit sequence "sets_aktueller_inhalt_id_seq" for serial column "sets_aktueller_inhalt.id"
psql:struktur.sql:52: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sets_aktueller_inhalt_pkey" for table "sets_aktueller_inhalt"
CREATE TABLE
Time: 5,451 ms
INSERT 0 1
Time: 0,235 ms
INSERT 0 1
Time: 0,426 ms
INSERT 0 1
Time: 0,169 ms
INSERT 0 1
Time: 0,188 ms
INSERT 0 1
Time: 0,356 ms
INSERT 0 1
Time: 0,468 ms
INSERT 0 1
Time: 0,508 ms
INSERT 0 1
Time: 0,210 ms
 id | set | charge | anzahl | id | artikel | chargen_nummer | haltbar_bis | kaufdatum  | kaufmenge | bestand |  haltbar
----+-----+--------+--------+----+---------+----------------+-------------+------------+-----------+---------+-----------
  1 |   1 |      1 |      2 |  1 |       1 | charge1        | 2013-08-31  | 2013-02-01 |        20 |      20 | okay
  2 |   1 |      2 |      2 |  2 |       1 | charge2 (alt!) | 2012-12-31  | 2012-01-01 |        20 |      10 | verfallen
(2 rows)

Time: 0,352 ms

Das ist natürlich vollständig unvollständig, aber sollte zeigen, was bei einem halbwegs geplanten Tabellendesign machbar ist.

Andreas