I have a JSON file that stores the information about a bunch of recipes, like cuisine, time, the ingredients, instructions, etc. I am supposed to transfer all the data to a MySQL table with the relevant headings.
The "ingredients" and the "instructions" are stored like this:
The instructions and ingredients have several "lines" , stored as a list.
How can we store the ingredients and instructions in a MySQL table, in a line by line format?
something like:
instructions
inst1
inst2
..
The JSON file was created using a python program using the beautiful soup module.
PS: I am very new to both SQL and JSON, so I unfortunately dont have anything to show under "what I tried"...Any help will be appreciated.
Rather than give you the exact answer, I'll give you the process I use to determine a database structure. You're using a relational database, so that's what I'll talk about. Its also good to have a naming convention, I've used CamelCase here but you can do whatever you want.
You mentioned you were using python but this answer is language agnostic.
You've chosen quite a complex example, but I'll assume you understand how to create a table, and use primary keys and foreign keys. If not, maybe you should do something simpler.
These are the real-life entities which need to represented as database tables. In this case, I'm seeing 4 entities;
Each of these can have a table in MySql. Give them a Primary key which follows a naming convention.
It looks like keywords are shared between multiple recipes, so you'll a many to many relationship - this means there's going to be an extra table,
This is just a link between Recipe and keyword to avoid redundancy. It has two foreign keys, RecipeId and KeywordId. At the moment its just a dumb object. In other situations like this, its common for an application to need information about a join - for example, who linked the two things together (consider users, permissions, and a join table with information on who granted the permission)
The other entities are one to many - each will need a foreign key, RecipeId
As well as having several lists, your Recipe object has some properties. These can be in its table. Most of them are strings in your data, although there are better ways to store things we can keep this simple.
The other entities just have a text field, from your screenshot, only the Recipe has properties.
For this system, you'll need to first insert all Recipe and Keyword objects. There is a common pattern in relational databases where in insert a record, and get its ID so you can insert more stuff which references it.
I don't know of one but google will help you find it. The documentation should include the basics of querying.
Here is some psudocode
FOR EACH recipe
INSERT the recipe, and get its ID
FOR EACH keyword
IF the keyword does not exist already
INSERT the new keyword and get its ID
INSERT a record into RecipeKeyword with RecipeId and KeywordId
FOR EACH ingredient
INSERT the ingredient, give it RecipeId as a foreign key
FOR EACH instruction
INSERT the instruction, give it RecipeId as a foreign key
That's it. From here you can select with joins - To form what we're seeing above, you might need to do 3 seperate queries and merge them together into a record object on the python side to reproduce the original structure.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With