Database structure for Recipes with RecipeGuides

https://tray2.se/posts/database-design

Level 73

I recommend reading these two posts. They should give you a good start.

https://tray2.se/posts/database-design-part-2

2 likes

Level 6

@Tray2 thanks, I'll read and give a feedback

Level 6

@Tray2 for now I think that I would prefer to store all data for the steps in one table, but also I wanna to see another options

Best Answer

Level 26

If you have only one guide for each recipe, which sounds logical, and this guide does not contain any additional information independent of the recipe: its own name, text, images, tags, etc., for example, different chefs prepare this recipe differently. Then, indeed, you can do without a table of guides.

And no, it won't be cumbersome, it's normal practice. The steps of different recipes are stored in one table, they are separated by the recipe they belong to (recipe_id key).

MySQL tables may look like this:

CREATE TABLE recipes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT
);

CREATE TABLE guide_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipe_id INT NOT NULL,
    step_number INT NOT NULL,
    step_text TEXT NOT NULL,
    image VARCHAR(255), 
    UNIQUE(recipe_id, step_number),  -- Ensure step order is unique per recipe
    FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
);

Insert data example:

INSERT INTO recipes (title, description) VALUES 
('Pasta Carbonara', 'Classic Italian dish');

INSERT INTO guide_steps (recipe_id, step_number, step_text, image) VALUES 
(1, 1, 'Boil water and add pasta.', 'step1.jpg'),
(1, 2, 'Fry bacon in a pan.', 'step2.jpg'),
(1, 3, 'Mix eggs and cheese.', 'step3.jpg');

1 like

Level 6

@EveAT thanks, actually while waiting for new ideas here I was searching for other solutions (json for example), and I figured out that It would be ok to have all guide steps in one table as you recommended, I also wanna to add indexes into the table for the better searching data experience

1 like

Snapey

Level 122

@EveAT your AI generated answer would be better if it was written for eloquent

2 likes

Level 26

@Snapey Your two cents are very much needed here. Yes, it is MySQL code, yes it should be.

Do you see a problem in the time savings that AI provides for writing a block of elementary code? There is some mockery in your answer.

Snapey

Level 122

@EveAT If you are going to use AI, why not tell it you are using Laravel framework?

Your opinion is less than 2 cents, because you got it from someone else.

Yes, there is some mockery because best practice is to have table design in code, not raw sql

Level 26

@Snapey blue frame, who do you think you are? Moderator of this forum? You barge into someone else's conversation and teach me how to respond in such a derogatory manner. Is my answer misleading? Is it wrong? No. So or answer yourself or move on.

What did I take from others? Basic SQL code? If you have nothing to say, it is better to remain silent - you will seem smarter. Or every answer count - more XP to remain on top? XD

This is not our first skirmish, and from your responses to other people I have already realized that you are a rotten person. It would be better to encourage those around you and not to mock, and maybe I would listen to you. Now it only makes me smile

Level 6

The end result looks like this

GuideStep.php :

class GuideStep extends Model
{
    use HasFactory;

    protected $fillable = [
        'recipe_id',
        'step_number',
        'step_text',
        'step_image'
    ];
}

create_guide_steps_table.php:

Schema::create('guide_steps', function (Blueprint $table) {
            $table->id();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->integer('step_number')->unique();
            $table->text('step_text');
            $table->string('step_image')->default('recipes-images/default/default_photo.png');
            $table->timestamps();
        });

Recipe.php:

...

public function guideSteps(): HasMany
    {
        return $this->hasMany(GuideStep::class);
    }

And now I can access the recipe guide using:

 $recipe = App\Models\Recipe::find(1); 

 $recipe->guideSteps

Level 26

@Dmytro_Shved check your create_guide_steps_table.php migration. The way you did it makes the step_number unique globally and not per recipe_id.

Here is how you can fix it:

Schema::create('guide_steps', function (Blueprint $table) {
            $table->id();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->integer('step_number'); // remove unique from here
            $table->text('step_text');
            $table->string('step_image')->default('recipes-images/default/default_photo.png');
            $table->timestamps();

			$table->unique(['recipe_id', 'step_number']); // add a composite unique
        });

2 likes