Creating a Quiz in PHP/PDO - help with ERD

[webdeveloper123]

Hey guys,

Just a quick one. I would like some advice. I am making a quiz in PHP. I have the following db design and the image attribute is inside the questions table. This attribute will hold the file name of an image (e.g movie_poster.jpg). But later on I thought to take out that attribute from that table and have a new entity called pictures and hold the filenames in there. Surely that would be a better idea? If I don't, I don't think it will conform to Normalisation. 

Here is the ERD:

Many thanks

[Strider64]

My hangman type of game is setup differently, but I keep everything together.

Here's my table setup

CREATE TABLE `lego_trivia` (
  `id` int NOT NULL,
  `question` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `answer` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `canvas_images` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `points` int NOT NULL DEFAULT '10'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

and here's just one of my PHP files

<?php
require_once '../assets/config/config.php';
require_once "../vendor/autoload.php";
use FanOfLEGO\Database;

$pdo = Database::pdo();

// Parse the input data
$data = json_decode(file_get_contents('php://input'), true);
if (!$data) {
    try {
        errorOutput('Invalid input data', 400);
    } catch (JsonException $e) {
    }
    exit();
}

$current_id = (int) $data['current_id'];

try {
    $stmt = $pdo->prepare('SELECT id, canvas_images FROM lego_trivia WHERE id > :current_id ORDER BY id LIMIT 1');
    $stmt->bindValue(':current_id', $current_id, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        // Makes it, so we don't have to decode the json coming from javascript
        header('Content-type: application/json');
        $data = ['next_id' => $result['id'], 'image' => $result['canvas_images']]; //canvas_images is the path
        output($data);
    } else {
        // Reached the end of the table
        output(['end_of_table' => true]);
    }
} catch (PDOException $e) {
    errorOutput($e->getMessage(), 500);
}

function errorOutput($output, $code = 500)
{
    http_response_code($code);
    echo json_encode(['error' => $output]);
}

function output($data)
{
    http_response_code(200);
    echo json_encode($data);
}

and a link to the some-what finished game https://www.fanoflego.com/hangman/can_you_solve.php

My point is that it is easier to keep everything organized if it is in one table though have a separate table for high scores is something I will will be working on next.

Edited April 7, 2023 by Strider64

[maxxd]

If there's a reasonable or good chance that there will be multiple images per question, then yes make a joining table. However, if it's most likely that there's only going to be one image per question then keep the data where it is.

The thing about db normalization is that it's kind of relative; one appropriately normalized database won't look like an appropriately normalized database for a different project. I personally think first normal form is a pipe dream, and from what I've read third normal form is kinda the ideal. It has all the logical hallmarks of a well-designed database and is easy to read and reason about, but it's also true to the business logic and functional specifications for the specific project.

Edited April 8, 2023 by maxxd
typo

[webdeveloper123]

Thanks guys. I think i'll keep the DB as it is.

On 4/7/2023 at 5:44 PM, Strider64 said:

My hangman type of game is setup differently, but I keep everything together.

 

Nice game, I visited the link. There is a lot in your game which I want in mine. Your keeping your answers in the same table but I have multiple choice answers so I have to have a separate table for the answers

[gizmola]

This really depends on whether or not you will expand or use this same schema for other things.  Certainly you should have a separate table for assets, as that is almost always a better idea that just having a varchar in the table.  Once you've done that, it's much easier to provide a failsafe against things like corruption of an image during replacement, or generating a filename that isn't the same as the original name of the image.  The other obvious thing that jumps out, is whether or not you might want eventually to have an image associated with the answer

So the question here, is really should you have asset_id as a foreign key in the question table, or might there be a question that requires multiple images.  If you make a foreign key (or just include the string as you have) then you aren't able to support that.

I also think relationally, you could argue that a url string is not a core attribute of a question, and thus begs the question of what entity really applies (which would be asset or image).

 

[webdeveloper123]

Hey gizmola,

14 hours ago, gizmola said:

things like corruption of an image during replacement, or generating a filename that isn't the same as the original name of the image

No, the pictures are fixed. The filenames won't change and the images won't be replaced with new ones. 

 

14 hours ago, gizmola said:

The other obvious thing that jumps out, is whether or not you might want eventually to have an image associated with the answer

I thought about that but decided against it. 

 

14 hours ago, gizmola said:

or might there be a question that requires multiple images

No, I won't be doing that.

 

Thanks for your help