Система бронирования для гостиницы - иерархические ограничения / Проектирование БД

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Система бронирования для гостиницы - иерархические ограничения

46 сообщений из 46, показаны все 2 страниц

все

Система бронирования для гостиницы - иерархические ограничения

#37683435

Uniquad

Гость

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

vacancies (
vacancy_id,
parent_vacancy_id,
vacancy_name
);

booking_orders (
booking_order_id,
date_start,
date_end
);

bookings (
booking_order_id,
vacancy_id
);

Очевидно, что если забронировать комнату на втором этаже в период с 5 мая по 10 мая, то весь второй этаж, а также домик целиком забронировать на этот период уже не получится.
Также очевидно и обратное, что если на этот период забронировать домик целиком - то нельзя будет забронировать ни одну из комнат и ни один из этажей этого домика.
Вопрос: каким образом реализовать эти запреты в БД?
Я использую PostgreSQL, и без иерархической структуры это можно сделать простым применением CHECK. Но так как в моем случае проверяться должны ещё потомки и предки - я зашел в тупик.
Точнее, я загнал в тупик БД, создав такой триггер, который использует блокироровки и при нескольких конкурентных запросах создает deadlock :)) (хотя, в документации к PostgreSQL написано, что СУБД умеет справляться с дедлоками, убивая одину из конкурирующих транзакций)
Если блокировки не использовать - то возникают ситуации, когда бронирования накладываются друг на друга. Я знаю, почему это происходит, но не могу понять, как от этого избавиться.

Мой триггер имеет вид after insert on bookings for each row execute procedure ...

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

...

Рейтинг:

0 / 0

29.02.2012, 03:56

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683439

Uniquad

Гость

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

...

Рейтинг:

0 / 0

29.02.2012, 04:15

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683452

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

29.02.2012, 06:00

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683455

Uniquad

Гость

Количество иерархий неограничено. Система должна быть как можно более универсальной. Может случиться и такое, что заказчик захочет каждую койку в комнате по-отдельности бронировать, это должно быть предусмотрено. Я думаю, единственным условием сделать то, чтобы заказчик четко вбил иерархическую структуру, и больше не менял её во время использования. При желании её как-то расширить ему придется создавать новую структуру. То есть, был домик с двумя комнатами, если он хочет сдавать там койки - то пусть создаст в БД новый домик с двумя комнатами и койками, а не добавляет койки в уже готовую структуру. Хотя, если можно найти простое и элегантное решение, чтобы гибко изменять структуру во время эксплуатации без побочных эффектов - было бы совсем хорошо. И волки сыты, и овцы целы :) Но мне кажется, это будет очень сложно, придется отслеживать очень много зависимостей.
Хотя, вот родилась идея использовать массивы для хранения ID всех зависимых единиц. Может, что-то из этого получится. И гибкое, и надёжное :) Пятый час утра - а я тут сижу реализовываю, чтоб не забыть как проснусь ))

...

Рейтинг:

0 / 0

29.02.2012, 06:15

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683457

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Единица бронирования день?
Если так то можно создать таблицу

Код: sql

1.
2.

create table booking (day_id date, room_id int references rooms
 primary key (day_id,room_id))

...

Рейтинг:

0 / 0

29.02.2012, 06:18

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683458

Uniquad

Гость

Ещё сразу уточню, что в БД могут быть иерархии с разными степенями вложения. То есть, может быть просто однокомнатный домик (то есть, просто либо домик, либо комната, без зависимостей), может быть двухкомнатный домик, может быть двухэтажный домик с двумя комнатами на первом этаже, тремя на втором, и еще чердаком. То есть, все зависит от фантазии заказчика :) И всё это одновременно должно находится в БД, не конфликтовать друг с другом и правильным образом отслеживаться при бронировании.

...

Рейтинг:

0 / 0

29.02.2012, 06:19

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683460

Uniquad

Гость

SERG1257Единица бронирования день?
Если так то можно создать таблицу

Код: sql

1.
2.

create table booking (day_id date, room_id int references rooms
 primary key (day_id,room_id))

и избавится от проверочного триггера как класса (приложение вставит по записи на каждую комнату каждого дня при бронировании на верхнем уровне) а ошибку будет генерить нарушение первичного ключа

Единица бронирования - произвольный период, измеряемый в днях. То есть, с 5 по 15 мая. Или с 3 июня по 6 сентября.

...

Рейтинг:

0 / 0

29.02.2012, 06:20

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683469

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Если дни, то количество строк будет не слишком большое (часы или минуты были бы гораздо хуже)
надо бы добавить в таблицу booking поле book_id со ссылкой на бронирование, чтобы не только добавлять строки, но и удалять (снимать бронирование)

Про иерархии
http://www.osp.ru/pcworld/2007/03/4199032/
Выбирайте любой способ

...

Рейтинг:

0 / 0

29.02.2012, 06:49

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683471

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Так же надо будет предусмотреть механизм удаления (архивации) исторических записей (скажем старше трех месяцев) из рабочей таблицы

...

Рейтинг:

0 / 0

29.02.2012, 06:51

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683478

Uniquad

Гость

SERG1257,

За ссылочку большое спасибо, нашел там ответ на один нужный вопрос.
А насчет архивации - это да, уже думал об этом. Кроме архивации там ещё много интересных фишек можно сделать, чтоб снизить нагрузку на сервер. Но оптимизацией производительности я буду заниматься позже, сейчас бы заставить эту махину работать :)

...

Рейтинг:

0 / 0

29.02.2012, 07:38

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37683666

Uniquad

Гость

Ещё одна вещь, которую я не понимаю: у меня есть триггер вида:

create constraint trigger trig_checkBookingPeriod after insert on bookings FROM booking_orders for each row execute procedure checkBookingPeriod();

если в него добавить строку
LOCK TABLE bookings IN SHARE MODE;
То возникает deadlock при попытке вставить строку в таблицу несколькими параллельными потоками. Если поток один - то все работает нормально. Откуда берется дэдлок? В самой процедуре триггера никаких записей ни в какие таблицы не делается, только чтение для проверки консистенции. Есть догадка, что он может возникать в случае, если проверка в треггере не проходит и выстреливает исключение (exception), что приводит к откату транзакции. Тогда системой из таблицы удаляется запись, которая не прошла проверку валидности, для этого делается LOCK IN EXCLUSIVE MODE, а это уже может вызвать дэдлок. В общем, понятно, что ничего не понятно =/

...

Рейтинг:

0 / 0

29.02.2012, 10:38

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37684945

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Uniquad Ещё одна вещь, которую я не понимаю: у меня есть триггер вида:Это в соседний форум .

