MySQL - Datenbankdesign

[schoby24]

Hallo,

ich muss für die Schule ein Kunden Management System in PHP realisieren und speichere zurzeit noch alles in einer einzigen Tabelle.

Spalten:
kundenNr
vorname
nachname
email
strasse
plz
stadt
land
und weitere Information zu gekauften Produkten bzw. Verträgen
...

die gekauften Produkte in einer neuen Tabelle auszulagern ist keine Problem, jedoch sind von den Kunden monatliche Gebühren zu bezahlen,
jetzt ist dies so realisiert, dass bei jedem Datensatz in der Tabelle jeweils in der Spalte für das bezahlte Monat der Betrag steht

01_06 - 24,90
02_06 - 24,90
03_06 - 24,90
04_06
05_06
06_06
07_06
08_06
09_06
10_06
11_06
12_06

also Redundanz pur!

würde ich eine Tabelle Bezahlungen2006 anlegen, muss ich ja erst wieder für
jedes Monat eine Spalte anlegen, und darin den betrag speichern, den der Kunden bereits bezahlt hat.

kundenNr 01_06 02_06

234234 24,90 24,90



Was meint ihr?

mfg

[Turbohummel]

Den Betrag kannst du doch einfach in die Kundentabelle reinstecken, oder?

[mwanaheri]

Du denkst noch in Kategorien von Tabellenkalkulationen. Datenbanken ticken anders.
In die Tabelle 'Kunden' gehören nur die minimalen Angaben zum Kunden hinein, also die Kontakt-Informationen. Alles, was mehrfach vorhanden sein kann, gehört ausgelagert und mit Fremdschlüssel versehen:
Kunde:
Kennung (PK), vorname, nachname, email, Land(FK!), PLZ (FK!), Strasse.
Land:
Kennung (PK), Land.
PLZ:
Land (PK,FK), PLZ (PK), Stadt.

Nach dem gleichen Prinzip verfährst du für Produkte. Du kannst dir noch überlegen, ob ein Monatsbeitrag nicht auch ein Produkt ist.

Geschäftsvorfälle werden einzeln erfasst, z.B.
Kennung, kunde (FK), Produkt (fk), Menge, Einzelpreis, Datum.

Rechnung:
Kennung, Kauf(fk), Betrag, bezahlt, Zahldatum.

so ist das nur mal auf die Schnelle zusammengehackt. Ich denke du siehst, worum es geht.

[schoby24]

Den Betrag kannst du doch einfach in die Kundentabelle reinstecken, oder?

Der Betrag ist nicht das Problem, jedoch muss ich für jeden Kunden angeben können, ob er er für das Monat xy bezahlt hat





Kunde:
Kennung (PK), vorname, nachname, email, Land(FK!), PLZ (FK!), Strasse.
Land:
Kennung (PK), Land.
PLZ:
Land (PK,FK), PLZ (PK), Stadt.

OK, das sinnvolle aufteilen der daten hab ich verstanden, aber wie kann ich diese aufteilungen auch bei den monatlichen bezahlungen durchführen.


Wenn ich den Gesamtbetrag als Produkt speichere hilft mir das nicht weiter

THX

mfg

[michael.sprick]

Das machst Du ebenfalls über eine seperate Tabelle...

z.B.


Tabelle: Zahlungen
+------------+---------+--------+
| KundenId | Monat | Jahr |
+------------+---------+--------+
| 1| 1| 6|
+------------+---------+--------+
| 2| 1| 6|
+------------+---------+--------+
| 1| 2| 6|
+------------+---------+--------+
| 2| 2| 6|
+------------+---------+--------+
| 1| 3| 6|
+------------+---------+--------+


Du kannst die beiden Spalten (Monat/Jahr) optimalerweise gegen EINE Spalte vom Typ date austauschen... Auf keinen Fall jedoch für jeden Monat eine Spalte oder sogar für jedes Jahr eine eigene tabelle...

[schoby24]

ok,

aber das würde dann immer nur für eine zahlung funktionieren oder?


