Структура таблиц БД

[Grut]

Коллеги, помогите, пожалуйста, сообразить правильную структуру таблиц базы даных в MySQL.
Исходные данные:
Совершая get-запрос к API SemRush, получаю массив данных в формате json. Этот массив есть список отслеживаемых поисковых запросов, их позиция в Гугле, урл ранжируемой страницы и др.
Один элемент массива выглядит следующим образом:

    "1": {
      "Pi": "16409507011027092224",            ## keyword ID ##
      "Ph": "keyword",                         ## keyword ##
      "Kb": 20151208,
      "Tg": {                                  ## tags for a keyword ##
        "0": "brand"
      },
      "Cp": "0.86",                            ## Average price in U.S. dollars
      "Nq": "320",                             ## Search Volume, monthly ##
      "Gs": "0",
      "Dt": {                                  ## array of dates and positions ##
        "20171116": {
          "www.domain.com/*": 6
        }
      },
      "Be": {                                 ## Position at the beginning of specified period
        "www.domain.com/*": 6
      },
      "Fi": {                                 ## Position at the end of specified period
        "www.domain.com/*": 6
      },
      "Diff": {                               ## Position difference for specified period
        "www.domain.com/*": 0
      },
      "Diff1": {                              ## Position difference for 1-day period
        "www.domain.com/*": 94
      },
      "Diff7": {                              ## Position difference for 1-week period
        "www.domain.com/*": 94
      },
      "Diff30": {                             Position difference for 1-month period
        "www.domain.com/*": 94
      },
      "Vi": {
        "20171116": {
          "www.domain.com/*": 0.000000
        },
        "Diff": {
          "www.domain.com/*": 0.000000
        }
      },
      "Sf": {
        "20171116": [
          "img",
          "rev",
          "kng"
        ]
      },
      "Tr": {
        "20171116": {
          "www.domain.com/*": 0.430000
        }
      },
      "Tc": {
        "20171116": {
          "www.domain.com/*": 0.370000
        }
      },
      "Lu": {                                   ## Landing URLs ##
        "20171116": {
          "www.domain.com/*": "https://www.domain.com/keyword-url.html"
        }
      },
      "Lt": {
        "20171116": {
          "www.domain.com/*": [
            "org"
          ]
        }
      }
    }

комментарии я добавил сам, чтоб было понятнее

Я планирую накапливать в БД следующие данные:
- keyword
- tag
- дата запроса
- позиция
- урл
- search volume ключевика
, чтоб в будущем формировать таблицы и графики с фильтрацией всех ключевиков по отдельному тегу.

[Grut]

Сообщение от веломан

лучше скрипт импорта поправь
почему-то дата отсутствует и позиции разные
кстати по дефолту должен быть NULL, т.е. создаёшь просто:
added date (added = название колонки)
это на будущее, потому что тут пустышек быть не может

Вместо отсутствующей даты, должна была быть дата 2017-12-05, от этого и позиции разные.

Как переделать скрипт импорта сейчас раздумываю. Спасибо.

[xUnicornx]

сделай чтобы запоминало в переменную последнюю не пустую дату, потом ее подставляй тогда, когда пусто.
Или, если у тебя date - это дата запроса к semRush, тогда проставляй нынешнюю дату. date('Y-m-d')

[Ёся]

решив проблему с датой, проблема избыточности данных останется, если бд в будущем будет большая + добавляться новый функционал то лучше изначально сделать более менее оптимизированную структуру таблиц. Допустим ты проверяешь 1000 ключей в день и что каждый день записывать эти одинаковые ключи в таблицу да плюс еще и теги...
А если у ключа несколько тегов будет (если такое возможно) то что делать, в колонке с тегом через запятую перечислять теги каждый день? А если ты к ключу захочешь добавить к примеру его частотность, что делать тогда? поэтому, моё мнение, изначально для ключей лучше сделать отдельную таблицу, возможно и не только для ключей..

[Hector]

Grut, ты можешь четче сказать что ты хочешь? Я не понял что ты собираешься делать в итоге. Дай пару примеров.

https://m.habrahabr.ru/post/193380/

[Grut]

Промежуточная цель - это автоматический сбор позиций сайта (c учетом категоризации ключевиков тегами) из сервиса SemRush Projects в БД для последующего формирования различных дашбордов с помощью BI-инструментов типа PowerBI или GoogleDataStudio.

Ну и все осложняется тем, что я ни разу не программист, а seoшник и seo-аналитик. И потому, задаю вероятно глупые вопросы. Сорри за это.

Сообщение от Ёся

решив проблему с датой, проблема избыточности данных останется, если бд в будущем будет большая + добавляться новый функционал то лучше изначально сделать более менее оптимизированную структуру таблиц. Допустим ты проверяешь 1000 ключей в день и что каждый день записывать эти одинаковые ключи в таблицу да плюс еще и теги...
А если у ключа несколько тегов будет (если такое возможно) то что делать, в колонке с тегом через запятую перечислять теги каждый день? А если ты к ключу захочешь добавить к примеру его частотность, что делать тогда? поэтому, моё мнение, изначально для ключей лучше сделать отдельную таблицу, возможно и не только для ключей..

Вот-вот, и меня настигают такие же вопросы. Изначально предполагается, что для каждого ключевика будет только один тег, но Семраш позволяет присваивать несколько тегов, потому и такую вероятность надо учитывать.

Частотность я также хочу трекать. Тем более, что частотность ключевика может меняться из месяца в месяц. И если собирать данные частотности всех отслеживаемых ключевиков для одного проекта, то в будущем можно будет прогнозировать сезонность тех или иных групп ключевиков. Для ecommerce seo - это офигенно полезная штука.

[веломан]

дубли ключевиков апдейтить надо, а не множить
там же только позиция меняется
просто по самому ключевику поиск делай или освой ON DUPLICATE KEY UPDATE