Uniquad Я думаю, единственным условием сделать то, чтобы заказчик четко вбил иерархическую структуру, и больше не менял её во время использования. При желании её как-то расширить ему придется создавать новую структуру. То есть, был домик с двумя комнатами, если он хочет сдавать там койки - то пусть создаст в БД новый домик с двумя комнатами и койками, а не добавляет койки в уже готовую структуруПопробуйте договорится с заказчиком и ограничить число уровней иерархии. Пусть будут не три, а пять или скажем десять. Если при этом детали типа маленький домик имеет один этаж, одну комнату, один угол, одну кровать, одну подушку будут скрыты от пользователя интерфейсом, то у пользователя может сложится впечатление, что количество иерархий неограничено (в пределах установленного максимума).
Я могу сделать автомобиль любого цвета, если этот цвет черный (с) Генри Форд.
Иначе вам придется парсить каждое бронирование на лету, приводя его к общему знаменателю.

...

Рейтинг:

0 / 0

29.02.2012, 18:35

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37684976

softwarer

Участник

Откуда: 127.0.0.1

Сообщения: 51 965

Рейтинг: 0 / 0

UniquadЕщё сразу уточню, что в БД могут быть иерархии с разными степенями вложения.
Да не суть, имхо. В любом случае вопрос довольно прост, имхо: при создании блокировки X следует создать также каскадные блокировки X1, X2, X3 на "родителя", "дедушку" и прочее вплоть до корневого объекта. Да и с диапазонами вряд ли стоит возиться, можно размножать записи по дням периода. Обеспечивать корректность блокировки, соответственно, будет простой unique. Ну а количество записей в таблице блокировок.. зависит от объекта, конечно, но на глаз будет никак не больше ста тысяч.

...

Рейтинг:

0 / 0

29.02.2012, 18:59

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37684989

Uniquad

Гость

softwarer,
Каскадные блокировки не рулят. Если у родителя есть два потомка, и один потомок каскадом заблокирует родителя, то второй останется незаблокированным, но заблокировать его уже будет нельзя, так как родитель заблокирован другим потомком. То есть, в доме 2 комнаты, одну бронирут - вторая остается свободной, но первая каскадом бронирует родителя, в результате свободную вторую забронировать уже нельзя.

...

Рейтинг:

0 / 0

29.02.2012, 19:10

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685302

Гость

Uniquad,

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

...

Рейтинг:

0 / 0

29.02.2012, 23:27

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685458

Uniquad

Гость

ЫUniquad,

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

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

...

Рейтинг:

0 / 0

01.03.2012, 01:49

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685488

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

Uniquad А как раскидывать потом клиентов, чтобы они вдруг не оказались в номере с какими-нибудь левыми чуваками? Или если приезжает большая группа людей, и они хотели бы все остановиться на одном этаже?Это как раз проще всего реализовать как массовой вставки комнат при бронировании этажа.
таблица бронирования содержит ссылки на листья дерева, хуже если по желанию клиента лист дерева превращается в ветку. Вариант softwarera с разделяемыми блокировками частичным бронированием, на более верхних уровнях заслуживает рассмотрения. Только ошибку должен будет возвращать триггер ибо unique по определению не сможет работать на нескольких частичных блокировках (или надо будет разделить таблицы с монопольными блокировками и разделяемыми). А если триггер, то надо глубоко копать pg на предмет когда транзакция начинается, что она видит, и как все это настраивается.

...

Рейтинг:

0 / 0

01.03.2012, 02:22

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685492

ChA

Участник

Откуда: Москва

Сообщения: 10 913

Рейтинг: 0 / 0

Uniquadsoftwarer,
Каскадные блокировки не рулят. Если у родителя есть два потомка, и один потомок каскадом заблокирует родителя, то второй останется незаблокированным, но заблокировать его уже будет нельзя, так как родитель заблокирован другим потомком. То есть, в доме 2 комнаты, одну бронирут - вторая остается свободной, но первая каскадом бронирует родителя, в результате свободную вторую забронировать уже нельзя.Рулят. Блокировки бывают не только монопольными(X), но и разделяемыми(S). На монопольно заблокированный объект нельзя наложить никакую другую блокировку, но разделяемых блокировок может быть на объекте сколько угодно. Комната 1 имеет монопольную блокировку, а дом и выше - разделяемую. Комната 2 - не сможет получить монопольную блокировку, если сдан её угол, так как она уже будет иметь разделяемую блокировку, с которой монопольная несовместима. Если же блокировок на ней нет, то на комнату 2 накладывается монопольная блокировка, а на "предков" - разделяемая. Т.е., на доме в таком случае будет 2 разделяемые блокировки. При снятии блокировки на любую комнату у всех её предков счетчик количества разделяемых уменьшается на 1. Соответственно, дом нельзя заблокировать монопольно, пока на нем есть хоть одна блокировка любого вида. Счетчик разделяемых блокировок необязателен, чисто для удобства. Если 0, значит "внизу" никто не блокировал.
В общем, это азы. Можно сделать на любой вкус и цвет, главное хорошо продумать.

...

Рейтинг:

0 / 0

01.03.2012, 02:26

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685540

Uniquad

Гость

Фух, ребята, вроде бы наконец-таки нарыл решение. Всем спасибо за информацию, было очень поучительно для меня пообщаться со знатоками БД. Я в этом вопросе не особо опытен :)
Оказалось, что триггер проверки на целостность нужно было явно объявить как constraint trigger deferrable initially deferred. Тогда проверка производится после окончания транзакции, и накладок не возникает. И не понадобились никакие принудительные блокировки.
Пока гоняю тесты, и, скрестив пальцы, жду результатов) Но по первым впечатлениям всё работает (тьфу-тьфу-тьфу) :)

Если кому интересно, могу сказать, что периоды я реализовал в виде геометрического типа BOX((start_day, start_day), (end_day, end_day)), и проверяю их на пересечение, пользуясь оператором && (start_day/end_day высчитываю, отнимая '2000-01-01' от даты начала бронирования).
Кроме того, в отдельном поле типа Array я храню айди всех предков и потомков узла, чтобы каждый раз при проверке целостности не обходить дерево вызовом дополнительных SELECT. Массив с айдишниками предков заполняется автоматически всё теми же триггерами, всё это очень красиво и элегантно выглядит (за это я и люблю программирование :)) ).

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

...

Рейтинг:

0 / 0

01.03.2012, 03:25

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685546

Uniquad

Гость

И еще, может, кому пригодится: в интернете нашел совет человека, сталкивавшегося с такой же проблемой, но только без иерархической структуры. Он для проверки пересечений обошелся единственным полем BOX, в котором одним из измерений является ID комнаты. Очень элегантное решение, и его же можно использовать в версии postgresql 9.0 с их новой фишкой exclude constraint.
Выглядит это как BOX((start_day, room_id), (end_day, room_id+0.5)). 0.5 добавляется, чтобы прямоугольник не оказался нулевой высоты(или ширины).
Всё гениальное просто :))

...

Рейтинг:

0 / 0

01.03.2012, 03:31

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685560

ChA

Участник

Откуда: Москва

Сообщения: 10 913

Рейтинг: 0 / 0

Это ППЦ, а не решение, расстрел воробьёв из пушек. Хорошо, если это всего лишь учебное задание, за коммерческое - увольнение без выходного пособия без права работы за компьютером, как минимум, 5 лет.

