Помогите собрать схему БД для расписания электричек.

[Dimka]

Можно было бы для наглядности и поле ID показать в первой таблице

Числовые ID в базах называются "фиктивные ключи", обладающие свойствами "первичных". В моих построениях используются настоящие первичные ключи. Затем, что так нагляднее показать концепцию. В реальной базе, конечно же, заводятся "фиктивные ключи".

Я бы наверное вторую таблицу делал зеркальную

Это избыточно. Достаточно по таблице построить view, который объединяет прямой и зеркальный порядок станций. Если же в таблице хранить все комбинационные варианты - это риск нарушить целостность данных, получив аномалии обновления и удаления.

1) это совокупность станций по пути следования из точки A в точку B по одной ветке, где A и B - конечные станции этой ветки. (т.е. маршрут электрички)
2) совокупность станций из точки A в точку B, где A и B - произвольные станции на схеме (т.е. машрут пассажира).

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

Справедливо. У меня только пункт 1. Пункт 2 лучше реализовать (включая поиск маршрутов электричек и пересадок) с учётом расписания и затрат времени, а не с учётом расстояний (особенно без обсуждения цены). Если прямой поезд ходит редко, может быть выгоднее с пересадкой на двух непрямых, но более часто ходящих поездах.

[Kivals]

Dale, Dimka, целосность тривиально решается введением тригера, который за этим следит. Но тем не менее в реальности возможна ситуация когда расстояние от А до Б не равно расстоянию от Б до А (одноколейные пути). Хотя на цену билета это по идее не должно влиять.

[Dale]

В данном случае с триггером может получиться не столь тривиально. Если триггер срабатывает на добавление строки в таблицу и сам добавляет строку в эту же таблицу, мы можем получить рекурсивный триггер, а это вряд ли то, что нам нужно, к тому же его поведение зависит от реализации и настроек сервера. Придется перед добавлением строки проверять, нет ли такой строки в базе. Те же проблемы будут при удалении и изменении данных.

Одноколейность в данном случае не является проблемой. Колея не является однонаправленной и используется поочередно в обоих направлениях. Насколько мне известно, основная часть железнодорожного пути вдоль черноморского побережья Краснодарского края имеет одну колею. Это снижает пропускную способность, но принципиальных проблем не создает.

У меня возникает аналогичная задача при поиске маршрута на графе, только вместо станций и путей у меня узлы коммутации и каналы связи. Если предположить, что упрощенная таблица маршрутов имеет вид RouteTable(P1, P2, Cost), где P1  и P2 - идентификаторы смежных узлов, а Cost - условная "цена" маршрута между ними, то ее легко симметрировать, введя представление:

Код:

SELECT P1, P2, Cost FROM RouteTable
UNION
SELECT P2, P1, Cost FROM RouteTable

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

[Kivals]

Я прекрасно понимаю, что информация будет избыточной, но ИМХО это ускорит работу с таблицей для вычисления "стоимости" всего пути (хотя могу и ошибаться). Что касается триггеров - то абсолютно согласен что нужно учитывать все события, а не только добавление (я и не говорю что это просто). Не знаю как в M$, а в PostgreSQL и Oracle можно узнать какое поле менялось (при on update): в Oracle вообще можно триггер к изменению поля привязывать.

Одноколейность в данном случае не является проблемой...

Я как раз имел в виду редкий случай когда одноколейная однонаправленная дорога, т.е. в разных направлениях едем по разному пути - тогда мы переходим к ориентированным графам

[Dale]

Многие СУБД поддерживают хранимые представления, которые по эффективности не уступают физическим таблицам (поскольку фактически и хранятся внутри БД в таких же таблицах). Так что остается выбирать вариант, обеспечивающий ту же степень дуракоустойчивости более наглядно и меньшими усилиями разработчиков. К тому же представления видны на традиционных схемах ERD, а триггеры - нет, нужно вникать в код.

[Алексей++]

А если:
пусть триггер сравнит ID1 и ID2 и переставит их так, чтоб они были ID1<ID2 (а случай ID1==ID2 - вообще обломать) . Тогда пар не будет

[Dale]

Пары реально нужны, поскольку мы хотим эффективно находить как путь из P1 в P2, так и путь из P2 в P1. Иначе клиенту, получившему отлуп при поиске маршрута по составному ключу (P1, P2), придется делать еще и попытку поиска по (P2, P1)

[RXL]

А может не морочить себе голову избыточным контролем целостности и оставить его на "совесть" ПО редактирования?

[Алексей++]

А может не морочить себе голову избыточным контролем целостности и оставить его на "совесть" ПО редактирования?

