Datenbankstruktur für bevorstehendes Projekt

[hossi]

Hallo zusammen,

ich bin dabei ein neues Projekt vorzubereiten. Das Script soll auf PHP und MySQL basieren.

Die Funktionen des Scripts:
Mit dem Script sollen Sonderwünsche von Kunden/Käufern die eine Immobilie erwerben, verwaltet werden bzw. sollen diese mit div. Filtern später zu Listen zusammengefügt werden.

Bsp.: Kunde A möchte die Innentüre der Küche entfallen lassen. Somit muss der zuständige Handwerker der die Türen setzt darüber informiert werden (Schreiner), der Putzer die die Türleibung putzen muss und der Maler, der das ganze noch streichen muss. Somit muss dem Kunden A dieser Sonderwunsch zugewiesen werden, ebenso wie den drei Handwerksfirmen.

Als Datenbankstruktur habe ich mir folgendes ausgedacht (auf das o. s. Beispiel bezogen):

Tabelle: sonderwunsch
id | text | bv | kunde | hw
1 | innentüre entfällt | 1 | 1 | 1, 2, 3

Tabelle: kunde
id | name_ku | bv_id | sw_id
1 | Max Mustermann | 1 | 1

Tabelle: bauvorhaben
id | name_bv
1 | Testobjekt 1

Tabelle: handwerker
id | name_hw
1 | Schreiner
2 | Putzer
3 | Maler

Wie Ihr hoffentlich erkennt, möchte ich z.B. in die Tabelle Sonderwunsch in die Spalte hw (Handwerker) nur die entsprechenden IDs aus der Tabelle handwerker eintragen (Bei einer Abfrage soll dann später natürlich nicht die Zahl der ID stehn, sondern der entsprechende Name des Handwerkers).

Filter soll es folgende geben:
1. Alle Sonderwünsche eines Kunden zusammengefasst als Liste
2. Alle Sonderwünsche zu einem Projekt für einen Handwerker
3. Alle Sonderwünsche zu einem Projekt (Bauvorhaben) gesamt
… andere folgen sicherlich, sobald damit mal gearbeitet wird .. 

Nun zu meinen Fragen:
1. Ist der Aufbau der Datenbank so gut gelöst?
2. Ich hab jetzt leider keine Ahnung, wie solch eine Abfrage aussieht, wie z. B. für Filter 1

Danke für die Mühen im Voraus.

greetz hossi

[Kai]

Hi Hossi,

Tabelle: sonderwunsch
id | text | bv | kunde | hw
1 | innentüre entfällt | 1 | 1 | 1, 2, 3

So ist das definitiv ungünstig, wenn man nicht sogar von falsch sprechen kann.
Für Deine letzte Spalte brauchst Du eine neue Tabelle.

Tabelle: kunde
id | name_ku | bv_id | sw_id
1 | Max Mustermann | 1 | 1

Auch nicht korrekt.
Kunde ist 1 Tabelle.
Bauvorhaben ist 1 Tabelle.
Und auch Sonderwünsche ist 1 Tabelle.

Danach gibts 1 Tabelle, die nur Kunde und Bauvorhaben verknüpft
Dann eine Tabelle, die Sonderwünsche und Bauvorhaben verknüpft.
Dann eine Tabelle, die die Handwerker beinhaltet.
Dann eine Tabelle, die Sonderwünsche und den zu informierenden Handwerker (je Handwerker 1 Eintrag!).

Wenn ich nun nichts vergessen habe, sollte der Rest über Deine Queries zu lösen sein.

Gruß, Karl

[EKKi]

Mahlzeit hossi,

Bsp.: Kunde A möchte die Innentüre der Küche entfallen lassen. Somit muss der zuständige Handwerker der die Türen setzt darüber informiert werden (Schreiner), der Putzer die die Türleibung putzen muss und der Maler, der das ganze noch streichen muss. Somit muss dem Kunden A dieser Sonderwunsch zugewiesen werden, ebenso wie den drei Handwerksfirmen.

Verständlich. Und sehr lobenswert, dass Du Dir *vorher* Gedanken über den Aufbau der Datenbank machst ... :-)

Tabelle: sonderwunsch
id | text | bv | kunde | hw
1 | innentüre entfällt | 1 | 1 | 1, 2, 3

Damit weist Du einen Sonderwunsch genau einem Bauvorhaben und einem Kunden zu ...