...

Рейтинг:

0 / 0

01.03.2012, 04:10

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685562

Uniquad

Гость

ChAЭто ППЦ, а не решение, расстрел воробьёв из пушек. Хорошо, если это всего лишь учебное задание, за коммерческое - увольнение без выходного пособия без права работы за компьютером, как минимум, 5 лет.
В чем пипец? Почему воробьев из пушек? Как тогда сделать лучше? Критика неконструктивна, предложи решение лучше :)
Про монопольные и разделяемые блокировки я думал, но проблема в том, что у нас есть периоды блокировки, которые начинаются и заканчиваются в произвольное время. Единственное решение, которое я вижу для этой схемы - это создавать запись для каждого отдельного дня. Но тогда количество записей в таблице будет огромным.

...

Рейтинг:

0 / 0

01.03.2012, 04:39

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37685581

ChA

Участник

Откуда: Москва

Сообщения: 10 913

Рейтинг: 0 / 0

UniquadПро монопольные и разделяемые блокировки я думал, но проблема в том, что у нас есть периоды блокировки, которые начинаются и заканчиваются в произвольное время. Единственное решение, которое я вижу для этой схемы - это создавать запись для каждого отдельного дня. Но тогда количество записей в таблице будет огромным.Прочитал первый топик. Забираю свои слова обратно, в данной задаче блокировки действительно не рулят, точнее рулят, но об этом ниже.
Вся необходимая информация уже есть и её можно получить одним, хотя и не самым простым запросом. Иерархическим, если PostgreSQL поддерживает, или простым, но тогда надо "разворачивать" иерархию в структуру данных, которая позволит обойтись неиерархическим запросом. Не надо никаких дополнительных фиксаций бронирования, которое, по сути, ничем не отличается от пребывания, кроме даты начала, которая принадлежит "будущему", и которая либо становится пребыванием, либо "удаляется", т.е., исключается из проверок.
Предполагается, что все данные непротиворечивы на момент добавления новой брони, а в момент самого добавления(попытки) просто проверяем, есть ли старая бронь, которая может этому помешать. Собственно всё и не надо никаких ограничений. Если мешает, то наносим отказ, в противном случае - добавляем. Вот в этот момент блокирование может понадобится. Сама же проверка вполне выполнима одним запросом, хотя повторюсь, не самым простым, но на практике вряд ли слишком дорогим, если только дело не касается огромной международной сети. Но даже в этом случае легко ограничить количество данных для проверки идентификатором отеля. Вряд ли кто забронирует всю сеть.
И последнее. Какой смысл несёт таблица "bookings" ? Насколько понял задачу, поле "vacancy_id" спокойно можно перенести в таблицу "booking_orders". Бронирование(пребывание) разве не связано 1-к-1 с местом ? Или допустимо бронировать одним ордером одновременно несколько произвольных объектов ? Но тогда даты надо переносить в таблицу "bookings", так как в этом случае на произвольный объект могут быть совершенно разные периоды. Нет ?

...

Рейтинг:

0 / 0

01.03.2012, 07:13

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37686210

Гость

UniquadЫUniquad,
Единицей бронируемого места всегда должна быть наименьшая из возможных, т.е. койка. Тогда домик будет суммой коек, расположенных в нем. В этом случае вы сможете обслужить клиента, которому потребуются, напр., просто 10 коек, и неважно в скольких домиках/комнатах они окажутся.
Да, это было бы проще всего. А как раскидывать потом клиентов, чтобы они вдруг не оказались в номере с какими-нибудь левыми чуваками? Или если приезжает большая группа людей, и они хотели бы все остановиться на одном этаже?

Я бы попробовал сделать так:
таблица неделимых единиц бронирования (Койки);

таблица с иерархией домик—этаж—комната и т.п. (Вместилища);

Каждое Вместилище связано с id своего родителя (можно вместо этого использовать ltree). Койки привязаны к листьям Вместилищ. Бронирование происходит только для Коек, а Вместилища используются только для поиска. Таким образом бронирование Домика №10 превращается в бронирование всех коек в Домике №10 на одно имя, а в интерфейсе это может выглядеть (и называться) бронированием Домика.

...

Рейтинг:

0 / 0

01.03.2012, 13:17

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37686620

Uniquad

Гость

ChA,

Смысл таблицы bookings как раз в том, что один заказ может включать несколько единиц. Период у всех один и тот же. Например, 4 человека хотят приехать в отель с 1 по 10 мая и остановиться в 2-х разных номерах.
Но даты я продублировал в bookings (опять же, триггером перед вставкой), чтобы при проверке не обращаться к таблице booking_orders. Для оптимизации.
Сейчас моя таблица booking_orders выглядит так:

Код: sql

1.
2.
3.
4.
5.
6.
7.

create table bookings (
  booking_order_id BIGINT NOT NULL,
  vacancy_id BIGINT NOT NULL,
  related_vacancy_ids bigint[] NOT NULL, -- автоматически вставляется триггером из таблицы vacancies
  period_box BOX NOT NULL, -- автоматически высчитывается триггером из booking_orders.start_date/booking_orders.end_date
...
);

Ы,

Да, видимо, так будет гораздо проще следить за целостностью данных. Я отказался от этой идее в самом начале, потому что хотелось, чтобы вся работа проводилась "за сценой", чтобы вся процедура бронирования со стороны клиента свелась к простому INSERT INTO booking_orders / INSERT INTO bookings. Интересно, а какие плюсы у такого подхода по сравнению с тем, что я уже реализовал? Ну, кроме того, что можно избавиться от триггера на проверку противоречий и использовать ecxlude constraint.

...

Рейтинг:

0 / 0

01.03.2012, 16:03

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37686822

ChA

Участник

Откуда: Москва

Сообщения: 10 913

Рейтинг: 0 / 0

UniquadСмысл таблицы bookings как раз в том, что один заказ может включать несколько единиц. Период у всех один и тот же. Например, 4 человека хотят приехать в отель с 1 по 10 мая и остановиться в 2-х разных номерах.Тогда Вы сами создаёте совершенно ненужное ограничение. Более, чем вероятно, что любой из этих 4 человек захочет продлить или укоротить срок брони. И, вообще, здесь есть нюансы. Бронировать могут несколько мест сразу организация, она же и будет расплачиваться за них по ордеру, а другой случай, когда кто-то из компании забронирует несколько номеров для друзей. В этом случае платить будет не он, а каждый сам за себя и, полагаю, каждый по своему ордеру. IMHO, вам надо досконально разбираться с предметной областью, прежде чем проектировать БД.

UniquadНо даты я продублировал в bookings (опять же, триггером перед вставкой), чтобы при проверке не обращаться к таблице booking_orders. Для оптимизации.Это называется преждевременная оптимизация. Выигрыш от такой, как правило, 0 и 0 десятых, не говоря уж о дублировании данных, так как надо будет контролировать изменение периода в обоих таблицах сразу. Золотое правило - один факт должен хранится в одном месте. Сначала сделайте правильную схему, потом уж думайте об оптимизации. Ещё раз повторюсь, нет никакой необходимости в дополнительной фиксации факта бронирования. Впрочем, я уже всё написал в предыдущем посте.