Tabelle: Zahlungen
+------------+---------+--------+
| KundenId | Monat | Jahr |
+------------+---------+--------+
| 1| 1| 6|
+------------+---------+--------+
| 2| 1| 6|
+------------+---------+--------+
| 1| 2| 6|
+------------+---------+--------+
| 2| 2| 6|
+------------+---------+--------+
| 1| 3| 6|
+------------+---------+--------+

ich muss aber für jedes Monat angeben können, ob ein Kunde bezahlt hat oder nicht

mdf

[mwanaheri]

Das kannst du nachprüfen mit:
Kundenid(pk),Jahr(pk),Monat(pk),Beitragshöhe,bezah lt.
Hier solltest du Beitragshöhe als numeric nehmen und bezahlt als Datum.
Vorgabe für Bezahlt ist null, du kannst also mit 'is null' unbezahlte Monatsbeiträge abfragen. Für eine kleine Lösung reicht aber auch bool. Für eine ernsthafte Lösung wäre auch für den Monatsbeitrag eine Rechnung zu stellen, die dann in der Rechnungstabelle auftaucht.

[michael.sprick]

ich muss aber für jedes Monat angeben können, ob ein Kunde bezahlt hat oder nicht

kannst Du doch...
Wenn ein Kunde bezahlt hat, bekommt er einen Eintrag in der Tabelle. Wenn er nicht gezahlt hat, hat er keinen Eintrag.

In der Beispieltabelle siehst Du ja, dass Kunde 1 für 01/06 , 02/06 und 03/06 bezahlt hat. Kunde 2 hingegen nur für 01/06 und 02/06.

Der Betrag, der gezahlt wird, darf nur dann in diese Tabelle, wenn er sicht ständig verändert.
Wenn er pro Kunde festgelegt ist, kommt er evtl. als Eigenschaft der Kundentabelle in Frage.
Vermutlich ist es am Besten, wenn Du eine extra Tabelle "Gebuehren" machst oder aus dem Monatsbeitrag ein Produkt machst...

[kehj]

Hallo,

für die Zahlungen des Kunden bietet sich doch auch eine eigene Tabelle an. Etwas der Art:

zahlungen = ((id, id_kunde, id_rechnung, eingangssdatum, betrag), (id))
Fremdschlüssel: id_kunde, id_rechnung.

Damit stellst du pro Monat eine Rechnung (id_rechnung) mit eigener Tabelle, usw. In der Zahlungstabelle speicherst du die Zahlungseingänge. Das hat sogar den Vorteil, daß du dich an die doppelte Buchführung anlehnst. Du speicherst damit die Rechnungsstellung getrennt von dem Zahlungseingang. Finde ich gut.

Gegebenenfalls mußt du etwas Logik in die Ausleseprozesse packen, um zu erkennen, ob der Kunde für einen bestimmten Monat schon alles gezahlt hat, oder nur Anzahlungen gemacht hat...

[schoby24]

Vielen Dank für die Hinweise!


Jetzt hab ich einmal folgende DB Struktur

Kunden:
kuNr(PK), vorname, nachname, email, land, plz, ort strasse

-- natürlich kann ich land und plz noch extra in eine tb auslagern

Vertraege:
vertragsNr(PK), produktNr(FK), abrechnungszeitraum

Rechnungen:
rechnungsNr(PK), kuNr(FK), vertragsnummer, ausstelldatum

Zahlungen:
kuNr(PK), rechnungsNr, monat, jahr

Produkte
produktNr(PK), bezeichnung, preis


Jedoch hab ich noch eine Frage zu den Primarykeys (kuNr, rechnungsNr..)
und zwar werden die bei mir per Zufall generiert (z.B. RE-34545) und gibt es nun eine MySQL Funktion, die überpüft ob der Key bereits in der TB vorhanden ist ohne gleich eine select Abfrage anwenden zu müssen.

Vielen Dank!


mfg

schoby24

[mwanaheri]

