[low] Database Design

[Schattenfänger]

Hey ich schreibe gerade an einer kleinen Musikverwaltungssoftware und habe schon länger nix mehr mit DBs zu tun gehabt.
Habe mich jetzt zwar wieder etwas eingelesen, aber wollte trotzdem nachfragen.
Ich will jetzt nicht unbedingt alles bis ins kleinste Detail beschreiben, da ich hoffe, dass ich auch so mit ein paar wenigen Beispielen den Rest selbst machen kann.
Vor allem, die Foreign Keys machen mir sorgen.

Achh, könnte mir vielleicht jemand ein DB Design Tool empfehlen? Habe jetzt MySQL Workbench verwendet, aber das ist nicht gerade stabil.

Also:
Ein Entry(Musikstück) hat einen Interpreten / lyric / year / studio / userrating / sellCount.
Also mal eine 1:1 Beziehung.
EIn Interpret/year/userrating/studio/sellCount jedoch können mehr als ein Entry besitzen also eine n:m Beziehung ?

Tabellen:
Entry:
entry_ID (PK)
entry_name
entry_eintragungsdatum (okay, kann ich wohl auch in ne eigene tabelle)

Lyric:
lyric_ID (PK)
lyric_plot
entry (FK)

Muss ich jetzt in Lyric oder in Entry den Foreigen Key bekanntgeben?

userRating:
userRating_ID(PK=)
userRating
entry (FK)

Hier, ein Rating von zehn kann zb ja hundert entry besitzen.
Ich habe mal gesehn, das man das dann in eine eigene Tabelle schreibt, solche Beziehungen?
Eine einzige nur zum Verweis zwischen entry und userRating.
Wäre das korrekt?
Dann hätte ich nicht mehr hundert userratings sondern nur mehr maximal 0 bis ~10.
Andrerseits wäre dafür dann doch die Zwischentabelle wieder voll.

Achh, was passiert eigentlich wenn ich die PKs Auto Incremente?
Ich habe jetzt hundert Einträge und lösche den PK 27 heraus.
Dann füge ich wieder einen neuen ein, wird dieser bei 27 eingefügt oder bei 101? (Solange ich nichts anderes angebe)

 

[New_Escaflowne]

Mal dir das Konzeptuelle am besten erstmal als Entity Relationship Modell auf ein Blatt Papier und mach dir genau klar, was du für Entity-Typen brauchst und was nur ein Attribut ist (z. B. kommt es mir nach deiner Beschreibung so vor, als ob du "year" als eigenen Entity-Typen modellieren willst?! oder das Rating. Das sind meiner Meinung nach Attribute eines Musikstückes. Um dann Musikstücke mit einem Rating von z.B. 10 zu finden, nutzt man einfach eine SQL-Abfrage über die Musikstücke).
Bei den Kardinalitäten zwischen den Entity-Typen kommt es auf deine Modellierung deines "Weltausschnittes" an, ob du 1:1, 1:n oder n:m brauchst.

Wenn du das hast, das ER-Modell einfach (per Hand) in ein Relationales Modell umwandeln. 1:1 und 1:n sollte man über Fremdschlüssel modellieren (solange die Beziehung keine eigenen Attribute hat). Für n:m braucht die Beziehung eine eigene Relation.
Wenn du dir dann noch Gedanken um die Funktionalen Abhängigkeiten zwischen den einzelnen Attributen machst, kannst du die Relationen in die dritte Normalform normalisieren, um keine Redundanzen zu haben.
Diese Relationen kannst du dann so in deiner Datenbank verwenden.


Zum Fremdschlüssel:
Als Fremdschlüssel wird immer die Attributmenge angegeben, die in einer anderen Relation ein Primärschlüssel ist.
Ein Primärschlüssel identifiziert ein Tupel der Relation eindeutig; das muss auch nicht immer eine ID sein, sondern kann z. B. ein eindeutiger Name (und nicht NULL) sein.

 

[NullPointer]

Ein Stück hat 1 Interpreten, aber ein Interpret hat beliebig viele Stücke, die Beziehung ist also 1:n. Der Fremdschlüssel kommt auf die n-Seite, also bei Entry. (Andersherum wäre es schwierig )

Was ist denn die genaue Bedeutung von userRating? Ist das einfach nur eine Zahl für jeden Entry? Dann pack sie doch einfach in die Entry-Tabelle. Mit extra Tabellen würde ich da erst anfangen, wenn ein Entry mehrere Ratings haben kann.

Wie sich die auto-inkrementierten Fremdschlüssel verhalten, hängt von deinem Datenbanksystem ab. Bei denen, die ich kenne, werden solche Lücken aber nicht geschlossen, d. h., es ginge bei 101 weiter.

 

[sasdensas]

Ein gutes Tool ist ein Blatt Papier und ein Bleistift. Gibts in der Vollversion auch mit nem Radiergummi ^^. Dann je nachdem, wie dein Scope sein soll, bröselst Du alles schön in Entitäten auf. Ich würde folgende Entitäten verwenden: Interpret, Lied, Album/CD, Bewertung. Daraus entstehen aber hauptsächlich n:m Verbindungen, die möglichst in 1:n umgewandelt werden sollten; Stichwort: schwache Entität oder Zwischentabelle.

 

[Krik]

Du hast die Attribute eines Objekts auf mehrere Tabellen verteilt. Das bringt nichts (außer Kopfschmerzen, wie du ja merkst ).

Dann hätte ich nicht mehr hundert userratings sondern nur mehr maximal 0 bis ~10.
Andrerseits wäre dafür dann doch die Zwischentabelle wieder voll.

Exakt! Du hast zwar dann nur noch bspw. 10 Ratings, aber bei n Entries auch n Verweise auf die Ratings. Deine Idee bringt dir gar nichts.

Achh, was passiert eigentlich wenn ich die PKs Auto Incremente?

Er macht bei der höchsten zuletzt verwendeten Zahl weiter. Hast du als Keys die Ziffern 1-10 und löscht die alle, bekommt der nächste, neue Eintrag die ID 11.


Ich empfehle dir, mal genau zu überlegen, was für Daten du eigentlich hast und wie sie zusammengehören.
Bei mir sähe die Sache ungefähr so aus:

 

[kling1]

es gibt doch auch collaborationen zwischen interpreten also kann ein musikstück auch mehrere interpreten haben.. und ein interpret kann mehrere musik stücke haben also ist das eine n:m beziehung..

heißt du brauchst ien zwischentabelle
also
table_song
id
..
..

table_song_artist
song_id
artist_id

table_artst
id
..
..

 

[Krik]

Ah, stimmt! Daran habe ich gar nicht gedacht.
Ich würde es dann genauso wie du machen, klomann83.

Also irgendwas in dieser Richtung:

 

[Daaron]

Er macht bei der höchsten zuletzt verwendeten Zahl weiter. Hast du als Keys die Ziffern 1-10 und löscht die alle, bekommt der nächste, neue Eintrag die ID 11.

Das ist so nicht vollkommen korrekt. MySQL InnoDB verhält sich hier z.B. teilweise inkonsistent.
Wenn du die 10 löschst und direkt was neues anlegst erhält es die 11. Wenn du die 10 löschst, den Server neu startest und jetzt einen neuen Eintrag anlegst, erhält er die 10.

 

[Schattenfänger]

Okay, danke sehr für die Antworten!
Entschuldigung das ich mich nicht gemeldet habe...

Das heißt also, dass wenn ich sage das ein Interpret nur einen Song haben kann, dann wäre es klüger diesen direkt in die Song-Tabelle zu speichern?.
So wie es bei laurin steht kann ich halt für einen Song mehrer Interpreten anlegen wodurch ich nicht immer einen neuen Eintrag erstellen muss sondern nur einen Verweis bei coll.

Ich dachte man könne keine n:m Relationen abbilden? (außer durch Zwischentabelle) Wesbalb steht dann bei laurin von Song auf Coll eine n:m Beziehung?

In wie fern ist es ein Unterschied wenn bei dem Diagramm nur der Pfeil von Album zu Song geht aber nicht auf beide Seiten?
Kommt doch wieder aufs gleiche raus?

Ein Song kann in mehreren Alben vorkommen 1:n.
Und in einem Album können mehrere Songs sein 1:n.
Also, kommts dann auf eine n:m Beziehung hinaus.

Also kann ich sagen, dass man ein Attribut in eine Table schreibt wenn diese "absolut" ist.

Was wäre dann zb bei Genre?
Da könnte ich doch wieder eine Tabelle anlegen.
Und dafür benötige ich dann wohl wieder eine Zwischentabelle, right?

 

[Krik]

n:m-Beziehungen müssen mit Hilfstabellen gemacht werden. Alternativ kann auch das Attribut eine Liste sein, wo du IDs einfach reinwirfst. Ich weiß jetzt aber nicht, ob das so klug ist. Ich denke eher nicht.
Ich hab nur deshalb n:m geschrieben, weil das Bild sonst riesig wird und ich nicht soo viel Lust darauf hatte.

Dasselbe mit dem Pfeil. Nimm das Bild von mir nicht zu ernst. Es soll das Prinzip klar machen, nicht eine fertige Lösung sein.

Das heißt also, dass wenn ich sage das ein Interpret nur einen Song haben kann, dann wäre es klüger diesen direkt in die Song-Tabelle zu speichern?

Andersrum wird ein Schuh daraus. Wenn du einem Song nur ein Attribut "Interpret" statt eines Foreign Keys gibst, dann bedeutet das: "Jeder Song hat maximal einen Interpreten." Das bedeutet aber auch, dass ein anderer Song zufällig denselben Interpreten haben kann.

Also kann ich sagen, dass man ein Attribut in eine Table schreibt wenn diese "absolut" ist.

Was bedeutet absolut bei dir in diesem Fall?
Ein Attribut ist eine Eigenschaft eines Enities. Ob man eine Eigenschaft lieber als Attribut oder als extra Entity haben will, hängt davon ab, welche Anforderungen man an die DB stellt und wie weit man die Normalisierung treiben will.

 

[Schattenfänger]

Andersrum wird ein Schuh daraus. Wenn du einem Song nur ein Attribut "Interpret" statt eines Foreign Keys gibst, dann bedeutet das: "Jeder Song hat maximal einen Interpreten." Das bedeutet aber auch, dass ein anderer Song zufällig denselben Interpreten haben kann.

Darauf wollte ich hinaus.
Also eine Tabelle für den Interpreten nur anlegen wenn ich für diesen weitere Infos speichern will?
Wenn ich nur den Namen will dann reicht eine Zeile bei Song.

Was bedeutet absolut bei dir in diesem Fall?
Ein Attribut ist eine Eigenschaft eines Enities. Ob man eine Eigenschaft lieber als Attribut oder als extra Entity haben will, hängt davon ab, welche Anforderungen man an die DB stellt und wie weit man die Normalisierung treiben will.

Mit absolut meinte ich, dass man das Attribut nicht weiter "finalisieren" kann.
Sprich weiter auseinanderreisen, also zb nen Eigennamen.
Ja, okay das gehört eigentlich zu den Normalformen....

 

[Krik]

Also eine Tabelle für den Interpreten nur anlegen wenn ich für diesen weitere Infos speichern will?
Wenn ich nur den Namen will dann reicht eine Zeile bei Song.

Jepp.

Mit absolut meinte ich, dass man das Attribut nicht weiter "finalisieren" kann.
Sprich weiter auseinanderreisen, also zb nen Eigennamen.

Das gehört in der Tat zu den Normalformen. Was du "absolut" oder "finalisieren" nennst, heißt in der Fachsprache "normalisieren."

Das hat unter anderem das Ziel, Daten atomar abzuspeichern. Atomar bedeutet, dass Daten in ihre kleinsten Informationshäppchen aufgesplittet werden. Telefonnummern werden hier zB nach Landesvorwahl, Regionalvorwahl und Rufnummer zerlegt.
Warum sollte man das wollen? Ggf. will man man schauen, welche Regionalvorwahlen zu einer Landesvorwahl gehören. Bei dieser Abfrage ist es völlig wurst, wem die Rufnummer gehört, wie viel Geld er verdient und wo er wohnt. Bei der Auswertung befragt die DB nur die Tabellen bzw. die eine Tabelle mit Regionalvorwahl und Landesvorwahl und zählt dann aus.
Wären die Daten nicht atomar und alles in eine Tabelle gestopft, müsste er zB Namen des Anschlussinhabers, seine Adresse usw. in die Auswertung mit einbeziehen, obwohl sie nicht von Belang sind.

Die Sache ist, bei Abfragen bastelt die DB aus allen Tabellen, die durch das FROM-Schlüsselwort angegeben sind, intern eine große Tabelle. Dann geht er alle Bedienungen des WHERE-Schlüsselwortes durch und wählt die passenden Tupel aus. Das, was übrig bleibt, sortiert und gruppiert er nach je nachdem, was durch GROUP und SORT angegeben ist. Und schließlich zeigt er dann nur die Daten an, die durch SELECT angegeben sind.*

* Es ist möglich, dass ich die Reihenfolge vertauscht habe. Das Thema ist bei mir schon länger her.

Du siehst, der Prozess ist recht komplex. Den will man möglichst vereinfachen und das schafft man nur dadurch, dass möglichst wenig Daten dabei verarbeitet werden. Das erreicht man einmal dadurch, die Daten atomar abzuspeichern, sodass gezielt nur die wirklich benötigten Daten verwendet werden. Zum anderen kann man auch durch möglichst intelligent formulierte Abfragen, die Ressourcen schonen.

Der Punkt bei der Sache ist, diese Optimierungen kommen erst bei großen Datenmengen so richtig zum Tragen. Wenn du eine DB mit vielleicht 1.000 Einträgen hast, merkst du bei Abfragen nix davon. Beispielwerte (ausgedacht!): 10 ms unoptimiert vs. 1 ms optimiert.
Hast du eine DB mit 1.000.000+ Einträgen, wird das merklich spürbarer.
Beispielwerte (ausgedacht!): 1000 ms unoptimiert vs. 100 ms optimiert.

Aus diesem Grund schreibe ich auch laufend, dass du selber ausknobeln musst, wie weit du die Normalisierung treiben willst. Ist die Software für den kleinen Privatmann, ist es fast egal, wie du die DB gestaltest. Die Datenmengen sind da extrem gering. Ich tippe auf maximal ein paar tausend Einträge. Ist die Software für einen Musikladen, sieht es schon etwas anders aus. Da kommen schnell zehn- oder hunderttausend Einträge zusammen.

 

[Schattenfänger]

Okay, danke
Ich werde dann mal meine Scripts zusammenschreiben

 

[Schattenfänger]

AW: [low] Database Designup

Sooo
Wie ichs mir gedacht habe wurde diese Aufgabe etwas erweitert

Basiert zwar auf der vorigen MusikDB aber hat sich ein bisschen vergrößert.

Hier mal zusammengefasst, was mir aufgetragen wurde:

Entwickeln Sie eine Datenbank, welche möglichst alle Typen von Medien abspeichern kann (zumindest: Lieder, Filme, Serien, Bücher usw...)
Zu jedem Medientyp sollen benötigte Grundinformationen abgespeichert werden können (zB.: Film: Story, Drehjahr, Regie, Benutzerkommentar, Genre usw).
Entwerfen Sie unter zu Hilfe nahme des dazugehörigen ER-Modelles eine Datenbank in mindestens dritter NF!

1.) Des weiteren soll eine einfache und flexible Möglichkeit bestehen die Datenbank nach Kundenwunsch zu erweitern.
Sei es für mehr Medientypen oder aber neue Attribute für einen bereits bestehenden Medientyp.

Hier meine vorläufige DB:

Ich schätze mal Type_Table werde ich wohl entfernen können.
Nur weis ich nicht genau wie ich dann die einzelnen "Obertabellen" adressieren kann.
Etwa immer durch konditionelle Bedingungen?

Bei Actor bin ich mir auch nicht sicher ob ich dafür zwei Zwischentabellen verwenden sollte.
Ich schätze mal, das eine einzige reichen wird.
Ich hoffe mal, dass ich die FKs korrekt gesetzt habe?

 

[Krik]

Wofür ist Type_Table überhaupt gut?
Und wenn ich mir die Aufgabenstellung ansehe, dann kannst du so ziemlich alles, was man in der Grafik sieht, wegwerfen oder zumindest komplett neu anordnen.

Btw, wir machen jetzt nicht zufällig gerade deine Hausaufgaben, oder?

 

[Schattenfänger]

Wofür ist Type_Table überhaupt gut?
Und wenn ich mir die Aufgabenstellung ansehe, dann kannst du so ziemlich alles, was man in der Grafik sieht, wegwerfen oder zumindest komplett neu anordnen.

Ja, das habe ich mich auch schon gefragt.

Was genau sollte ich da genau anordnen?

Bei Entrys werden sämtliche eingetragenen Medien gespeichert.
Eine Entry hat entweder einen Song, Movie, Serie oder Buch.
Ein Movie kann mehrere Actors und Genres haben und umgekehrt. n:m
Eine Serie hat mehrere Staffeln und eine Staffel kann nur bei einer Serie sein. 1:n
Eine Staffel hat mehrere Eps und eine Eps kann nur bei einer Staffel sein. 1:n

Keine Ahnung was ich da jetzt genau neu anordnen soll.

Btw, wir machen jetzt nicht zufällig gerade deine Hausaufgaben, oder?

Nope, wäre mir neu.

 

[Krik]

Jetzt kommt man langsam an die Grenzen des ER-Modells.
Man kann es hier mit Generalisierung probieren (so ähnlich hast du es ja auch schon da oben gemacht).

Ich würde es allerdings auf andere Art probieren:
Es fällt auf, dass du genau zwei Konzepte in der Datenbank abspeichern willst. Das eine ist ein Medium (Buch, Song, Movie, Episode), das andere ist eine Sammlung/Collection (Album, Serie). Da wäre es eine Idee, genau diese Konzepte zu modellieren.

Im Anhang ist ein Bild, wie man es machen könnte. Beachte, dass die Collection auf eine andere Collection verweisen kann, um Hierarchien wie Serie -> Staffeln darstellen zu können.


Edit: Besseres Bild