UniquadСейчас моя таблица booking_orders выглядит так:

Код: sql

1.
2.
3.
4.
5.
6.
7.

create table bookings (
  booking_order_id BIGINT NOT NULL,
  vacancy_id BIGINT NOT NULL,
  related_vacancy_ids bigint[] NOT NULL, -- автоматически вставляется триггером из таблицы vacancies
  period_box BOX NOT NULL, -- автоматически высчитывается триггером из booking_orders.start_date/booking_orders.end_date
...
);

На мой взгляд, абсолютно негодная реализация, но дело ваше. Грабли тоже.

...

Рейтинг:

0 / 0

01.03.2012, 17:16

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37686955

Uniquad

Гость

ChA,

Вы когда-нибудь пользовались Booking.com? Там все просто и очевидно: заходишь, вбиваешь количество человек и количество номеров, дату заезда и дату выезда, и бронируешь их одним заказом. Правда, там нет никакой иерархической структуры, все просто и гладко. Отель только дает информацию о количестве номеров каждого типа, и потом сам занимается расселением по номерам. Мне нужно сделать что-то в этом роде, но чтобы при этом программа сама автоматически раскидывала клиентов по нужным номерам, и можно было просмотреть таблицу заполненности номеров в любой промежуток времени, а также карту заезда-выезда клиентов на любую дату. Это для администрации отеля, естественно, а не для туристов :)

ChAБолее, чем вероятно, что любой из этих 4 человек захочет продлить или укоротить срок брони.
Обычно, если большая компания едет отдыхать, то они стараются забронировать номера в одном отеле поближе друг к другу. По крайней мере, мы с друзьями так делаем :) И приезжаем и уезжаем все вместе. Бронирует один человек по интернету, рассчитываются каджый за себя по приезду на место. Либо же кто-то один бронирует и оплачивает весь заказ по интернету, а остальные потом рассчитываются с ним. Если же в каком-то отеле нет необходимого количества свободных мест - мы просто выбираем другой отель. Именно для этого я и делаю таблицы booking_orders и bookings, а не одну общую таблицу.

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

ChAНа мой взгляд, абсолютно негодная реализация, но дело ваше. Грабли тоже.
Предложите же уже, наконец, Ваш вариант :) Какую реализацию Вы бы посчитали годной? Я для того и создал этот топик, чтобы люди делились опытом и мнениями, а не просто говорили, что "это негодно" :)

...

Рейтинг:

0 / 0

01.03.2012, 18:33

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37687034

ChA

Участник

Откуда: Москва

Сообщения: 10 913

Рейтинг: 0 / 0

UniquadПредложите же уже, наконец, Ваш вариант :) Какую реализацию Вы бы посчитали годной?Вот хотел же добавить в прошлом посте примечание, что здесь за Вас никто ничего делать не будет, но понадеялся, что Вы это уже и так поняли. Похоже, что зря. Как правило, обсуждаются только некие специфические особенности, с которыми столкнулся разработчик БД, никто за Вас полностью всю схему БД рисовать не будет. Если Вы в себе не уверены, то лучше пойти на форум Работа , выложить ТЗ и озвучить сумму. Здесь Вы можете рассчитывать только на пинки, иногда в правильную сторону. Доброжелательное консультирование оплачивается отдельно и опять же не здесь. Всё, что хотел до Вас донести, было уже сделано. Sapienti, уж извините, sat.
Добавлю ещё пару замечаний, последних. Использование типа box для работы с периодами глу...неумно, запросы на пересечение периодов решаются элементарно без всяких специфических типов. Использование типа array в РСУБД, как правило, неверно в принципе, это прекрасно решается введением дополнительной таблицы, с которой легко и просто работать обычными запросами. Раньше это вообще считалось нарушением 1НФ, так как значение поля становилось неатомарным, можно было обращаться к её отдельному элементу. Сейчас, с лёгкой руки некоторых товарищей, обычно - любителей ООБД, это явление стало почти нормой, но de facto, а не de jure. В вашем конкретном случае, это прекрасно заменяется стандартной таблицей (Leaf, Node) "разворачивания" иерархии, где для каждого потомка указаны все его предки. Сколько предков, столько и записей. Иногда полезно, для некоторых специфических задач, включать и самоё себя как предка. В результате все иерархические запросы можно сделать без поддержки такой возможности конкретной РСУБД. Ну и мелочь, непринципиально, но глаз режет. Зачем нужно использовать BIGINT ? Вы хорошо представляете, что такое 8-байтовое число ? Вы собрались забронировать каждый атом вселенной ?

...

Рейтинг:

0 / 0

01.03.2012, 19:25

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37687054

SERG1257

Участник

Сообщения: 2 932

Рейтинг: 0 / 0

UniquadЕсли кому интересно, могу сказать, что периоды я реализовал в виде геометрического типа BOX((start_day, start_day), (end_day, end_day)), и проверяю их на пересечение, пользуясь оператором && (start_day/end_day высчитываю, отнимая '2000-01-01' от даты начала бронирования).Наши микроскопы - лучшие микроскопы для забивания гвоздей
авторКроме того, в отдельном поле типа Array я храню айди всех предков и потомков узлаНарушение первой нормальной формы - прогулка по граблям. Что самое забавное? то грабли имеют большую вероятность пройти тестирование, так что шишки будут набивать пользователи.
UniquadЯ знаю, что поле типа box можно индексировать при помощи gistВы знаете как работает этот самый gist под капотом, какие там ограничения, best practice и т.д.? Если нет - то вот вам еще один источник граблей, причем опять же легко проходящий тестирование. Где гарантия, что разработчики gist не скажут "упс у нас тут ошибка замечена" (руководство по прыжкам с парашютом. издание второе исправленное).
UniquadЕдинственное решение, которое я вижу для этой схемы - это создавать запись для каждого отдельного дня. Но тогда количество записей в таблице будет огромным.Давайте подсчитаем - размер записи будет day_id два байта, room_id (или bed_id) четыре байта (хватило бы и двух, но пусть будет четыре), booking_id четыре байта - итого 10 байт
пусть у нас сто тыщ самых мелких объектов (надеюсь этого хватит) на год это будет 35 миллионов строк или 350 метров - копейки в наше время. Если удалять старые записи то таблица больше расти не будет. Конечно надо набросить накладные расходы на заголовки блоков, покопаться в pg на тему организации таблицы в виде индекса (iot в Oracle, clustered index в MS SQL, ?? в pg) и т.д. но в общем вполне себе подъемная величина.
И в отличие от вариантов с проверками триггером абсолютно надежная. Триггер можно отключить, сделать черное дело и включить снова и никто не заметит разницы, а ограничение просто не включится.
Далее, вы уже отловили странные ошибки с дедлоками, вызванные непониманием работы СУБД под капотом. А теперь представьте ситуацию когда вам надо решать: или допускать редкие накладки или допускать редкие дедлоки. Они будут редкие: результат нелепого стечения обстоятельств - воспроизвести этот баг будет очень трудно (пользователь ни за что не расскажет что произошло), но для заказчика ситуация будет однозначной - ошибки есть, программист о них знает, но ничего не делает (обычные отмазки - у меня все работает или во всем виноват вендор (глюкавая винда, кривой pg ...) для заказчика не катит абсолютно)