а вот я тоже за это ) Поэтому сначала и не понял, зачем два FK были созданы. А вот интересно - как лучше?

[RXL]

Леш, я не про FK, а про возражения Dale на предложение Kivals держать в таблице перегонов по одной записи для каждого направления (по две записи на перегон).

[Dale]

Если это учебный пример в две ветки по три станции в каждой, можно и не заморачиваться. В реальных системах очень часто к единой базе обращаются разные клиенты. И если сегодня данные вводятся через тщательно написанное клиентское приложение, которое гарантирует целостность данных, то завтра, возможно, в системе появится "дружественный" веб-интерфейс, в котором такое дублирование просто забыли сделать или сделали криво. Результат - падающее приложение, которое еще вчера устойчиво работало, затраты времени на поиск причины, а потом и на корректировку противоречивых данных.

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

[RXL]

Dale, "дружественный интерфейс" не будет сбоить, если  будет работать не напрямую с БД, а через прослойку сервера приложений, единого для все дружественных и недружественных интерфейсов.

[Dale]

То есть удвоением строк в данном случае будет заниматься специально выделенный под эту задачу сервер приложений? IMHO дороговатая цена за то, чтобы избежать простенького представления, которое полностью снимает проблему.

К тому же, возможно, я неправильно интерпретировал фразу:

А может не морочить себе голову избыточным контролем целостности и оставить его на "совесть" ПО редактирования?

Под "ПО редактирования" подразумевается сервер приложений или же клиентский интерфейс?

[Dimka]

Если речь идёт об ориентированном графе, некоторые рёбра которого двунаправленные, то, конечно, оба направления хранятся в таблице, они понимаются как два однонаправленных ребра. В данном случае такого нет и никогда не будет в будущем (направления описываются маршрутами, а не дорогой, в отличие от сети городских улиц с соответствующими знаками).

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

[Kivals]

поэтому предпочитаю надежные решения, в которых логика обеспечения целостности сосредоточена в едином месте, по возможности как можно ближе к базе данных

Поддерживаю всеми руками - нельзя такое оставлять на откуп клиентских приложений. И со всеми доводами по этому вопросу тоже полностью согласен.

[Man1BLR]

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

Тоже считаю, что должно быть именно так. И варинат с view тоже считаю самым оптимальным.

[x77]

Пары реально нужны, поскольку мы хотим эффективно находить как путь из P1 в P2, так и путь из P2 в P1. Иначе клиенту, получившему отлуп при поиске маршрута по составному ключу (P1, P2), придется делать еще и попытку поиска по (P2, P1)

не придётся, так как это разные маршруты. в одном случае считаем расстояния по одному маршруту, в другом случае - по другому.

я вообще, если честно, не понимаю, о чём все эти разговоры: про тригера, вьюхи, зеркальные таблицы...  маршрут А=>B и В=>А - это два разных маршрута, которые задаются руками в некоем конфигураторе. ну или втупую прописываются в базе.

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

[x77]

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

а ты молодец, кстати не самую тривиальную задачку себе выбрал, интересную, из которой (при желании) вытекает ещё куча задач, и комбинаторику, и на оптимизацию. можно же не просто выдать все маршруты, можно выдать самый быстрый маршрут, самый дешёвый, оптимальный по соотношению время/деньги и т.д.

респект, в общем

[Dimka]

Кстати, маршрут можно задавать и таким образом:

Маршрут	Этап	Станция отправления	Станция прибытия
Электричка 1	1	Молодечно	Олехновичи
Электричка 1	2	Олехновичи	Радошковичи

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

Поле "Этап" можно расширить на два поля "Этап в прямом направлении" и "Этап в обратном направлении", если один маршрут предполагает движение поезда в обоих направлениях по одной ветке железной дороги. Хотя это частный случай, так что такая структура данных скорее всего будет ненужным усложнением.

[Man1BLR]

можно же не просто выдать все маршруты, можно выдать самый быстрый маршрут, самый дешёвый, оптимальный по соотношению время/деньги и т.д.

Интересная идея... Обязательно учту

[Kivals]

Кстати, маршрут можно задавать и таким образом...

Я об этом и писал вначале:

...И в таблице 4 хранил бы ссылки на таблицу 2

[Dale]

я вообще, если честно, не понимаю, о чём все эти разговоры: про тригера, вьюхи, зеркальные таблицы...  маршрут А=>B и В=>А - это два разных маршрута

Речь в данном случае идет на самом деле о модели связности станций. Если есть некое звено пути от А к В, из этого неизбежно следует, что есть звено и от В к А, поскольку рельсы сами по себе односторонней проводимостью не обладают.

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

[RXL]

