Контроль непрерывности и непересечения интервала дат

← →
девушка (2008-09-27 08:14) [0]

Добрый день!
Разрабатывается БД на MS SQL 2005.

Исходные условия такие:
Есть некий набор Клиентов и некий набор Объектов.
Клиенты могут владеть Объектами в течении некоторого времени.
В течении одного промежутка времени Объект может находится во владении только у одного Клиента!
Могут появляться новые Объект. В момент появления или владеет уже какой-нибудь Клиент.
клиенты могут "передавать" друг другу Объекты.

Также Объект может менять свое состояние в то время, когда им владеет Клиент (может быть "закрыт", "приостановлен"). В один момент времени Объект может находится только в одном состоянии.

Принцип реализации:
Таблица КЛИЕНТЫ.
Таблица ОБЪЕКТЫ.
Таблица СОСТОЯНИЯ_ОБЪЕКТОВ (на оформлении,открыт, приостановлен, на закрытии, закрыт).

Считаем, что если Объект закрыт - то Клиент им уже не владеет.

Таблица СВЯЗИ_ПО_ВЛАДЕНИЮ(ИД_Клиента, ИД_Объекта, ИД_СОСТОЯНИЯ_ОБЪЕКТА, дата_начала_связи, дата_окончания_связи).

//Пример данных:

СОСТОЯНИЕ1(Клиент1, Объект1, на_оформлении,10.02.2007, 12.01.2007);
СОСТОЯНИЕ2(Клиент1, Объект1, открыт, 13.01.2007, 01.01.3000); - просто открыт навсегда

//Объект1 передают во владение Клиента2 с 18.03.2007:
СОСТОЯНИЕ2(Клиент1, Объект1, открыт,13.02.2007, 17.03.2007);
СОСТОЯНИЕ3(Клиент1, Объект1, закрыт,18.03.2007, 01.01.3000);

СОСТОЯНИЕ4(Клиент2, Объект1, открыт,18.03.2007, 01.01.3000);

//Т.е. при выборке за 14.02.2007 мы видим что Объект1 принадлежит Клиенту1.
//при выборке за 18.03.2007 Объект1 принадлежит уже Клиенту2, но известно что раньше он был у Клиента1.

В качестве типа данных использую datetime.

Стоит ли учитывать время? Т.е. при всех операциях выставлять
дата_начала_связи на 00:01 текущий даты, а дата_окончания_связи на 59:59 текущей даты?

САМЫЙ ГЛАВНЫЙ ВОПРОС: как в такой системе лучше поддерживать непрерываность и непересекаемость временных интервалов?

← →
девушка (2008-09-27 08:15) [1]

кросспост
http://sql.ru/forum/actualthread.aspx?bid=1&tid=599411&pg=-1

За год в таблице будет прибавляться примерно 50 000 записей

← →
Anatoly Podgoretsky © (2008-09-27 09:19) [2]

> девушка (27.09.2008 8:14:00) [0]

Почему дата окончания меньше даты начала? Опечатка? - 10.02.2007, 12.01.2007
Зачем 01.01.3000, проще и удобнее NULL
По времени, только если оно нужно в расчетах, но во всяком случае не так, как ты предлагаешь, а реальное время, тем более, что это ничего не стоит, тип поля в обязательном порядке содержит время. Но если расчетов со временем нет, то оставить часть время равной 00:00:00.000, любые расчеты будут проще.
Ну а на главный вопрос ответа нет - поскольку система не может, только на запросами, проверяя допустимость. Например для проверки закрыта или нет связь, можно выполнить запрос Select Count(*) FROM Objects WHERE ObjectID=:ObjectID AND EndDate IS NULL

← →
Anatoly Podgoretsky © (2008-09-27 09:23) [3]

Соответвенно проверка на открытие у кого либо

Select Count(*) FROM Objects WHERE ObjectID=:ObjectID AND StartDate IS NULL

← →
Виталий Панасенко(дом) (2008-09-27 09:25) [4]

IF (EXISTS (SELECT * FROM doc WHERE num_id=NEW.num_id AND id<>NEW.id and DOC_STATUS=1 AND ((period_beg <= NEW.period_end)AND (period_end >= NEW.period_beg)) )) THEN EXCEPTION "ЛЯ-ЛЯ-ЛЯ";
использую такой код в триггере BEFORE INSERT OR UPDATE в для FB2.1.1, попробуй "причесать" для себя
PERIOD_BEG - начало владения клиентом Обьекта
PERIOD_END - окончание владения.
У меня это ГГГГММ, у тебя я так понял ДатаВремя, но суть не меняется
Структура таблицы DOC
|CREATE TABLE DOC ( ID BIGINT NOT NULL, NUM_ID VARCHAR(10) NOT NULL COLLATE WIN1251_UA, SUMMA CURRENCY, DOC_STATUS SMALLINT DEFAULT 0, PERIOD_BEG VARCHAR(6) NOT NULL COLLATE WIN1251_UA, PERIOD_END VARCHAR(6) NOT NULL COLLATE WIN1251_UA, MONTHSBETWEEN SMALLINT, SROK_DATE DATE, FULL_SUMMA COMPUTED BY (summa * monthsbetween), OPIS_ID BIGINT DEFAULT 1 NOT NULL, CLIENT_ID BIGINT NOT NULL, DOC_DATE DATE NOT NULL, USER_ID VARCHAR(32) DEFAULT current_user NOT NULL, SUJET VARCHAR(50) COLLATE WIN1251_UA, REPSTATUS SMALLINT DEFAULT 0 NOT NULL );

← →
Виталий Панасенко(дом) (2008-09-27 09:28) [5]

И вопрос на счет "непрерывности" - обьект, что не может "простаивать"?(Не принадлежать никому кокое-то время?)

← →
девушка (2008-09-27 09:30) [6]

> Опечатка? - 10.02.2007, 12.01.2007

опечатка :(

> Зачем 01.01.3000, проще и удобнее NULL

А как будет в случае с NULL выглядеть запрос на то открыта ли связь в определенный день?
Например:

select * from table1_tmp
where
CURRENT_TIMESTAMP between date_beg and date_end

при данных

id_tmp date_beg date_end
1 01.01.2007 20.03.2007
2 21.03.2007 24.03.2007
3 25.03.2007 null

не дает ниодной записи

при данных:
id_tmp date_beg date_end
1 01.01.2007 20.03.2007
2 21.03.2007 24.03.2007
3 25.03.2007 20.10.2008

дает запись №3

← →
девушка (2008-09-27 09:31) [7]

> Виталий Панасенко(дом) (27.09.08 09:28) [5]
> И вопрос на счет "непрерывности" - обьект, что не может
> "простаивать"?(Не принадлежать никому кокое-то время?)

Считается что он принадлежит своему последнему хозяину, но находится в состоянии закрыт.

← →
Виталий Панасенко(дом) (2008-09-27 09:32) [8]

> девушка (27.09.08 09:31) [7]

Чем мой код не подходит? из
> Виталий Панасенко(дом) (27.09.08 09:25) [4]

← →
девушка (2008-09-27 09:38) [9]

>
> > Виталий Панасенко(дом) (27.09.08 09:28) [5]
> > И вопрос на счет "непрерывности" - обьект, что не может
>
> > "простаивать"?(Не принадлежать никому кокое-то время?)
>
> Считается что он принадлежит своему последнему хозяину,
> но находится в состоянии закрыт.

Для передачи другому Клиенту он должен быть переоформлен на него.
Еще объект может быть разрушен - но это регулируется другим статусом.

Наверное, стоит подробнне описать предметную область:
В городе есть здания. Некоторые площади могут принадлежать определенным Клиентам.
Здания отличаются адресами, для которых ведется отдельный справочник - справочник адресов с точностью до дома.

В здании могут быть некоторые площади, которые принадлежат разным владельцам. Например, жилой дом пренадлежит ЖЭУ, а вот некую квартиру на 1 этаже ЖЭУ сдает Аптеке. Эти площади и являются Объектами.

Если Клиент куда-то исчезает (расформировывается и т.д.) - то это уже несколько иная история.

> Виталий Панасенко(дом) (27.09.08 09:28) [5]
> И вопрос на счет "непрерывности" - обьект, что не может
> "простаивать"?(Не принадлежать никому кокое-то время?)

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

← →
девушка (2008-09-27 09:40) [10]

> Виталий Панасенко(дом) (27.09.08 09:32) [8]
>
> > девушка (27.09.08 09:31) [7]
>
> Чем мой код не подходит? из
> > Виталий Панасенко(дом) (27.09.08 09:25) [4]

Я и не говорю, что не подходит :) Просто отвечаю на вопросы.
Может быть еще и услышу что-нибудь интересное по поводу метафоры системы :)

← →
девушка (2008-09-27 09:41) [11]

В системе также есть понятие текущего периода - т.е. год и месяц.
Имеет ли смысл в Таблицу СОСТОЯНИЯ_ОБЪЕКТОВ вводить признак принадлежности состояния к текущему периоду?
Возможно при наложении дополнительного условия выборка данных по текущему периоду ускорится...

← →
девушка (2008-09-27 09:45) [12]

> PERIOD_BEG VARCHAR(6) NOT NULL COLLATE WIN1251_UA,
>
> PERIOD_END VARCHAR(6) NOT NULL COLLATE WIN1251_UA,
>

А с чем связан такой тип для периода? Выкинуть день из даты?

← →
девушка (2008-09-27 09:48) [13]

Кстати, к каждой зааписи СОСТОЯНИЯ_ОБЪЕКТОВ привязывается документ.
Некий договор с Клиентом.
В зависимости от состояния этого договора колбасит и клиента и связь...
вот только никак не могу продумать как красиво связать состояние Клиента и Договора... но там другая песня - договор может заключаться на одного клиента, владельцем может быть другой, а платить за все третий...

← →
Anatoly Podgoretsky © (2008-09-27 09:53) [14]

Я бы посоветовал ограничиться только одним и более специализированым форумом, не этим, а то все пойдет в разброд.
Null для EndDate означает закрытие, а 3000 год это неудачная иммитация этого NULL
Должно быть или NULL или реальная дата закрытия, зависит от предметной области. Если реальная дата, то NULL означает, что объект не закрыт. Если дата означает предполагаемое закрытие, то NULL означает, что объект закрыт, а не NULL, что просрочен. Поэтому надо разобраться с предметной областью. Но обычно NULL означает, что объект не закрыт, а проставленая дата может означать либо реальную дату закрытия, или дату действия в будущем и предназначена для автозакрытия. И поскольку есть статус, то всегда можно определить закрыт объект или просрочен. В этом случае пустая дата означает, что время окончания/действия не определено. Целостность определяется или триггерами или запросами, при смене статуса проставляется и дата окончания.

← →
Anatoly Podgoretsky © (2008-09-27 09:58) [15]

Да и зачем у тебя период непонятно, помоему ненужное дублирование информации, вся информация находится в Start/EndDate

Без точного описания/изучения предметной области советы будут неконкретные и вероятно ошибочные и будут тебя запутывать, особенно на данном форуме, поскольку он предназначен для обсуждения SQL и сервера. А часть Delphi сводится к .CommandText := "запрос"; А сам запрос к предметной области и к конкретному синтаксису, определенного сервера.

← →
Anatoly Podgoretsky © (2008-09-27 10:01) [16]

CURRENT_TIMESTAMP between date_beg and date_end OR date_end IS NULL

← →
девушка (2008-09-27 10:21) [17]

> Но обычно NULL означает, что объект не закрыт, а проставленая
> дата может означать либо реальную дату закрытия, или дату
> действия в будущем и предназначена для автозакрытия.
...
>В этом случае пустая дата означает, что время окончания/действия не >определено

> CURRENT_TIMESTAMP between date_beg and date_end OR date_end
> IS NULL

Пожалуй, вы правы.

> И поскольку есть статус, то всегда можно определить закрыт
> объект или просрочен.

Ну, NULL-ы тут не причем. Если предполагаемая дата закрытия объекта будет находится в таблице с документами, то сравнивать её с NULL или с 3000 годом - примерно одной сути задачи.

Просто во все проверки хвостом будет прилепляться OR date_end IS NULL...

← →
Виталий Панасенко(дом) (2008-09-27 11:06) [18]

> девушка (27.09.08 09:45) [12]

да, просто занятость определяется строго по месяцам

> девушка (27.09.2008 10:21:17) [17]

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

Может стоит подумать на тему объединения таблиц "СОСТОЯНИЯ_ОБЪЕКТОВ" и "СВЯЗИ_ПО_ВЛАДЕНИЮ"? Все само по себе и решится. ИМХО.

А зачем вообще дата закрытия??? И без неё можно прекоасно обойтись, раз уж объект "принадлежит старому хозяину" до "посинения"!
Объект в данный момент принадлежит тому, у кого дата date_beg максимальна, вот и всё! Чё огород с конечной датой городить? Лишняя инфа.

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

Непрерывность и непересекаемость дат в моём варианте поддерживается примитивно - новая дата ВСЕГДА больше предыдущей.

Контроль непрерывности и непересечения интервала дат Найти похожие ветки