Итого вот вам моя схема, о ней же писал Ы. (great minds think alike ей богу на английском звучит куда лучше русского аналога)

Код: sql

create table calendar (day_id smallint primary key, calendar_date date ....)

причем если хранить day_id как количество дней с к-л. круглой даты (н-р 01.01.2000) то можно из даты легко вычислять day_id. Заполните эту таблицу хоть на 10 лет вперед. Отмечайте там выходные и праздники, рекламные акции и скидки - короче пригодится

Код: sql

1.
2.
3.
4.
5.

create table level1 (level1_id int primary key, descr varchar(100))
create table level2 (level2_id int primary key, level1_id int references level1, descr varchar(100), isVisible bit)
create table level3 (level3_id int primary key, level2_id int references level2, descr varchar(100)  isVisible bit)
create table levelN (levelN_id int primary key, level(N-1)_id int references level(N-1), descr varchar(100)  isVisible bit)
create table leaf (leaf_id int primary key, levelN_id int references levelN, descr varchar(100)  isVisible bit)

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

Код: sql

create table booking_orders (booking_orders_id int primary key, dfrom date, dto date, who varchar(10), booking_detail ...)

Это собственно заказы.

Код: sql

1.
2.
3.

create table bookings (day_id smallint references calendar, leaf_id int references leaf,
 booking_orders_id int references booking_orders
 primary key (day_id, leaf_id))

это собственно бронирование по дням. Плюсом такой таблицы является то что вы легко строите по ней отчет (например для поиска свободного дня для объекта, или объектов для определенной даты) ДО собственно бронирования.

Бронирование уровня иерархии выглядит так -
открываем транзакцию
вставляем в booking_orders, запоминаем booking_orders_id в переменную new_booking_orders_id
пытаемся вставить в bookings

Код: sql

1.
2.
3.
4.
5.
6.
7.

insert into bookings (day_id,leaf_id,booking_orders_id)
select day_id,leaf_id,new_booking_orders_id
from calendar, leaf, levelN, level(N-1) ....
where calendar.calendar_date between dfrom and dto
and leaf.levelN_id=levelN.levelN_id
and levelN.level(N-1)_id=level(N-1).level(N-1)_id
....

если вставилось завершаем транзакцию, если нет откатываем, показываем сообщение пользователю. Если перед этим пользователь полазил по таблице bookings на предмет свободных дней/комнат, то ошибка возникает только если кто-то это делал параллельно и успел быстрее. Короче ситуация достаточно редкая, чтобы не раздражать пользователя.
Чтобы не терять данные в таблице booking_orders (вводили вводили имя бронирующего, данные его кредитки и т.д.) можно не вставлять запись в транзакции, а изменять статус у записи например с "принят" на "подтвержден"

dixi

...

Рейтинг:

0 / 0

01.03.2012, 19:36

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37687172

Uniquad

Гость

ChA...здесь за Вас никто ничего делать не будет..
Да я и сам это прекрасно понимаю. Я и не прошу ничего делать за меня, я просто спросил, какое решение вы находите годным. Вот товарищ Ы предложил решение, не рисуя никакой готовой схемы, а вы просто заявили, что моё не годится. Поэтому я несколькими постами ранее и написал, что ваша критика неконструктивна. Без обид.
ChAИспользование типа box для работы с периодами глу...неумно, запросы на пересечение периодов решаются элементарно без всяких специфических типов
Именно поэтому я здесь, на этом форуме, в этом топике. В интернете я нашел несколько человек, которые решали данную задачу с помощью box, и решил, почему бы и мне этим не воспользоваться. Понятно, что это забивание гвоздей микроскопом, и я сам этого не люблю. Но ведь работает же, причем достаточно быстро и надёжно. Вот мне и интересно стало услышать мнение экспертов, _какие минусы_ в подобных вещах. А то, что это глу... неуместно - у меня у самого хватает здавого смысла додуматься :)
ChAИспользование типа array в РСУБД, как правило, неверно в принципе, это прекрасно решается введением дополнительной таблицы...
Это я тоже осознаю. В первой версии БД не было никакого array, я просто обходил всю иерархию рекурсивным запросом, но посчитал, что это слишком большой минус к производительности, и поэтому решил "кэшировать" айди всех зависимых узлов в массив. "Упреждающая оптимизация" :) Причем, об отдельной таблице для этого я тоже думал, но решил, что массив будет гораздо эффективнее. Теперь вижу, что эксперты категорически против таких костылей :)
SERG1257Далее, вы уже отловили странные ошибки с дедлоками, вызванные непониманием работы СУБД под капотом.
От принудительных блокировок я отказался, теперь никаких дедлоков) Я вообще их использовал только как крайнюю меру, просто для проверки, сработает или нет. Это было, когда я уже перепробовал всё остальное :) Но потом я выяснил, что просто триггер должен быть объявлен отложенным, и выполняться в конце транзакции, и это сработало. Единственное, что меня сейчас тревожит - это то, что триггеры действительно можно незаметно отключить, и тогда прощай целостность.

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

У меня на уме сейчас 2 схемы организации иерархии: либо вынести все минимальные единицы бронирования в таблицу leafs, либо же в самой таблице vacancies создать поле is_leaf. И при бронировании родителя просто бронировать все принадлежащие ему leafs, как предложил товарищ Ы.
Что можете сказать о таком подходе?
Единственная вещь, которая меня смущает: если пользователь забронировал дом, то в таблицу бронирований будут добавлены все комнаты, принадлежащие этому дому. И в деталях заказа (например, в письме), он увидит список комнат. А бронировал он дом. Думаю, это его удивит :)) Как быть в этом случае? Пока на ум приходит создание в таблице bookings отдельного поля original_vacancy_id, в которую будет заноситься ID родительского объекта, который пользователь изначально бронировал. Но оно будет дублироваться для всех записей о забронированных комнатах. Это плохо с точнки зрения теории, или хорошо? Или не имеет значения?

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

Еще раз упомяну, что я программист, и поэтому, скорее всего, подхожу к вопросу проектирования БД по-программистски. Я не особо знаком со стандартами, правилами и общепринятыми подходами в области проектирования БД. Поэтому, мнение специалистов в этой области для меня очень ценно, так как заставляет взглянуть на вещи по-другому. За что вам всем большое спасибо)

...

Рейтинг:

0 / 0

01.03.2012, 21:23

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37696189

Uniquad

Гость

