CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
X3minater
X3minater

Reputation: 99

Database structure for Cocktail Recipes

let me start by saying I'm a complete starter on SQL and MySQL, so decided to invest in this pet project to develop my database knowledge.

Objective: Build a very simple website to access a cocktail recipe database. The fields it has id, name, glass, ingredients, method, ice, garnish, comment.

It would be pretty simple for me if all the fields were linear but the ingredients column is cracking my head.

This is an example of a recipe: This is an example of a recipe:

Some recipes may have 3 ingredients, some may have 5, others 10, and sometimes with different ways to measure those ingredients (ml, dashes, tsp, ...).

My first question is, what's the simplest way to build a database like that? And how would I implement that?

Having one table with all the ingredients would be interesting for future development for stock recording.

Since I'm upgrading from an pretty, ready to print, Excel sheet to sql database, my second question is, What's the easiest/fastest method to move all the recipes to the database? Again, I'll face a problem with the ingredients part. Should I even try to export and import or just suck it up and input all the recipes by hand?

Thanks for reading and for any comment on the subject :)

Upvotes: 1

Views: 2317

Answers (1)

user5964698
user5964698

Reputation:

I suggest you look into database normalisation. Essentially, it's the process of breaking up data into specific groups and then forming a new table with that data.

A few reasons to do is a) a good structure can make it easier to find, maintain, scale etc b) you want to repeat as little data as possible. If your database had one million records, you'd want to be as efficient as possible.

Table 1: ID (PK),Name,Method,Ice,Garnish,Comment

1 Cocktail Collins Shake all ingredients... Cubed Lemon slice For the elderflower...

Table 2: ID (PK), T1_ID (foreign key), Ingredient, Quantity, Measure

row 1-

1: 1: Beefeater Gin: 2: tsp

row 2-

2: 1:Lemom Juice: 1: tsp

row 3-

3: 1: Sugar Syrup: 1: tsp

row 4-

4: 1: Soda 75: ml

Upvotes: 2

Related Questions