То есть удвоением строк в данном случае будет заниматься специально выделенный под эту задачу сервер приложений? IMHO дороговатая цена за то, чтобы избежать простенького представления, которое полностью снимает проблему.

И понижает универсальность. Я о том, что говорится в последних постах: обратный маршрут не обязан 100% совпадать с прямым, равно как и километраж, в случае, если колеи проложены по разным физическим маршрутам. Конечно, можно возразить, что Краснодарском крае и в пригороде Минска такого не бывает, но утверждать, что такого вообще не бывает, я бы не стал.

К тому же, возможно, я неправильно интерпретировал фразу:

А может не морочить себе голову избыточным контролем целостности и оставить его на "совесть" ПО редактирования?

Под "ПО редактирования" подразумевается сервер приложений или же клиентский интерфейс?

Я подразумевал административное ПО, через которое будет выполнятся занесение и изменение данных о перегонах, станциях, маршрутах и т.п. Это в противовес ПО для пользовательского доступа.

[Dale]

И понижает универсальность. Я о том, что говорится в последних постах: обратный маршрут не обязан 100% совпадать с прямым, равно как и километраж, в случае, если колеи проложены по разным физическим маршрутам.

Отнюдь не факт. Между станциями вполне может быть не одно звено пути, а несколько, возможно, даже разной длины. Никто не мешает прокладывать конкретный маршрут в одну сторону по одному звену, а в обратную - по другому. Как раз такой вариант наиболее универсален: если данное звено используется конкретным маршрутом лишь в одном направлении, это вовсе не мешает другому маршруту двигаться по нему же во встречном.

Я подразумевал административное ПО, через которое будет выполнятся занесение и изменение данных о перегонах, станциях, маршрутах и т.п. Это в противовес ПО для пользовательского доступа.

Это, конечно, дело вкуса, но лично я частенько корректирую данные напрямую с консоли сервера. Триггеры, которые предложил Kivals, в данном случае сработают. Вариант с view также сработает, поскольку он динамический. А вот сервер приложений будет не в курсе. Впрочем, это и неудивительно, поскольку серверы приложений в многоуровневых архитектурах обычно предназначаются для других задач.

[Man1BLR]

Таким образом у нас получается следующая схема для маршрутов и расстояний, если что-то не так или есть какие-то замечания поправляйте. КодРМС в таблице РасстояниеМеждуСтанциями был введен для упращения, возможно нужно было сделать первичным ключом - Станция1, Станция2, но мне кажется, что с ним (КодРМС) будет проще, кстати, хотелось бы узнать ваше мнение.

[Dale]

Ранее высказывалось справедливое замечание, что между двумя станциями в принципе может существовать несколько звеньев пути с разной длиной. Поэтому я бы переименовал таблицу РасстояниеМеждуСтанциями в ЗвеноПути (или, возможно, существует более точный железнодорожный термин для обозначения участка рельсового пути между соседними станциями). В этом случае составной ключ (Станция1, Станция2) не является уникальным, и нужно использовать суррогатный уникальный ключ для каждого Звена.

Тогда Маршрут описывается следующими атрибутами: КодМаршрута, НачальнаяСтанция, КонечнаяСтанция, а также связанный список звеньев пути. Для каждого звена Маршрута при необходимости можно добавить длительность стоянки после его прохождения (если 0, на станции не предусмотрена остановка).

[McZim]

Промежуточные точки маршрута (код маршрута) - зачем два ключа на этом поле?

[Man1BLR]

Промежуточные точки маршрута (код маршрута) - зачем два ключа на этом поле?

Ну с учетом последнего замечания от Dale, я немного переделал и схема выглядит так:

Думаю теперь понятно, что составной первичный ключ (RouteID, SectionID) нужен, потому что ни RouteID, ни SectionID не обеспичивают по отдельности уникальности + не хочется вводить суррогатный.

[Dimka]

Man1BLR, по-моему в Routes StartStation и FinishStation - лишние, а вот в RouteDescriptions явно не хватает порядкового номера "секции" маршрута.

[Man1BLR]

Вроде как справедливое замечание...

Dimka, хотелось бы уточнить по поводу ответа о создании зеркальной таблицы, в данном случае RailwaySections.

Это избыточно. Достаточно по таблице построить view, который объединяет прямой и зеркальный порядок станций...

Как видно из схемы таблица RouteDescriptions ссылается на таблицу RailwaySections, таким образом в RouteDescriptions присутствует ограничение внешнего ключа, а если мы построим представление, то

• нужно установить с ним (представлением) связь
• идентификатор (SectionID) не будет уникальным

Или я просто чего-то не знаю?