Tabelle: kunde
id | name_ku | bv_id | sw_id
1 | Max Mustermann | 1 | 1

... und hier weist Du einem Kunden genau ein Bauvorhaben und einen Sonderwunsch zu. Dass das nicht gut gehen *kann*, sollte klar sein.

Darüber hinaus ist es eigentlich nie eine gute Idee, bei einer n:m-Beziehung (wie die zwischen Sonderwünschen und Handwerkern) eine Liste von IDs in einer Spalte zu speichern. Informiere Dich *dringend* zum Thema "Normalisierung".

Nun zu meinen Fragen:

1. Ist der Aufbau der Datenbank so gut gelöst?

Nein. Ich würde Dir eher folgende Struktur vorschlagen (unter der Voraussetzung, dass jedes Bauvorhaben *genau einem* Kunden zu geordnet werden kann):

Tabelle: kunde
id | name           | ...
---+----------------+----
 1 | Max Mustermann | ...

Tabelle: bauvorhaben
id | name         | kunde_id | ...
---+--------------+----------+----
 1 | Testobjekt 1 |        1 | ...

Tabelle: handwerker
id | name      | ...
---+-----------+----
 1 | Schreiner | ...
 2 | Putzer    | ...
 3 | Maler     | ...

Tabelle: sonderwunsch
id | text               | bv_id | ...
---+--------------------+-------+----
 1 | Innentüre entfällt |     1 | ...

Tabelle: sonderwunsch2handwerker
id | sw_id | hw_id
---+-------+------
 1 |     1 |     1
 2 |     1 |     2
 3 |     1 |     3

Ein Sonderwunsch gehört *immer* zu *genau einem* Bauvorhaben. Um welchen Kunden es sich handeln, muss nicht beim Sonderwunsch gespeichert werden, da der Kunde durch das Bauvorhaben eindeutig identifizierbar ist.

Für jeden an jedem Sonderwunsch beteiligten Handwerker gibt es in der n:m-Zuordnungstabelle einen Eintrag (sinnvollerweise legst Du dort einen UNIQUE-Constraint über die beiden Spalten "sw_id" und "hw_id" an).

Filter soll es folgende geben:

1. Alle Sonderwünsche eines Kunden zusammengefasst als Liste

2. Alle Sonderwünsche zu einem Projekt für einen Handwerker

3. Alle Sonderwünsche zu einem Projekt (Bauvorhaben) gesamt

4. Ich hab jetzt leider keine Ahnung, wie solch eine Abfrage aussieht, wie z. B. für Filter 1

1.

SELECT sw.name  
  FROM sonderwunsch AS sw  
  JOIN bauvorhaben  AS bv ON sw.bv_id = bv.id  
 WHERE bv.kunde_id = :bla:

2.

SELECT sw.name  
  FROM sonderwunsch            AS sw  
  JOIN sonderwunsch2handwerker AS sw2hw ON sw.id = sw2hw.sw_id  
 WHERE sw.bv_id = :foo:  
   AND sw2hw.hw_id = :bar:

3.

SELECT sw.name  
  FROM sonderwunsch AS sw  
 WHERE sw.bv_id = :blubb:

MfG,
EKKi

[Karl]

Hi EKKI,

Nein. Ich würde Dir eher folgende Struktur vorschlagen (unter der Voraussetzung, dass jedes Bauvorhaben *genau einem* Kunden zu geordnet werden kann):

Tabelle: bauvorhaben
id | name         | kunde_id | ...
---+--------------+----------+----
1 | Testobjekt 1 |        1 | ...

Dein Model entspricht exakt meinem, nur dass ich davon ausging, dass Deine obige Bedingung sicher nicht erfüllt ist.

Deshalb würde ich eine eigene Tabelle "Bauvorhaben" (ohne zugeordnetem Kunden!) bevorzugen und eine Zuordnungstabelle zwischen Kunde und Bauvorhaben erstellen.

Dann brauchst Du keine Voraussetzungen mehr.

Grüße, Karl

[Karl/Kai]

Grüße, Karl

Oops, habe doch glatt vergessen, wie ich heiße *g*

[hossi]

Hi Ekki,

Ein Sonderwunsch gehört *immer* zu *genau einem* Bauvorhaben. Um welchen Kunden es sich handeln, muss nicht beim Sonderwunsch gespeichert werden, da der Kunde durch das Bauvorhaben eindeutig identifizierbar ist.

