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

[Man1BLR]

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

Помощь нужна в правильной организации таблиц маршрутов и расстояний.

Расстояния нужны для информации пользователей системы (Например, человек подошел к системе, ввел заданный пункт, система ему вывела на экран все маршруты на сегодня до этого пункта + расстояние до него), а также от расстояния зависит стоимость проезда, и хочется сделать так, чтобы система не зависела от конкретного пункта отправления (Например, если система будет находиться в Минске, то можно сохранять расстояние от Минска до пунктов движения - это плохой вариант). Теперь давайте поподробнее.

У нас есть следующая схема движения электричек

в данном случае минимизированная схема - для простоты. Цифры - это расстояние между пунктами.

Составленная мною схема БД для маршрутов выглядит так:

т.е. маршруты будут вводиться руками, промежуточные станции, которые включаются в этот маршрут, тоже, а вот расстояние между станцией отправления и станцией куда следует пассажир должно каким-то образом подсчитываться автоматически (Вот я у Вас и прошу помочь разобраться каким таким образом?). Навреное нужно добавить еще одну таблицу с расстояниями между двумя соседними пунктами, но я не могу понять как это организовать, поэтому спрашиваю у Вас. (Возможно нужно даже 2 таблицы добавить, а потом воспользоваться декартовым произведением или чем-нибудь в этом роде). Принимаются все варианты, даже очень сложные.

Если что-то непонятное написал уточняйте... Заранее спасибо.

[lapulya]

Первые 2 секунды просмотра выявили, что таблица "Станции" имеет внешний ключ на таблицу "Маршруты", что не верно, должно быть наоборот.


Для вычисления расстояний в таблицы "станции" добавь:
1. добавь поле "направление" (название линии) причем линия это любая ветка из Минска (т.е. противоположные красные ветки от Минска это разные направления)
2. поле "расстояние" и заноси туда расстояние от этой станции до Минска (т.е. для Олехновичи это будет 47).

Тогда расстояние между ЛЮБЫМИ станциями будет мериться так:
ЕСЛИ начальная и конечная станции маршрута лежат на разных ветках, то просто складываем значения поля "расстояние" из таблицы "станции" для начала и конца маршрута
ЕСЛИ же начальная и конечная станции маршрута лежат на одной и той же ветке, то берем модуль разности значений поля "расстояние" из таблицы "станции" для начала и конца маршрута

[lapulya]

Ой, сорри не прочитал, что такой вариант (храним расстояния от Минска) не катит, но все равно станция не должна содержать внешних ключей на маршрут.

Хотя... боюсь что уж совсем общий вариант сделать не получится... или от просто будет хардкорным. Т.е. можно пронумеровать станции в разрезе каждой ветки и потом высчитывать путь. Или для каждой станции хранить предшественника и последователя и по ним выстраивать маршрут и высчитывать расстояний.

Подробней описать сейчас времени нет. Вечером может подробней распишу.

[Dimka]

Нужно хранить несколько вещей.

1) Взвешенный граф дорожной сети. Это будет одна таблица - станции. Другая таблица - железная дорога между станциями.

Станция

Молодечно

Олехновичи

Радошковичи

Станция 1	Станция 2	Расстояние
Молодечно	Олехновичи	30
Олехновичи	Радошковичи	13

Это базовая структура информации. На неё накладываются маршруты.

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

Если маршрут задаётся переменным числом станций, то опять две таблицы:

Маршрут

Электричка 1

Маршрут	Порядковый пункт маршрута	Станция
Электричка 1	1	Молодечно
Электричка 1	2	Олехновичи
Электричка 1	3	Радошковичи
Электричка 1	4	Олехновичи
Электричка 1	5	Молодечно

Пример кольцевого маршрута.

Вычисление расстояния поездки по таблице маршрута и расстояний делается не очень сложным запросом.

3) Расписание лучше представить отдельной подсистемой данных и увязать её с маршрутами через вспомогательные таблицы.

[Man1BLR]

Dimka, суть понял, только не могу понять для чего таблица Станция в 1?

[Man1BLR]

lapulya, буду очень признателен, если подробнее опишешь свой вариант.

[Алексей++]

Dimka, а хорошо ли повторять имя станции во всех таблицах, разве не делается так: станциям присвоен уникальный ID, а дальше используются ID станций, а не имена ?
Или так делается только для огромных по объёму баз?

[Kivals]

Man1BLR, Алексей1153++, именно для этого и есть таблица Станция - во всех остальных таблицах именно ID, а названия в посте - для наглядности

[Man1BLR]

Kivals, ха))), только что хотел написать, что наверное Dimka для этого и создал таблицу станции))))

[Алексей++]

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

(да оптимизационный рефлекс сработал )

[Kivals]

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

Молодечно	Олехновичи	30
Олехновичи	Молодечно	30

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

[x77]

под маршрутом надо понимать две разные вещи:

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

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

[x77]

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

[Алексей++]

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

[Man1BLR]

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

