Как правильно создать представление базы данных MySQL с несколькими подстолбцами?

Question

[NickOver]

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

spoiler

Вот так я пытался сделать БД

spoiler

Но вскоре понял, что не смогу выгрузить и выбрать отдельные номера маршрутов, смогу только работать по факту с данными станций и временами.
Запрос типа

SELECT `bus`, `stations`, `time_departure` FROM `table_name` WHERE (`time_departure` BETWEEN '00:06' AND '00:35') AND (`stations` LIKE 'Фрязики')

будет невозможен.

Прошу натолкнуть на правильную мысль или где можно посмотреть готовые подобные примеры.

Answer 1

[ThunderCat]

хранить время в формате времени, а не какой-то непонятной фигни

UPD:
во первых - вынести номера маршрутов в отдельную таблицу, во вторых - вынести в отдельную таблицу остановки. И тогда таблица будет выглядеть так:
id | route_id | station_id | arrival_time | departure_time

Comments

[NickOver]

Значения вставлены для примера, конечно они будут сохранены в формате TIME. Вопрос и трудности в другом, как правильно сделать БД из таблицы excel (представление под первым спойлером)

[ThunderCat]

NickOver, во первых - вынести номера маршрутов в отдельную таблицу, во вторых - вынести в отдельную таблицу остановки. И тогда таблица будет выглядеть так:
id | route_id | station_id | arrival_time | departure_time

Не надо смотреть на готовый результат как на образец структуры, важно понять какие сущности задействованы и как они связаны.

[NickOver]

ThunderCat, спасибо за ответ, я планировал разделить по сущностям и сделать отдельными таблицами маршрут, станции, а потом связать их внешними ключами. Но вытекает другая проблема, у меня около 300 таких маршрутов и как их всех легко импортировать из эксель таблицы в БД? Или только вручную заносить все данные по маршрутам, станциям, времени и связывать их FOREIGN KEY? Дайте совет, пожалуйста.

[ThunderCat]

NickOver, а кто сказал что будет легко? Я бы поменял екселевские файлики слегка, например добавил бы 2 поля номер маршрута вместо одного объединенного, чтобы был нормальный вид, затем экспорт в csv, а а дальше бы уже парсил скриптом по таблицам, ориентируясь на имена станций и номера маршрутов.

[NickOver]

ThunderCat, спасибо, буду теперь гуглить этот вариант парсинга, в эксель корректировки внесу, все равно таблицы делить надо будет, значит не так всё просто)

Answer 2

[Vitsliputsli]

schedule (id int serial, route_id int, station_id int, event int, event_time time) // расписание маршрутов.
routes (id int serial, route_name string) // названия маршрутов: 110,...
stations (id int serial, station_name string) // станции: Лебедский, ...
events (id int serial, event_name string) // события: Прибытие, Отправление.

Comments

[ThunderCat]

не думаю что для данной задачи целесообразно выносить евенты отдельно. В остальном полностью согласен.

[Vitsliputsli]

ThunderCat, можно, пусть автор решает, как ему удобнее, к примеру можно сделать events интом, но справочник не заводить в БД, а держать его только в коде. А в примере более-менее стандартная нормализация.

[NickOver]

Vitsliputsli, спасибо за ответ, но тут вытекает другая проблема, у меня около 300 таких маршрутов и как их всех легко импортировать из эксель таблицы в БД? Или только вручную заносить все данные по маршрутам, станциям, времени и связывать их FOREIGN KEY? Подскажите, пожалуйста.

[Мирон]

NickOver, а вы запомните список как CSV а потом его можно занести с помощью выражения LOAD, там пример есть.

[NickOver]

Мирон, благодарю за помощь и ответ, вариант с LOAD это что-то новое для меня, думаю интересное решение, буду пробовать и гуглить дальше.

[Vitsliputsli]

NickOver, 300 это совсем немного, можно в Excel сформировать запросы insert и выполнить их, если не хочется/нет времени изучать загрузку из файла.