Für jeden an jedem Sonderwunsch beteiligten Handwerker gibt es in der n:m-Zuordnungstabelle einen Eintrag (sinnvollerweise legst Du dort einen UNIQUE-Constraint über die beiden Spalten "sw_id" und "hw_id" an).

Ok, wir kommen der Sache näher :) Besten Dank schon mal für deinen Vorschlag für eine Datenbankstruktur.

Ein Problem ist jetzt noch, dass es natürlich auch Bauvorhaben mit mehreren Einheiten geben kann. Bsp. Bau von 20 Eigentumswohnungen. Also müssten mehrere Sonderwünsche einem Bauvorhaben zugeordnet werden können, bzw. in einem Bauvorhaben wohnen mehrere Eigentümer, später mal :)

Ich denke, dazu müsste man dann noch zusätzlich eine Tabelle Einheiten anlegen. Ein Bauvorhaben mit X-Beliebigen Einheiten. Wenn ich dich richtig verstanden habe, sollte die Datenbank dann so aussehen, hoffe ich:

Tabelle: kunde
id | name           | bv_id | einheit_id
---+----------------+-------+------------
 1 | Max Mustermann | 1     | 1

Tabelle: bauvorhaben
id | name         |
---+--------------+
 1 | Testobjekt 1 |

Tabelle: einheit
id | name             |  bv_id
---+------------------+---------
 1 | EG Wohnung links |  1

Tabelle: handwerker
id | name      | ...
---+-----------+----
 1 | Schreiner | ...
 2 | Putzer    | ...
 3 | Maler     | ...

Tabelle: sonderwunsch
id | text               | bv_id | einheit_id
---+--------------------+-------+-----------
 1 | Innentüre entfällt |     1 | 1

Tabelle: sonderwunsch2handwerker
id | sw_id | hw_id
---+-------+------
 1 |     1 |     1
 2 |     1 |     2
 3 |     1 |     3

Lieg ich hiermit richtig?

Greetz
hossi

[EKKi]

Mahlzeit hossi,

Ein Problem ist jetzt noch, dass es natürlich auch Bauvorhaben mit mehreren Einheiten geben kann. Bsp. Bau von 20 Eigentumswohnungen. Also müssten mehrere Sonderwünsche einem Bauvorhaben zugeordnet werden können, bzw. in einem Bauvorhaben wohnen mehrere Eigentümer, später mal :)

Wie auch immer Du das benennst, auf jeden Fall hättest Du dann ein umgebendes Irgendwas (das Haus), in dem sich 20 andere Irgendwasse (die einzelnen Wohnungen mit *genau einem*(?) Kunden) befinden.

Tabelle: kunde
id | name           | bv_id | einheit_id
---+----------------+-------+------------
1 | Max Mustermann | 1     | 1

Die Spalte "bv_id" ist unnötig - eine Einheit gehört zu *genau einem* Bauvorhaben, also reicht die Angabe einer "einheit_id".

Tabelle: sonderwunsch
id | text               | bv_id | einheit_id
---+--------------------+-------+-----------
1 | Innentüre entfällt |     1 | 1

Hier genauso.

Bei Einfamilienhäusern hättest Du dann *ein* Bauvorhaben mit nur *einer* Einheit - aber das sollte ja kein Problem darstellen, oder?

MfG,
EKKi

[hossi]

Hi Ekki,

Wie auch immer Du das benennst, auf jeden Fall hättest Du dann ein umgebendes Irgendwas (das Haus), in dem sich 20 andere Irgendwasse (die einzelnen Wohnungen mit *genau einem*(?) Kunden) befinden.

Ganz genau.

Tabelle: kunde
id | name           | bv_id | einheit_id
---+----------------+-------+------------
1 | Max Mustermann | 1     | 1

Die Spalte "bv_id" ist unnötig - eine Einheit gehört zu *genau einem* Bauvorhaben, also reicht die Angabe einer "einheit_id".

Alles klar! Seh ich ein :)

Tabelle: sonderwunsch
id | text               | bv_id | einheit_id
---+--------------------+-------+-----------
1 | Innentüre entfällt |     1 | 1

Hier genauso.

Bei Einfamilienhäusern hättest Du dann *ein* Bauvorhaben mit nur *einer* Einheit - aber das sollte ja kein Problem darstellen, oder?