Eine Zufallszahl als generierten Schlüssel zu nehmen, ist in zweierlei Hinsicht keine gute Idee.
1) Je mehr Einträge in der Tabelle sind, desto häufiger wird es vorkommen, dass die Zahl bereits verwendet wird. Also überschreibst du entweder einen gültigen Eintrag oder scheiterst beim Einfügen.
2) In einer geschäftlichen Anwendung muss die Zählung der Rechnungen fortlaufend und lückenlos sein.
Letzteres wird bei dir noch keine Rolle spielen, da es ja ein Übungsprojekt ist, aber ersteres solltest du vermeiden. Nimm also besser einen automatischen Zähler (serial), dann brauchst du auch nicht nachsehen, ob er schon vorhanden ist.


Solltest du die durchgehende Zählung gebrauchen, hier das Prinzip:
Du legst eine Tabelle mit Zählerständen für Schlüssel an:
tabelle|feld|letzter_wert

Dann brauchst du unbedingt eine Transaktionskontrolle.
-Beginne die Transaktion
-lies den aktuellen wert aus
-erhöhe ihn um eins
-benutze ihn als Schlüssel
-speichere ihn als letzter_wert
-schließe die Transaktion ab
bei Fehler: widerrufe die Transaktion

Abprüfen, ob ein Schlüssel bereits vorhanden ist, kannst du nur durch ein select (dann wird gefunden) oder durch ein insert (dann gibt es einen Fehler).

[elrond]

Um zahlungen zu verwalten bewährt sich täglich auf's neue ein Buchungsjournal zu führen. Voraussetzung hierfür ist ein Kontensystem, das sehr einfach sein kann. In deinem Fall würde ich zwei konten in betracht ziehen:

1. Empfängerkonto (typ=1)
2. Kundenkonto (typ=2)

die Tabelle könnte so aussehen:

buchungsid (PK)
kontonr (PK) <- Kontonummer=kundenID, Empfängerktonr=1
kontotyp (PK) <- s. o.
buchungsdatum
betrag

angenommen, es gibt einen Kunden (kundenID=100). Dann würde ich am anfang des Monats, wenn die zahlung fällig wird eine Buchung generieren.

!! Achtung: zu jeder Buchung existsiert eine gegenbuchung !!

Buchung:

buchungsid = 1
kontonr=1 (Empfänger)
kontotyp =1
buchungsdatum=heute
betrag=20.00

Gegenbuchung:
buchungsid = 1
kontonr=100 (Kunde)
kontotyp =1
buchungsdatum=heute
betrag=-20.00

sind die beiden buchungen drin, kannst du das journal aufsummieren und wirst den betrag 0 erhalten. das ist bei diesem system immer so, und ist eine ausgezeichnete kontrollmöglichkeit.

siehst du dir nun das konto des kunden an stehen da -20 drauf. die muss er mit seiner einzahlung ausgleichen:

Buchung:
buchungsid = 2
kontonr=100 (Kunde)
kontotyp =1
buchungsdatum=heute
betrag=20.00

Gegenbuchung:
buchungsid = 2
kontonr=1 (Empfänger)
kontotyp =1
buchungsdatum=heute
betrag=-20.00

nun sind alle konten ausgeglichen.
Das selbe spiel wird im nächsten monat wiederholt. wenn es mehr als einen kunden gibt, wird auf dem empfängerkonto trotzdem nur eine buchung vollzogen, mit der summe aller kundenzahlungen/-forderungen.


dieses system kannst du benutzen um viele tausend kunden mit den unterschiedlichsten dingen abzurechnen. Ich benutze da in einer tabelle für ein-/auszahlungen und ein bonusprogramm.

Die darstellung hier oben ist nur um das prinzip zu verdeutlichen. Natürlich solte das buchungsjournal noch felder wie buchungstext und ggf. einen buchungscode denthalten.

[schoby24]

ok, bei den rechnungsnummern werde ich fortlaufende PKs verwenden


Solltest du die durchgehende Zählung gebrauchen, hier das Prinzip:
Du legst eine Tabelle mit Zählerständen für Schlüssel an:
tabelle|feld|letzter_wert