Всем привет.
Я полностью поменял структуру БД, отказавшись от произвольной иерархии. Теперь у меня есть номера, этажи и здания. Теперь я думаю над структурой календаря событий. Допустим, имеется таблица:

Код: sql

1.
2.
3.
4.
5.
6.

CREATE TABLE booking_calendar (
  booking_order_id BIGINT NOT NULL REFERENCES booking_orders,
  calendar_date DATE NOT NULL,
  room_id BIGINT NOT NULL REFERENCES rooms,
  PRIMARY KEY (calendar_date, room_id)
);

Вопрос в следующем: как с помощью стандартных средств SQL в данной таблице разрешить, чтобы дата заезда в конкретный номер могла совпадать с датой выезда предыдущего клиента? То есть, сейчас две записи в один день для одного номера запрещены ограничением PRIMARY KEY. Я думаю, нужно добавить ещё одно поле, что-то вроде event_type SMALLINT (1 - заезд, 2 - выезд, 0 - проживание). Но что делать с этим дальше - я не знаю. Если просто добавить это поле в PRIMARY KEY - получится совсем не то, что нужно. Нужно, чтобы разрешалось иметь две записи, но только если одна имеет тип 1 (заезд), а другая - 2 (выезд), во всех остальных случаях разрешается одна запись. Возможно такое, или придется использовать какие-то специфические для конкретной СУБД примочки?

...

Рейтинг:

0 / 0

08.03.2012, 02:35

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697138

АнатоЛой

Участник

Откуда: Киев, Украина

Сообщения: 2 910

Рейтинг: 0 / 0

Uniquad,

у тебя нельзя снимать одно место в двухместном номере?

...

Рейтинг:

0 / 0

09.03.2012, 14:44

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697146

АнатоЛой

Участник

Откуда: Киев, Украина

Сообщения: 2 910

Рейтинг: 0 / 0

Uniquad, весь геморрой от смешанных гостиничных суток: с 12:00 дня до 12:00 дня, правильно? для чего предназначена таблица:
1. Смотреть занятость номеров
2. Контролировать корректность

Оставь таблицу как есть -просто не добавляй в неё день выезда :)

...

Рейтинг:

0 / 0

09.03.2012, 14:57

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697172

Uniquad

Гость

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

Код: sql

1.
2.

CREATE UNIQUE INDEX booking_calendar_morning_events_idx ON booking_calendar(calendar_date, room_id) WHERE event_type = 3 OR event_type = 1;
CREATE UNIQUE INDEX booking_calendar_afternoon_events_idx ON booking_calendar(calendar_date, room_id) WHERE event_type = 3 OR event_type = 2;

По идее, эти индексы дальше могут понадобиться, но пока я не дошел до той части программы, где они будут нужны, поэтому, меня терзают смутные сомнения :)

...

Рейтинг:

0 / 0

09.03.2012, 15:35

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697243

Uniquad

Гость

Теперь я добрался до алгоритма поиска свободных номеров, и у меня возникла проблема. Допустим, юзер хочет найти на конкретный период домик (состоящий из двух номеров "Стандарт"), и ещё один номер "Стандарт". То есть, по сути, нужно найти 3 свободных номера "стандарт", но при этом 2 из них должны находиться в одном доме. Как это сделать оптимальнее всего? Что-то у меня в голове по этому поводу пока сплошная каша =/ Это делается одним запросом, или несколькими связанными/не связанными?

...

Рейтинг:

0 / 0

09.03.2012, 17:16

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697250

Uniquad

Гость

Наверное, я "недоформулировал" суть проблемы ) Например, домик немаленький, и состоит из 2-х номеров "Стандарт" на первом этаже, и 2-х номеров "Люкс" на втором. Например, нужно найти 3 таких свободных домика на конкретный период. То есть, нам нужно найти 6 номеров "Стандарт" и 6 номеров "Люкс", свободных в этот период. Но как удостовериться, что мы имеем именно столько именно таких номеров, и они не будут рассредоточены по более чем трем разным домикам? Чувствую, что тут каким-то образом замешан "building_id", но пока не могу понять, каким именно
Простейший путь, но, насколько я понимаю, не самый оптимальный - это просто проверить все домики в базе по-очереди на "свободность". А если, к примеру, юзер ищет два домика и ещё один номер, то найти 2 свободных домика путём поочередной проверки всех имеющихся, а потом искать свободный номер с учетом, чтобы он не принадлежал к уже найденным свободным домикам. Может, есть способ сделать это оптимальнее?

...

Рейтинг:

0 / 0

09.03.2012, 17:30

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697277

АнатоЛой

Участник

Откуда: Киев, Украина

Сообщения: 2 910

Рейтинг: 0 / 0

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

UniquadДни заездов и дни выездов нужны как минимум для отчётов по заездам и выездам гостей.
Сформулируй требования к отчётам. Неужели такая большая проблема их выдать, если день выезда не помечен?!

UniquadНу и потом, если планировать какие-то мероприятия в номере (например, номер нужно закрыть на день для ремонта), то могут возникать накладки, так как фактически в день выезда номер будет пустовать не с самого утра, а только после обеда.
Фактически в день выезда номер может быть занять до 12:00. А если заезд будет до 12:00 - накладок не будет?

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

UniquadПока что я вышел из ситуации с помощью двух выборочных (неполных) уникальных индексов
Эта фишка специфична для конкретной СУБД...

UniquadПо идее, эти индексы дальше могут понадобиться, но пока я не дошел до той части программы, где они будут нужны, поэтому, меня терзают смутные сомнения :)

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

...

Рейтинг:

0 / 0

09.03.2012, 18:22

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697281

АнатоЛой

Участник

Откуда: Киев, Украина

Сообщения: 2 910

Рейтинг: 0 / 0

UniquadНаверное, я "недоформулировал" суть проблемы ) Например, домик немаленький, и состоит из 2-х номеров "Стандарт" на первом этаже, и 2-х номеров "Люкс" на втором. Например, нужно найти 3 таких свободных домика на конкретный период. То есть, нам нужно найти 6 номеров "Стандарт" и 6 номеров "Люкс", свободных в этот период.

Определитесь, в каком виде будет оформлять запрос сам пользователь? Как 3 домика, или таки как 12 номеров...

UniquadНо как удостовериться, что мы имеем именно столько именно таких номеров, и они не будут рассредоточены по более чем трем разным домикам? Чувствую, что тут каким-то образом замешан "building_id", но пока не могу понять, каким именно

Среди всех подходящих по номерам множеств у искомого варианта количество уникальных building_id должно быть минимальным :)

...

Рейтинг:

0 / 0

09.03.2012, 18:36

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697293

Uniquad

Гость

АнатоЛойФактически в день выезда номер может быть занять до 12:00. А если заезд будет до 12:00 - накладок не будет?

