Как спроектировать таблицу с динамическим набором колонок?

Question

[zlodiak]

Помогите пожалуйста спроектировать простейшую БД. Есть задача создать многопользовательский сервис для учёта тренировок бегунов в виде таблицы.

Таблица будет состоять из обязательной колонки "дата" и набором колонок типа: "длительность тренировки, мин", "дистанция, м", "средний пульс, уд/с", и т.д. Количество колонок и их имена определяет пользователь через UI в процессе работы с сервисом.

Я решил использовать реляционную БД типа mysql с такими таблицами:

1. Users:
- primary_key number,
- fname: string,
- lname: string,
- login: string,
- pass_hash: string,
- reg_date_unixstamp: number

2. Columns:
- primary_key: number,
- title: string,
- id_user: foreign_key

3. Values:
- primary_key: number,
- value: string,
- last_update_unixstamp: number,
- id_column: foreign_key,
- id_user: foreign_key

Таким образом, в UI пользователь при помощи datepicker будет выбирать интервал дат и бекенд возвращать результат запроса к таблице Values. Затем уже на фронте при помощи нехитрых манипуляций я буду ответ сервера отрисовывать в виде таблицы.

Проблема в том, что В таблицу Values используется id_user, а это избыточно потому что id_user уже есть в Columns. Помогите пожалуйста перепроектировать БД

Comments

[Kirill Mokevnin]

Как вариант https://en.wikipedia.org/wiki/Entity%E2%80%93attri...

Answer 1

[Виктор Янышев]

column_id за глаза

Comments

[zlodiak]

Я понимаю) Но ка в этом случае делать запрос к Values для конкретных user и column?

В качестве бекенда собираюсь использовать django с встроенным ORM. Но если приведёте схематичный пример запроса на чистом sql я тоже пойму

[Виктор Янышев]

Select * from user
Left join colums as c on c.user_id = user.id
Left join values as v on v.column_id = c.id
Where user.id = 123

[zlodiak]

Виктор Янышев, я понял. По сути вы предлагаете, просто избавиться от id_user: foreign_key в табличке Values

[Виктор Янышев]

zlodiak, её там не должно быть в принципе

[zlodiak]

Виктор Янышев, я прозрел. Спасибо)

Answer 2

[Андрей]

Откройте для себя json-тип)

Comments

[Adamos]

Если бы в окончательном варианте сервиса было так, как у ТС в ТЗ - возможно, json и был бы оправдан.
Но у него изложена ни к черту не годная логика, которая все равно работать не будет.
Еще и с json разбираться, переделывая?

[zlodiak]

Adamos, а почему моя логика работать не будет? Где возникнет проблема, на каком этапе?

[Adamos]

zlodiak, думаю, примерно на этапе "окей, поля я себе придумал. А че, заполнять их тоже мне, что ли? Тогда на хрена все это на компьютере?!"

[zlodiak]

Adamos, мне кажется, здесь трудностей не возникнет. На фронте, во время построения таблицы в DOM, я буду в атрибуте тега сохранять id столбца. id пользователя у меня будет храниться тоже где-нибудь, ну например в локалсторадже(на первое время только). Таким образом после отправки формы отработает запрос на вставку в таблицу Values записи с набором значений, получить которые не проблема

[Adamos]

zlodiak, вообще не понимаю, что вам мешает сразу делать свой гугломобиль.

[Андрей]

Adamos, ну, есть и такая схема. Проблема в потенциальной дороговизне alter на таблице.
А у схемы проблема будет в фильтрации по значениям в values.

[Adamos]

Андрей, просто Jason оправдан, когда ты готов вынести логику его разбора вне базы и уверен, что в запросах к базе она не понадобится.
А у ТС, похоже, с логикой работы с этими данными ещё чистое поле и никакого понимания, откуда они возьмутся и как будут обрабатываться.

[Андрей]

Adamos, 5.7 поддерживает операции над json-полями же

[Adamos]

Андрей, между "поддерживает" и "эффективное решение" - пропасть.

[Андрей]

Adamos, все слишком сильно зависит от архитектуры. если она не предполагает сложных операций с данными внутри БД либо требует гибкости структуры без возможности использовать что-нибудь типа монги...

[Adamos]

Андрей, ну, так в том и дело, что непохоже, чтобы у ТС была хоть какая-то архитектура. А советовать новичку, который не в курсе даже простейших решений, варианты, требующие архитектурного обоснования и понимания ограничений, согласитесь, нелогично.

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