Dann brauchst du unbedingt eine Transaktionskontrolle.
-Beginne die Transaktion
-lies den aktuellen wert aus
-erhöhe ihn um eins
-benutze ihn als Schlüssel
-speichere ihn als letzter_wert
-schließe die Transaktion ab
bei Fehler: widerrufe die Transaktion

Abprüfen, ob ein Schlüssel bereits vorhanden ist, kannst du nur durch ein select (dann wird gefunden) oder durch ein insert (dann gibt es einen Fehler).

Kann ich bei einem fortlaufenden Primarykey nicht einfach die auto_increment Funktion von MySQL verwenden?

Dann würde ich bespielsweise folgende KundenNr. bekommen:
1, 2, 3, 4, 5......

Wie kann ich jedoch KundenNr erstellen, die alle 6-stellig, eindeutig und eventuell fortlaufend sind ohne gleich eine extra Tabelle anlegen zu müssen?


mfg

[mwanaheri]

genau um das auto-increment geht es.

Was das Problem der sechsstelligen Zahlen angeht:
Die schnelle Lösung: 999999 Kunden einfügen und löschen. Danach sind dann die Nummern sechsstellig. Du kannst aber auch den Zählerstand manipulieren (musst mal in das Verwaltungswerkzeug von MySQL schauen, da sollte das gehen).

Fortlaufende Nummern musst du mit Transaktionskontrolle machen, sonst kann es Lücken geben. Ach ja: wirklich gelöscht werden darf dann auch nicht ;-)

[schoby24]

Gibt es nicht in PHP eine random Funktion, mit der ich in der abhängigkeit vom datum eine eindeutige kundennummer generieren kann?


Die schnelle Lösung: 999999 Kunden einfügen und löschen.

eigentlich reicht es auch wenn der erste datensatz in der tb die id 999999 besitzt oder?




Zählerstand manipulieren (musst mal in das Verwaltungswerkzeug von MySQL schauen, da sollte das gehen).

ich benutze phpMyAdmin 2.7.0-pl1 und leider ist dies nicht möglich

mfg

[mwanaheri]

Von PHP habe ich keine Ahnung, aber das Generieren von solchen Zahlen sollte man dem DBMS überlassen


eigentlich reicht es auch wenn der erste datensatz in der tb die id 999999 besitzt oder?

mfg

Ich bin mir nicht sicher, wie MySQL das macht, aber ich bezweifle, dass es den höchsten Wert aus der Spalte liest und den dann 1 raufsetzt. Schau mal unter 'Sequenzen' nach, da sollte eine angelegt sein. Meine Angaben beziehen sich aber nicht auf MySQL, weil ich das hier nicht habe. Da wissen hier andere besser Bescheid.

[schoby24]

Natürlich wäre es super wenn das DBMS (MySQL) eine 6 stellige KuNr generieren könnte

[RHBaum]

Egal welches DBMS oder nicht DBMS du verwendest, einen grundsatz solltest du Dir merken ^^

DatenbankID's sollten nur eine funktion haben, naemlich DatentbankID's sein, nix weiter. Sobald du diesen irgend etwas mehr Information zuschanzt, oder oder mehr Funktionalitaet hineinsteckst, wird es Dir das Genick brechen, designtechnisch gesehen

sonst kann es Lücken geben
DatenbankID's haben somit nur eine Anforderung: Sie muessen Eindeutig sein, nicht mehr und nicht weniger.
Fuer lueckenlose numnmerierung wuerd ich die ned verwenden, ich wuerd die nummerierung generieren lassen .


Natürlich wäre es super wenn das DBMS (MySQL) eine 6 stellige KuNr generieren könnte
Alles was in irgend einem Report auftaucht, sollte nicht als DB ID hergenommen werden ...

Eine Kundennummer wuerd ich extra halten ... natuerlich kann dir deine Umgebung (oder Stored procedures in msql, wenn du sie nutzt) dir das zeugs generieren ... nur versaubeutel die ID's nicht damit ^^

Ciao ...