Datenbankdesign für die Entwicklung von „Quiz“-Webanwendungen mit PHP und MySQL

Also versuche ich, PHP und MySQL zu lernen (ich habe ein grundlegendes Verständnis von beiden; ich habe die erste Hälfte von Head First SQL und Head First PHP & MySQL gelesen) und ich denke, der beste Weg, mein Wissen zu festigen, ist indem man etwas baut, anstatt zu lesen.

Vor diesem Hintergrund möchte ich eine einfache Webseite erstellen, die eine Verbindung zu einer MySQL-Datenbank auf einem Server herstellt. Ich werde ein einfaches HTML-Formular erstellen und Benutzern erlauben, grundlegende Informationen einzugeben, wie z. B.: Nachname, Vorname, E-Mail, Geburtstag, Geschlecht.

Mein Problem ist Ich weiß nicht, wie ich eine Datenbank entwerfen soll, die die Ergebnisse eines einfachen Quiz aufzeichnet – ich möchte nur 5 Multiple-Choice-Aufgaben. Schließlich möchte ich die Ergebnisse des Benutzers im Vergleich zu den Ergebnissen der vorherigen Benutzer anzeigen.

Wenn Sie mir helfen könnten, zu verstehen, wie man Tabellen für ein 5-Fragen-Quiz entwirft, wäre ich Ihnen dankbar. Vielen Dank!

Ich würde mit 4 einfachen Tabellen beginnen:

Benutzer

- user_id        auto integer
- regtime        datetime
- username       varchar
- useremail      varchar
- userpass       varchar

Fragen

- question_id    auto integer
- question       varchar
- is_active      enum(0,1)

Frage_Auswahl

- choice_id        auto integer
- question_id      Questions.question_id
- is_right_choice  enum(0,1)
- choice           varchar

Benutzer_Frage_Antworten

- user_id        Users.user_id
- question_id    Questions.question_id
- choice_id      Question_choices.choice.id
- is_right       enum(0,1)
- answer_time    datetime

Mein Gedanke zu diesem Tischdesign ist:

• Tisch Users dient zum Speichern registrierter Benutzer.
• Tisch Questions dient zum Speichern all Ihrer Fragen.
  • Es hat is_active sodass Sie selektiv nur aktive Fragen anzeigen können (mithilfe von WHERE is_active="1")
• Tisch question_choices dient zum Speichern aller verfügbaren Optionen. Es hat is_right_choice die definiert, welche Wahl die richtige Antwort auf eine bestimmte Frage ist.
• Tisch User_question_answers dient zum Speichern der Antwort Ihres Benutzers.
  • Es hat is_right für eine schnellere Suche, um zu sehen, ob diese bestimmte Frage- und Antwortauswahl richtig ist (basierend auf is_right_choice zuvor definiert).
  • Es hat auch answer_time nur um zu beachten, wann dieser bestimmte Benutzer die Frage beantwortet.

Ich bin mir nicht sicher, wie neu Sie im Allgemeinen in der Programmierung sind, aber selbst wenn Sie gerade erst anfangen, würde ich Ihnen empfehlen, ein Framework zu verwenden.

Die Verwendung eines Frameworks führt Sie durch die Bereitstellung von Best-Practice-Implementierungen der Tools, die Sie in Ihrem Projekt benötigen.

Ich persönlich benutze Symfonie für PHP-Projekte, und ich würde vorschlagen, dass Sie sich das ansehen ihre Anleitungen und Tutorials. Symfony ist ein etabliertes Framework und basiert auf weithin akzeptierten Designs.

Um Ihre Frage direkter zu beantworten, würde ich jedoch Folgendes für Ihre Anwendung vorschlagen:

 - user
  - id (PK)
  - last_name
  - first_name
  - email
  - gender


 - quiz
  - id (PK)
  - title


 - quiz_question
  - id (PK)
  - quiz_id (FK)
  - text

 - quiz_question_option
  - id (PK)
  - quiz_question_id (FK)
  - text
  - is_correct

 - quiz_user_answer
   - id (PK)
   - quiz_question_id (FK)
   - quiz_question_option_id  // this is the answer.

Das obige würde es Ihnen ermöglichen, mehrere Quizes mit jeweils mehreren Fragen zu definieren und Antwortsätze (einen Satz von Antworten eines Benutzers auf ein Quiz) zu erstellen und jede Antwort aufzuzeichnen.

Ich hoffe, das hilft 🙂

Dies war auch das erste Projekt, das ich vor ungefähr 8 Jahren in PHP/MySQL durchgeführt habe.

Ihre erste Lösung besteht darin, die Datenbank so zu codieren, dass sie genau mit Ihrem Formular übereinstimmt. Sie möchten also Benutzer und Quiz-Einreichungen aufzeichnen, also sieht es ungefähr so ​​aus:

CREATE TABLE users (
  username VARCHAR(16) PRIMARY KEY, 
  password VARCHAR(8), 
  email VARCHAR(255), 
  birthday DATE, 
  gender ENUM('M', 'F')
);

CREATE TABLE quiz_answers (
  username VARCHAR(16) REFERENCES users,
  question1 VARCHAR(10),
  question2 INT,
  question3 ENUM('YES', 'NO', 'MAYBE'),
  question4 BOOLEAN,
  question5 VARCHAR(25),
  submitted_at DATETIME,
  PRIMARY KEY (username, submitted_at)
);

