[Erledigt] Fortlaufende Auftragsnummern je Kalenderjahr

Hallo zusammen,

ich stehe vor folgenden Problem: Ich möchte eine Auftragsnummer in folgender Form generieren:

10-100001 (10 = Jahr, 100001 = 6-sechsstellige fortlaufende Nummer)

Nach jedem Jahr soll die fortlaufende Nummer zurückgesetzt werden, sprich 11-100000, 12-100000 usw.

Nun könnte ich ein auto-increment Feld anlegen mit dem Startwert 10100000 und per PHP das Minuszeichen einfügen. Zum Jahreswechsel müsste ich den auto-increment Wert natürlich anpassen.

Gibt es eine gute Möglichkeit, dies automatisch erledigen zu lassen? Mir kommt im Moment nur die Idee, eine zusätzliche Abfrage zu machen und dann die ersten zwei Zeichen mit der PHP time() Funktion zu vergleichen. Aber irgendwie erscheint mir das alles unnötig und kompliziert.

Kann man das ganze evtl. per Cronjob lösen? (bspw.: ALTER TABLE orders AUTO_INCREMENT = 11100000/1200000 usw.

[nikosch]

Die Antwort ist simpel: speichere die Teile der Auftragsnummer getrennt in der DB.

[ChrisB]

Man kann zwar bei Verwendung von MyISAM als Engine auto_increment für eine Spalte eines mehrspaltigen Keys angeben (siehe MySQL :: MySQL 5.1 Reference Manual :: 3.6.9 Using AUTO_INCREMENT; weiß nicht, ob du dich darauf bezogst, nikosch?)

Allerdings gibt es da einen Grund, der massiv dagegen spricht:

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group.

Dieses Verhalten möchte man bei so sensiblen Geschäftsdaten wie Auftragsnummern ziemlich sicher nicht haben.


Wenn die Anforderung also unbedingt bestehen bleiben soll (wofür sollen Auftragsnummern fortlaufend sein müssen?), dann sollte das eher mit eigener Logik umgesetzt werden (beim Einfügen eines neuen Datensatzes höchste Nummer auslesen und um eins erhöhen - was selbstverständlich in eine Transaktion gekapselt werden sollte), ggf. auch als Stored Procedure o.ä.

[nikosch]

Nö, ich rede davon

Auftrag
AuftrNrPfx (year) | AuftrNrLfd (int auto_inc) | ...

PRIMARY KEY (AuftrNrPfx , AuftrNrLfd)

[edit]
Oh, das meinte ich wohl doch. Nunja, man könnte argumentieren, Auftragsdaten, Rechnungsnummern etc. darf man ohnehin nicht löschen, sondern muss sie rückbuchen o.ä.

[Trainmaster]

Diese Lösung scheint für mein Anliegen geeignet zu sein. Allerdings ignoriert MySQL meinen auto-increment Startwert. Selbst wenn ich in PMA direkt den Befehl "ALTER TABLE orders AUTO_INCREMENT = 100000;" ausführe und auch bei den Tabellenoptionen eine auto-increment Wert von 100000 angezeigt wird, zählt es trotzdem mit 1, 2, 3 ... hoch. Das verstehe ich nicht.

Die Problematik, welche ChrisB anspricht, meint doch, im Falle das man die höchste Auftragsnummer im Jahr 10 löscht, die Nummerierung der Auftragsnummer im Jahr 10 wieder zurückgesetzt wird?! In der Tat wäre das nicht schön, aber ich sehe es einfach so, dass Aufträge sensible Daten sind, die nicht gelöscht werden. Ich hätte einfach ein zusätzliches Feld für "storniert" bereitgestellt.

Und ehrlich gesagt sehe ich kein Argument, dass für NICHT-fortlaufende Auftragsnummern spricht. Fortlaufende Nummer finde ich in diesem Fall einfacher praktischer. Bspw. kann die Anzahl der Aufträge in einem Jahr oder Stornierungen in Sekundenschnelle abgelesen werden.

Ohne mich jetzt in Stored Procedures eingelesen zu haben, wie kann ich das verstehen? Dass man ein SELECT und INSERT Befehl in einer Abfrage ausführen kann?

[thomas_w]

Direkt mit einem Befehl geht es nicht. Zudem muß der ganzen Vorgang in einer Transaktion sein.

Als Diskussionsgrundlage mal dies (MYSQL 5.1.44):

CREATE TABLE daten (
 id INT NOT NULL,
 feld INT NOT NULL
);

CREATE TABLE nummer (
 id INT NOT NULL
);

-- Startwert der laufenden Nr.
INSERT INTO nummer VALUES ( 0 );

-- Beginn Transaktion 

INSERT INTO daten VALUES
( 
  (SELECT id + 1 FROM nummer),
  20
);

UPDATE nummer
   SET id = id +1;

COMMIT;

-- Ende Transaktion 

mysql> select * from daten;
+----+------+
| id | feld |
+----+------+
|  1 |   20 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from nummer;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql>

Grüße
Thomas

[nikosch]

Kleiner Tipp am Rande: Bei Rechnungsnummern gibt es ja das Gebot, dass sie fortlaufend sein müssen. Ich weiß nicht, ob das bei Auftragsnummern auch so sein muss. Ich denke zwar, dass nicht, aber: In diesem Fall ist Dein Plan vermutlich nicht geeignet. Numerierungen dieses Prinzips sind nachträglich manipulierbar.

[Trainmaster]

Ich denke nicht, dass Auftragsnummern fortlaufend sein müssen. Aber aus oben genannten Gründen möchte ich gerne die Auftragsnummern in dieser Weise generieren. Was meinst du damit, dass Numerierungen nachträglich manipulierbar sind?

[nikosch]

Rechnungsnummern müssen fortlaufend sein, damit nach dem Jahresabschluss nicht nachträglich Sachen beim FA geltend gemacht werden können:

20090001
...
20090547
20100001
...

> 20090548, 49..

[thomas_w]

Rechnungsnummern müssen fortlaufend sein, damit nach dem Jahresabschluss nicht nachträglich Sachen beim FA geltend gemacht werden können:

20090001
...
20090547
20100001
...

> 20090548, 49..

Aufgrund der Jahresabgrenzungsregeln darf bis 31.03.xxxx (Folgejahr) noch Rechnungen auf den 31.12.xxxx (Vorjahr) erzeugt werden. Folglich dürfen und müssen nachträglich Rechnungsnummern eingefügt werden können. Daher gibt es zeitweise zwei laufende Rechnungsnummer.

Aber nichts desto trotz ging es oben um eine unkritische Auftragsnummer und die muss weder fortlaufend, noch lückenlos, noch numerisch sein (siehe Amazon).

Grüße
Thomas

[Trainmaster]

Ich habe es folgendermaßen gedacht:

10-100001
10-100002
...
10-101349

11-100001
11-100002
...
11-103415

Das sollte in Ordnung gehen!?

Irgendwie sehe ich keinen optimalen Lösungweg. Der mehrspaltige Primärschlüssel geht zwar in die richtige Richtung, hat aber das Problem, dass die höchsten Werte, sofern sie gelöscht werden, wieder aufs Neue befüllt werden (Widerspricht im Grunde der Idee des auto-increment). Nun gut, dann dürfte ich einfach keine Datensätze löschen. Jedoch wird ein individueller Startwert für auto-increment ignoriert. Schade

Allerdings habe ich mir einen ziemlich "dirty" Workaround überlegt: Ich setz einfach für, sagen wir mal 10 Jahre im Voraus, für jedes Jahr einen Wert, bspw.:

10 - 99999
11 - 99999
12 - 99999
usw.

Somit würde der zweispaltige Primärschlüssel bei jedem Jahreswechsel mit dem von mir gewünschten Wert von 100000 beginnen.

Update:
Den dirty Workaround lass ich wohl bleiben. Stattdessen werde ich nur ein Feld erstellen und dort bspw. folgende fortlaufende Nummer eintragen: 10100000. Mit PHP füge ich dann das Minuszeichen ein. Zum Jahreswechsel muss ich den auto-increment Wert manuell zurücksetzen, aber das kann man evtl. mit einem zeitgesteuertem Skript erledigen?! Das ganze hätte zugleich den Vorteil, dass ich mit lastInsertId() die komplette Auftragsnummer für die Auftragsbestätigunsemail bekomme, welche nach Eintrag in die Datenbank versandt wird. Ich denke das ist der beste Weg.

[nikosch]

DHast Recht. "Fortlaufende Rechnungsnummer"? Eine einmalig vergebene Nummer genügt! - Online lernen bei akademie.de

[Trainmaster]

Abschließend möchte ich noch meine Lösung vorstellen:

$db = Database::getInstance()->getConnection(); 
$db->beginTransaction();
$db->exec("INSERT INTO orders (order_id) VALUES (CONCAT((SELECT increment_prefix FROM orders_settings), (SELECT increment_value FROM orders_settings)))");
$order_id = $db->query("SELECT order_id FROM orders WHERE id = LAST_INSERT_ID()")->fetchColumn();
$db->exec("UPDATE orders_settings SET increment_value = increment_value + 1");
$db->commit(); 

In einer zusätzlichen Tabelle "order_settings" habe ich einen Präfix definiert (stellv. für das Jahr) und einen increment-Wert. Damit kann ich bequem zum Jahreswechsel per Cronjob den Präfix und increment-Wert anpassen. Der INSERT Befehl ist wohl selbsterklärend. Und damit ich abschließend dem Kunden die vergebene Auftragsnummer mitsenden kann, frage ich die order_id entsprechend zur last_insert_id() ab. Funktioniert einwandfrei. Danke nochmals für die Denkanstösse.