dto.
Nein, stellt kein Problem dar, ist ganz richtig so :)

Dann kann ich mich ja jetzt mal vor den mysqladmin hauen und anfangen :)

Danke.

Aber eine Sache wäre da doch noch:
Bis dato sieht bei mir eine Eingabe via Formular in eine MySql DB so aus (einfach in eine Tabelle mehrere Spalten einfügen):

  
<?php  
  
  include('../config.php'); ?>  
  
  $bauvorhaben = $_POST["bauvorhaben"];  
  $einheit = $_POST["einheit"];  
  $handwerker = $_POST["handwerker"];  
  
  mysql_query("SET NAMES 'utf8';");  
  
  mysql_query("INSERT INTO garant  
  (bauvorhaben, einheit, handwerker) VALUES  
  ('$bauvorhaben', '$einheit', '$handwerker')");  
  
?>  

Nun zur Frage:
Als erstes lege ich ein Bauvorhaben an (funktioniert wie oben). Wie stelle ich nun eine Verknpüfung her, wenn ich eine Einheit anlege, dass die ID des Bauvorhabens bei Einheit in die Spalte id_bv geschrieben wird?

Greetz
hossi

[EKKi]

Mahlzeit hossi,

$bauvorhaben = $_POST["bauvorhaben"];
  $einheit = $_POST["einheit"];
  $handwerker = $_POST["handwerker"];

Wozu dieses überflüssige und fehlerträchtige Umkopieren? Du verschleierst damit nur die Herkunft der Werte. Und da diese Daten auf Benutzereingaben basieren (bzw. Du davon ausgehen *musst*), gilt: "ALL INPUT IS EVIL!" (d.h. Du kannst und *darfst* Dich nicht darauf verlassen, dass diese Werte gültig sind).

mysql_query("INSERT INTO garant
  (bauvorhaben, einheit, handwerker) VALUES
  ('$bauvorhaben', '$einheit', '$handwerker')");

Anschließend verwendest Du diese nicht geprüften Werte sorglos in einer Datenbankabfrage ... das ist in höchstem Maße riskant. Informiere Dich zu den Themen "SQL-Injection" und "Kontextwechsel". Dringend!

Besser wäre:

mysql_query(sprintf("INSERT INTO garant (bauvorhaben, einheit, handwerker) VALUES ('%s', '%s', '%s')", mysql_real_escape_string($_POST['bauvorhaben']), mysql_real_escape_string($_POST['einheit']), mysql_real_escape_string($_POST['handwerker'])));

oder gleich die Verwendung der mysqli*-Funktionen. Informiere Dich dazu in der PHP-Dokumentation Deiner Wahl.

Nun zur Frage:
Als erstes lege ich ein Bauvorhaben an (funktioniert wie oben). Wie stelle ich nun eine Verknpüfung her, wenn ich eine Einheit anlege, dass die ID des Bauvorhabens bei Einheit in die Spalte id_bv geschrieben wird?

Indem Du die ID des gerade angelegten Datensatzes ausliest und in Deiner nächsten Abfrage verwendest. Alternativ könntest Du auch über die Verwendung von "Stored Procedures" nachdenken - dies ist in der Datenbank gespeicherter Programmcode, der z.B. mehrere voneinander abhängige INSERT-Statements in einer Transaktion abarbeitet.

MfG,
EKKi

[hossi]

Hi,

Besser wäre:
mysql_query(sprintf("INSERT INTO garant (bauvorhaben, einheit, handwerker) VALUES ('%s', '%s', '%s')", mysql_real_escape_string($_POST['bauvorhaben']), mysql_real_escape_string($_POST['einheit']), mysql_real_escape_string($_POST['handwerker'])));

Du hast Recht, das Thema hatte ich vor einigen Tagen bereits :) Das wird dann bei dieser Version berücksichtigt :)

Indem Du die ID des gerade angelegten Datensatzes ausliest und in Deiner nächsten Abfrage verwendest. Alternativ könntest Du auch über die Verwendung von "Stored Procedures" nachdenken - dies ist in der Datenbank gespeicherter Programmcode, der z.B. mehrere voneinander abhängige INSERT-Statements in einer Transaktion abarbeitet.

Danke. Da hätte ich eig. auch von selbst draufkommen können .. :(

Danke soweit. Jetzt hab ich erstmal was zu tun :)

Greetz
hossi