Стоп. О поиске наикратчайшего пути на графе никто и не говорил. Задача системы достать из БД имеющиеся актуальные маршруты на данный момент и показать их пользователю, а пользователь уже сам решает какой маршрут он предпочтет.

[Man1BLR]

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

Задача построить реляционную БД, а не плоский файл.

[x77]

Man1BLR, да ну? у вас на упрощённой схеме 17 станций. на боевой, надо думать, поболее. если пассажир едет по одной ветке - нет вопросов, он просто выбирает ветку. а если ему надо попасть из Руденска в Борисов, например? у вас в базе такого маршрута нет. или вы собираетесь хранить все возможные маршруты между всеми станциями?

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

если это курсовая - сойдёт. если реальный проект - это не прокатит. слишком неудобно.

[Man1BLR]

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

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

Получается что да.

[Man1BLR]

У меня еще один небольшой вопрос по поводу решения Dimka. Получается что в таблице2 Станция1 и Станция2 - это внешние ключи ссылающиеся на таблицу1, так можно? 

[x77]

не можно, а нужно

[Алексей++]

там как-то вот так будет )

Станция	IDst (PK)
Молодечно	1
Олехновичи	2
Радошковичи	3

IDst1	IDst2	Расстояние
1	2	30
2	3	13

[Man1BLR]

Теоритически, между теорией и практикой нет различий.
Но на практике они есть. (Jan L.A. van de Snepscheut)

Дело в том, что 2 внешних ключа ссылающихся на 1 таблицу не может быть! Для примера возьмем 2 таблицы, которые составил Алексей1153++ выше. Получается, что IDst1 - это FK, который ссылается на столбец IDst, но и IDst2 - это тоже FK, который ссылается на столбец IDst, а такого не может быть, такой связи нигде нет и в MS SQL такой фокус провернуть нельзя.

Не верите, проверьте сами, файл сценария прилагается.

[x77]

IDst1 - это FK, который ссылается на столбец IDst, но и IDst2 - это тоже FK, который ссылается на столбец IDst, а такого не может быть

почему не может? всю жизнь так и было. какую ошибку M$ выдаёт, дословно?

[Man1BLR]

Ну если дословно то:

Сообщение 8148, уровень 16, состояние 0, строка 1
Более одного ограничения столбца FOREIGN KEY указано для столбца "Расстояние", таблица "ДорожнаяСеть".

Вот только причем тут столбец "Расстояние". 

[x77]

да при том, что вы все ключи определяете для него. смотрите синтаксис CREATE TABLE.

первичный ключ: job_id smallint PRIMARY KEY,
внешний ключ: job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id),

и т.д. у вас просто синтаксис кривой

[Man1BLR]

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

А по поводу синтаксиса, то я считаю, что объявление ограничений PK и FK в таком стиле очень даже привлекательно, хотя бы по 3-м причинам:

• упращается работа при создании первичного ключа, состаящего из нескольких столбцов (ведь первичный ключ с несколькими столбцами не может быть объявлен как ограничение столбца)
• ты контролируешь название внешнего или первичного ограничения
• проще понимать устройство оператора CREATE TABLE

Вот пример создания таблицы:

Код:

-- создание таблицы, которая задает расстояние между пунктами
CREATE TABLE ДорожнаяСеть
(
	Станция1		int				NOT NULL,
	Станция2		int				NOT NULL,
	Расстояние		int				NOT NULL,
	
	CONSTRAINT PK_ОтСтанции1ДоСтанции2
		PRIMARY KEY (Станция1, Станция2),
	
	CONSTRAINT FK_Станция1
		FOREIGN KEY (Станция1)
			REFERENCES Станции(КодСтанции),
	
	CONSTRAINT FK_Станция2
		FOREIGN KEY (Станция2)
			REFERENCES Станции(КодСтанции)
)
GO

Очень даже симпотичный синтаксис. В прочем на вкус и цвет товарища нет.

[Алексей++]

ох, и увлечение ключами. А нужно ли

Код:

	
	CONSTRAINT FK_Станция1
		FOREIGN KEY (Станция1)
			REFERENCES Станции(КодСтанции),
	
	CONSTRAINT FK_Станция2
		FOREIGN KEY (Станция2)
			REFERENCES Станции(КодСтанции)

? Вроде нафик

Я не очень часто с базами воюю, но интуиция мне подсказала так ) Или я не прав?

[Алексей++]

А, всё, понял. Целостность охота блюсти.

Только название таблице больше подойдёт не "ДорожнаяСеть", а "РасстояниеМеждуСтанциями" ))

[Kivals]

Алексей1153++, вообще-то для целосночти базы - желательно. Если такая связь есть - СУБД не даст удалить станцию из Станции, если на нее есть ссылки по FK в других таблицах. Иначе - останутся неразрешенные ссылки (при наличии FK легче обрабатывать exceptions).

[Dale]

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

Молодечно	Олехновичи	30
Олехновичи	Молодечно	30

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