Во сколько осуществляется заезд/выезд - решается администрацией гостиницы, и прописывается в правилах, например, на сайте гостиницы. Если гость приехал раньше - он тупо сидит в холе и ждет, пока его заселят, если номер ещё не освободился. Сколько раз ездил отдыхать в разные там египты/турции - везде именно так делают. Кроме того, встречал даже такое, что если хочешь заехать хотя бы на полчаса раньше установленного времени - доплачивай как за половину суток. Но это уже хозяин отеля сам решает, как ему поступать в таких случаях. Точно так же, как он сам решает, в котором часу у него заезд-выезд. Мне нужно просто контролировать ситуацию в БД, чтобы не возникало накладок более масштабного плана. Чтобы, например, гость не приехал сегодня, а номер освобождается только завтра, ну или наоборот, гость хочет заехать сегодня, номер к обеду освобождается, а система ему говорит, что номер занят.

АнатоЛойМожет тогда вводить понятие периода регистрации (check-in) и выезда (check-out)?
Ну, эти понятия и так введены :) Они регистрируются в таблице booking_orders. Изначально, таблица booking_calendar вообще не была спроектирована как "одна запись на каждый день пребывания". В ней просто указывалась дата заезда и дата выезда, а отсутствие накладок контролировалась триггером. И именно чтобы избавиться от триггера я пришел к нынешней структуре. Ну да ладно, худо-бедно, но это решается условными индексами. Вопрос только в том, не является ли контроль уникальности с помощью индексов "забиванием гвоздей микроскопом", как уже неоднократно выразились в этой ветке знатоки?

АнатоЛойОпределитесь, в каком виде будет оформлять запрос сам пользователь? Как 3 домика, или таки как 12 номеров...

В том-то и дело, что и так, и так. Это усложняет задачу, так как вносит много путаницы. Дело в том, что в гостинице, для которой изначально это проектируется, на данный момент есть домики, состоящие из двух номеров. Гость должен иметь возможность забронировать как номер в отдельности (если, скажем, он приезжает один или вдвоем с кем-то), так и домик целиком (если, например, приезжает семья из 4-6 человек). И это еще полбеды, но дальше гостиница планирует постройку разных домиков разных типов. Например, на 4 номера, на 6 номеров, на 16 номеров, одноэтажные, двухэтажные. И опять же, номера будут устроены так, что каждый домик можно будет снять как целиком, так и по отдельному номеру, и даже по отдельному этажу (например, второй этаж, состоящий из трех номеров). Вот тут у меня тоже возникал вопрос: давать ли возможность пользователю выбирать домики и этажи, или же ограничить его выбором количества необходимых ему номеров, а система сама пусть решает, будут ли находиться все эти номера в одном и том же домике на одном и том же этаже (по возможности). Я все же склоняюсь к тому, что пользователю будет гораздо удобней и понятней выбрать в списке "Хочу отдельный двухкомнатный домик", или "Хочу второй этаж вот такого домика", чем "Хочу две комнаты, желательно в одном домике, желательно на втором этаже", и потом сомневаться, получилось ли найти две комнаты в одном домике на одном этаже, или же они будут в разных, и будет ли это именно второй этаж.

АнатоЛойСреди всех подходящих по номерам множеств у искомого варианта количество уникальных building_id должно быть минимальным :)
Это я тоже понимаю :) Не понимаю только, как наиболее оптимальным способом найти искомый вариант) Должен же быть какой-то мега-запрос, который находит все эти множества и упорядочивает их по этому самому уникальному building_id )) При этом, не сильно нагружая систему. Так как, по всей видимости, одним отелем здесь всё не ограничится, скорее всего, в ней будет зарегистрировано много отелей, и их число будет постоянно расти.

...

Рейтинг:

0 / 0

09.03.2012, 19:12

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697309

Uniquad

Гость

Я пришел вот к такому запросу:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SELECT
  *,
  (
    SELECT COUNT(*) > 0
    FROM booking_calendar c
    WHERE
      calendar_date > '2012.05.01'
      AND calendar_date <'2012.05.10'
      AND c.room_id = r.room_id
  )
  AS booked
  FROM buildings
  LEFT JOIN floors USING (building_id)
  LEFT JOIN rooms r USING (floor_id)
  WHERE
    building_type_id = 28;

Он выводит мне такую таблицу:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

 floor_id | building_id | building_type_id |     building_name     | building_type_floor_id | room_id | room_type_id | room_idx | booked 
----------+-------------+------------------+-----------------------+------------------------+---------+--------------+----------+--------
       43 |          58 |               28 | Двухкомнатный домик 1 |                      8 |      86 |            7 | A-2      | f
       43 |          58 |               28 | Двухкомнатный домик 1 |                      8 |      85 |            7 | A-1      | f
       44 |          59 |               28 | Двухкомнатный домик 2 |                      8 |      88 |            7 | A-4      | f
       44 |          59 |               28 | Двухкомнатный домик 2 |                      8 |      87 |            7 | A-3      | f
       45 |          60 |               28 | Двухкомнатный домик 3 |                      8 |      90 |            7 | A-6      | f
       45 |          60 |               28 | Двухкомнатный домик 3 |                      8 |      89 |            7 | A-5      | f
       46 |          61 |               28 | Двухкомнатный домик 4 |                      8 |      92 |            7 | A-8      | f
       46 |          61 |               28 | Двухкомнатный домик 4 |                      8 |      91 |            7 | A-7      | f
       47 |          62 |               28 | Двухкомнатный домик 5 |                      8 |      94 |            7 | A-10     | f
       47 |          62 |               28 | Двухкомнатный домик 5 |                      8 |      93 |            7 | A-9      | f
       48 |          63 |               28 | Двухкомнатный домик 6 |                      8 |      96 |            7 | A-12     | f
       48 |          63 |               28 | Двухкомнатный домик 6 |                      8 |      95 |            7 | A-11     | f
(12 rows)

...

Рейтинг:

0 / 0

09.03.2012, 19:33

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697316

S.G.

Участник

Откуда: cartoon network

Сообщения: 30 700

Рейтинг: 0 / 0

UniquadНет, одно место в двухместном номере снимать нельзя. Номер - минимальная неделимая единица.Одно место может и нельзя, но один человек может снять весь двухместный номер. У вас такое предвидится?

UniquadНе добавлять в таблицу день выезда - странный ход. Очень даже нормальный ход. День выезда не считается днем проживания и не оплачивается. А день заезда- оплачивается как целый день.

...

Рейтинг:

0 / 0

09.03.2012, 19:43

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697320

Uniquad

Гость

S.G.Очень даже нормальный ход. День выезда не считается днем проживания и не оплачивается. А день заезда- оплачивается как целый день.
Хм... Что-то в этом есть, надо подумать :) Пока единственное возражение против этого - это, как я уже и писал, если нужно в день выезда запланировать какое-то мероприятие (например, ремонт), которое должно занять весь день, с утра до вечера. Тогда БД это позволит сделать (так как записи на этот день нет), но фактически это будет сделать невозможно, так как номер освободится только после обеда.

...

Рейтинг:

0 / 0

09.03.2012, 19:51

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697322

Uniquad

