From JSON Multidimensional array to SQL

Question

I have this JSON structure and need to pass it to a database. But I do not know if the structure I made is the best possible code has some repetitions that I do not think this is correct and I think it is unnecessary.

JSON:

{
    "info": [
        {
            "atriz": "Sandra Bullock",
            "atriz-id": "162",
            "atriz-slug": "sandra-bullock",
            "carreira": {
                "id": "264",
                "inicio": "08/10/91",
                "final": "16/08/18",
                "videos": [
                    {
                        "id": "2930500",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "Sb6y1oTwd",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "drama",
                        "upload": {
                            "send": true,
                            "id": "118840448",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    },
                    {
                        "id": "2930499",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "R2G0GhTwF",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "acao",
                        "upload": {
                            "send": true,
                            "id": "118840554",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    }
                ]
            }
        },
        {
            "atriz": "Jennifer Lawrence",
            "atriz-id": "207",
            "atriz-slug": "jennifer-lawrence",
            "carreira": {
                "id": "263",
                "inicio": "02/01/88",
                "final": "09/08/18",
                "videos": [
                    {
                        "id": "2930443",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "DNJNYHWh",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "drama",
                        "upload": {
                            "send": true,
                            "id": "118844113",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    },
                    {
                        "id": "2930442",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "OqieXJHwh",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "comedia",
                        "upload": {
                            "send": true,
                            "id": "118844112",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    }
                ]
            }
        }
    ]
}

I do not understand much of SQL so I used an online convert and it returned me this:

SQL:

CREATE TABLE IF NOT EXISTS infos (
    `info_atriz` VARCHAR(17) CHARACTER SET utf8,
    `info_atriz_id` INT,
    `info_atriz_slug` VARCHAR(17) CHARACTER SET utf8,
    `info_carreira_id` INT,
    `info_carreira_inicio` DATETIME,
    `info_carreira_final` DATETIME,
    `info_carreira_videos_id` INT,
    `info_carreira_videos_titulo` VARCHAR(49) CHARACTER SET utf8,
    `info_carreira_videos_desc` VARCHAR(78) CHARACTER SET utf8,
    `info_carreira_videos_code` VARCHAR(9) CHARACTER SET utf8,
    `info_carreira_videos_source` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_download` VARCHAR(4) CHARACTER SET utf8,
    `info_carreira_videos_link` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_duration` INT,
    `info_carreira_videos_post_status` VARCHAR(7) CHARACTER SET utf8,
    `info_carreira_videos_category` VARCHAR(7) CHARACTER SET utf8,
    `info_carreira_videos_upload_send` VARCHAR(4) CHARACTER SET utf8,
    `info_carreira_videos_upload_id` INT,
    `info_carreira_videos_upload_url` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_upload_status` VARCHAR(8) CHARACTER SET utf8
);
INSERT INTO infos VALUES
    ('Sandra Bullock',162,'sandra-bullock',264,'1991-10-08 00:00:00','2018-08-16 00:00:00',2930500,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','Sb6y1oTwd','http://URL','True','http://URL',NULL,'pending','drama','True',118840448,'http://URL','finished'),
    ('Sandra Bullock',162,'sandra-bullock',264,'1991-10-08 00:00:00','2018-08-16 00:00:00',2930499,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','R2G0GhTwF','http://URL','True','http://URL',NULL,'pending','acao','True',118840554,'http://URL','finished'),
    ('Jennifer Lawrence',207,'jennifer-lawrence',263,'1988-01-02 00:00:00','2018-08-09 00:00:00',2930443,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','DNJNYHWh','http://URL','True','http://URL',NULL,'pending','drama','True',118844113,'http://URL','finished'),
    ('Jennifer Lawrence',207,'jennifer-lawrence',263,'1988-01-02 00:00:00','2018-08-09 00:00:00',2930442,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','OqieXJHwh','http://URL','True','http://URL',NULL,'pending','comedia','True',118844112,'http://URL','finished');

Is this really the best possible structure for the database? This repetition in these values for example ('Sandra Bullock', 162, 'sandra-bullock', 264, '1991-10-08 00:00:00', '2018-08-16 00:00:00') does not have how to optimize this?

you could identify fields which repeat and move them into a separate "parent" table representing that entity (e.g. an Actor table might make sense in this case). Then the infos table (you should think of a more descriptive name for this!) would just contain the matching ID of the actor from the actor table. This is called a foreign key relationship. If you don't understand these basic concepts of database design, I suggest studying it before you go any further. Entity-relationship design and database normalisation are topics you should search for. — ADyson
– ADyson, Commented Sep 5, 2018 at 10:22

Andrey_Murgin · Accepted Answer · 2018-09-05 10:50:50Z

The answer depends on your goals you try to achieve. And also it is important to know in what way you will use this data further.

The structure is not very good. You will have several records (their count may be huge - depends on JSON) for the only JSON item. I'm not sure this will be convenient to retrieve data in such way. It is preferred to use relational model:

Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity (such as "Lee" or "chair") and the columns representing values attributed to that instance (such as address or price).

So, in your case, I would use more than 1 table.
-- videos with columns {id, titulo, desc, etc.}
-- carreira with id of related "atriz" record as a separate column and other columns {id, inicio, final}
-- carreira_videos - separate table which stores carreira-video relations. The rable may contain only two columns: carreira_id and video_id. For example, "Sandra Bullock" item in your example JSON has carreira with two videos - so, in carreira_videos table you will have two records:

carreira_id | video_id
264 | 2930500
264 | 2930499

-- atrizs with columns {atriz, atriz-id, atriz-slug} and separate column related carreira_id.

Actually, you're right about a lot of data repetition in your example. In case your JSON has more than just two items in "info" node, then there are more data duplicates. If you will use relational model (as it is preferred in SQL-based databases) and optimal schema such as described above you will avoid repetitions.
You need to study basics of relational databases and SQL before you go further. If you do not understand them then it is not real to create optimize design.
https://www.w3schools.com/sql/
https://searchdatamanagement.techtarget.com/definition/relational-database
Actually, if you want to store JSON itself then you may not need SQL-based database. You should look at NoSQL approaches and DBs https://en.wikipedia.org/wiki/NoSQL
https://www.quackit.com/json/tutorial/list_of_json_databases.cfm

All depends on your goals.

Collectives™ on Stack Overflow

From JSON Multidimensional array to SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related