Dies ist also nur das Nötigste: die Benutzer und die Quiz-Einreichungen. Ich habe Typen für die Antworten angegeben, die Sie für Ihr eigentliches Quiz spezifisch machen müssten. Ich habe auch eine Antwort gemacht, die den Benutzer und den Moment, in dem er sie abgeschickt hat, verschlüsselt hat. Es ist wahrscheinlicher, dass Sie einen Ersatzschlüssel verwenden (AUTO_INCREMENT), aber ich widersetze mich Surrogaten gerne so weit wie möglich.

Auf Anhieb gibt es eine 1NF-Verletzung: questionN. Wenn Sie dies richtig machen, würden Sie diese Spalten nach ihrer Bedeutung benennen, nicht nur nach der Frage, um die es geht. Aber dies zu normalisieren ist wirklich der nächste Schritt in Richtung Formulare, die erweiterbar sind, aber den Verlauf verfolgen.

Das nächste, was Sie bemerken würden, ist, dass ein Quiz eigentlich eine Sammlung von Fragen ist, von denen jede eine Sammlung möglicher Antworten hat. Und dann bezieht sich eine Formularübermittlung wirklich auf eine Reihe ausgewählter Antworten auf ihre Fragen in einem bestimmten Quizformular von einem bestimmten Quizbenutzer. Das klingt nach einer Vier-Wege-Beziehung: Benutzer, Quiz, Frage, Antwort. Sie können eines davon streichen, wenn es Ihnen nichts ausmacht, Fragen zu verschiedenen Quizes zu wiederholen, aber der Vollständigkeit halber gehen wir diesen Weg ein. Ersetzen quiz_answers oben damit:

CREATE TABLE questions (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  question TEXT
);

CREATE TABLE answers (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  question_id INTEGER REFERENCES questions,
  answer VARCHAR(255)
);

CREATE TABLE quizzes (
  name VARCHAR(255) PRIMARY KEY,
);

Wir haben eigentlich keine speziellen Metadaten für ein Quiz, daher ist es vorerst nur ein Name.

Jetzt brauchen Sie also eine Eins-zu-Viele-Beziehung von Fragen zu Antworten und von Quiz zu Fragen.

CREATE TABLE question_answers (
  question_id INTEGER REFERENCES questions,
  answer_id INTEGER REFERENCES answers,
  idx INTEGER,
  PRIMARY KEY (question_id, answer_id)
);

CREATE TABLE quiz_questions (
  quiz_name VARCHAR(255) REFERENCES quizzes,
  question_id INTEGER REFERENCES questions,
  idx INTEGER,
  PRIMARY KEY (quiz_name, question_id)
);

Der knifflige Teil, wie oben erwähnt, ist die Beziehung höherer Ordnung zwischen dem Benutzer und der Formularübermittlung und der Link von den Formularfragen zu den Benutzerantworten. Ich habe mich entschieden, dies in zwei Tabellen aufzuteilen, um Wiederholungen zu vermeiden.

CREATE TABLE quiz_submissions (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(16) REFERENCES users,
  quiz_name VARCHAR(255) REFERENCES quizzes,
  submitted_at DATETIME
);

CREATE TABLE submission_answer (
  submission_id INTEGER REFERENCES quiz_submissions,
  question_id INTEGER REFERENCES questions,
  answer_id INTEGER REFERENCES answers,
  PRIMARY KEY (submission_id, question_id)
);

Dies ist an dieser Stelle ziemlich gut normalisiert. Sie können sehen, dass die Abfrage auch etwas schwieriger sein wird. Um alle Fragen für ein Quiz herauszubekommen, müssen Sie vom Quiz zu den Fragen kommen. Sie können sich entweder von dort aus den Antworten anschließen, um eine große Abfrage durchzuführen, um alle Daten zu erhalten, die Sie zum Erstellen des Formulars benötigen (zusammen mit mehr Nachbearbeitung), oder Sie können für jede Frage erneut auf die Datenbank zugreifen und dies tun weniger Nachbearbeitung. Ich kann so oder so argumentieren. Um alle Antworten eines bestimmten Benutzers herauszubekommen, müssen Sie von user_submissions mit der Quiz-ID und dem Benutzernamen bis zur Tabelle submit_answer die Frage und die vom Benutzer gewählte Antwort auswählen. Das Abfragen wird also schnell interessant. Sie werden Ihre Angst vor Joins verlieren, falls Sie eine haben.

Ich hoffe, das schreckt Sie nicht zu sehr von relationalen Datenbanken ab; Auf diese Weise erstellen Sie tatsächlich ein relationales Modell innerhalb des relationalen Modells, wenn auch in eingeschränkter Form.

Mir ist klar, dass es heutzutage etwas unorthodox ist, viele natürliche Schlüssel zu verwenden, wie ich es oben getan habe. Ich empfehle Ihnen jedoch, es zumindest zu Beginn auszuprobieren, da es viel einfacher zu erkennen ist, wie die Joins funktionieren müssen, wenn sie nicht alle Ganzzahlen im Bereich von 1 bis 10 sind.

Nun, im Moment bin ich in einer Entwicklungsphase und stoße immer noch auf einige Probleme (z. B. das Synchronisieren von Rundenzuständen zwischen Server und Client), aber es kann Ihnen etwas helfen

PS: Speichern Sie keine Passwörter in der Datenbank, wie im Bild oben – speichern Sie stattdessen Passwörter-Hashes