Гость

S.G.UniquadНет, одно место в двухместном номере снимать нельзя. Номер - минимальная неделимая единица.Одно место может и нельзя, но один человек может снять весь двухместный номер. У вас такое предвидится?

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

...

Рейтинг:

0 / 0

09.03.2012, 19:56

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37697815

Uniquad

Гость

UniquadЯ пришел вот к такому запросу:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SELECT
  *,
  (
    SELECT COUNT(*) > 0
    FROM booking_calendar c
    WHERE
      calendar_date > '2012.05.01'
      AND calendar_date <'2012.05.10'
      AND c.room_id = r.room_id
  )
  AS booked
  FROM buildings
  LEFT JOIN floors USING (building_id)
  LEFT JOIN rooms r USING (floor_id)
  WHERE
    building_type_id = 28;

Он выводит мне такую таблицу:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

 floor_id | building_id | building_type_id |     building_name     | building_type_floor_id | room_id | room_type_id | room_idx | booked 
----------+-------------+------------------+-----------------------+------------------------+---------+--------------+----------+--------
       43 |          58 |               28 | Двухкомнатный домик 1 |                      8 |      86 |            7 | A-2      | f
       43 |          58 |               28 | Двухкомнатный домик 1 |                      8 |      85 |            7 | A-1      | f
       44 |          59 |               28 | Двухкомнатный домик 2 |                      8 |      88 |            7 | A-4      | f
       44 |          59 |               28 | Двухкомнатный домик 2 |                      8 |      87 |            7 | A-3      | f
       45 |          60 |               28 | Двухкомнатный домик 3 |                      8 |      90 |            7 | A-6      | f
       45 |          60 |               28 | Двухкомнатный домик 3 |                      8 |      89 |            7 | A-5      | f
       46 |          61 |               28 | Двухкомнатный домик 4 |                      8 |      92 |            7 | A-8      | f
       46 |          61 |               28 | Двухкомнатный домик 4 |                      8 |      91 |            7 | A-7      | f
       47 |          62 |               28 | Двухкомнатный домик 5 |                      8 |      94 |            7 | A-10     | f
       47 |          62 |               28 | Двухкомнатный домик 5 |                      8 |      93 |            7 | A-9      | f
       48 |          63 |               28 | Двухкомнатный домик 6 |                      8 |      96 |            7 | A-12     | f
       48 |          63 |               28 | Двухкомнатный домик 6 |                      8 |      95 |            7 | A-11     | f
(12 rows)

При попытке добавить GROP BY building_id СУБД начинает ругаться, что в подзапросе используется room_id, который не участвует в группировании. Как-то можно сгруппировать полученный результат по building_id?

И всё же, кто-то может подсказать, как сгруппировать результаты по building_id? Или я на ложном пути, и нужно использовать какой-то другой запрос для поиска свободного домика? Я уже думаю о том, чтобы создать таблицу building_rooms (building_id, room_id), в которую заносить информацию о принадлежности номеров строениям, в обход таблицы floors. Это избавит от JOIN floors, но, насколько я понимаю, это не решит проблему с группировкой, так как в подзапросе используется поле room_id, которое в группировке не участвует.

...

Рейтинг:

0 / 0

10.03.2012, 15:51

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37728685

s u

Участник

Сообщения: 812

Рейтинг: 0 / 0

1. иерархическая структура

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

create table t_vac (vac_id number primary key,
                    name_tx varchar2 (255),
                    vac_rfk number
                   );
                 

ALTER TABLE T_VAC ADD 
CONSTRAINT C_VAC_RFK
 FOREIGN KEY (VAC_RFK)
 REFERENCES T_VAC (VAC_ID)
 ENABLE
 VALIDATE;
 
create table t_book (book_id number primary key,
                     start_dt date,
                     end_dt date,
                     vac_id number
                    );

ALTER TABLE t_book ADD 
CONSTRAINT C_VAC_FK
 FOREIGN KEY (vac_id)
 REFERENCES T_VAC (VAC_ID)
 ENABLE
 VALIDATE;

комнаты\этажи\дома

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

 insert into t_vac values (1, 'Home', null);
 insert into t_vac values (2, '1st floor', 1);
 insert into t_vac values (3, '2nd floor', 1);
 insert into t_vac values (4, 'Room 101', 2);
 insert into t_vac values (5, 'Room 102', 2);
 insert into t_vac values (6, 'Room 103', 2);
 insert into t_vac values (7, 'Room 201', 3);
 insert into t_vac values (8, 'Room 202', 3);

бронируем 202 комнату на 2-м этаже

Код: plsql

insert into t_book values (100, sysdate, sysdate + 10, 8);

доступен ли объект (:property_id) в данном промежутке (:book_start_dt :book_end_dt )

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

select case count(*)
         when 0 then 'Y'
         else 'N'
       end available_yn
  from t_book,
       (
         select *
           from t_vac
          start with vac_id = :property_id
        connect by vac_id = prior vac_rfk 
          union all
         select *
           from t_vac
          start with vac_id = :property_id
        connect by prior vac_id = vac_rfk
       ) t1_vac
 where t_book.VAC_ID = t1_vac.VAC_ID
   and (   :book_start_dt between start_dt and end_dt
        or :book_end_dt between start_dt and end_dt
       )
   and rownum = 1

в упрощенном варианте как-то так

...

Рейтинг:

0 / 0

29.03.2012, 01:21

| Ответить | Цитировать | Написать

Система бронирования для гостиницы - иерархические ограничения

#37730064

Uniquad

Гость

s u,

А что это за странная конструкция

Код: sql

1.
2.

start with vac_id = :property_id
        connect by vac_id = prior vac_rfk

Это изобретение Oracle? Или это стандарт, который просто не поддерживается PostgreSQL?

По поводу таблицы t_book - как в ней поддерживается целостность? Где гарантия того, что два юзера параллельно не забронируют одну и ту же комнату на один и тот же срок?

И насчет s uбронируем 202 комнату на 2-м этаже - я тоже вначале думал, что всё так просто. Но в реале юзер не бронирует комнату 202 на 2-м этаже, в реале юзер хочет забронировать 3 комнаты типа "стандарт", или второй этаж бунгало (состоящий из двух номеров "люкс") и ещё один номер "люкс" на первом этаже, как-то так. Это усложняет задачу, так как сначала нужно найти свободные номера, домики и этажи нужного типа, а потом их бронировать. При этом, если два юзера бронируют параллельно и их желания пересекаются, то получается каша, иногда ведущая к дедлокам. Поэтому я все-таки пришел к использованию advisory locks при бронировании.

...

Рейтинг:

0 / 0

29.03.2012, 17:35

| Ответить | Цитировать | Написать

46 сообщений из 46, показаны все 2 страниц

все

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=32&tid=1541767]:	0ms
get settings:	4ms
get forum list:	13ms
check forum access:	2ms
check topic access:	2ms
track hit:	140ms
get topic data:	9ms
get forum data:	3ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	197ms

total:	427ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы