Прописка. Защита от дурака.

[dr.MIG]

Необходимо вести базу пользователей, одной из характеристик пользователя является «Прописка». При этом необходимо, чтобы без особых проблем из этой характеристики можно было извлечь область, город/населенный пункт, домашний адрес, почтовый индекс. Необходимо исключить возможные ошибки пользователей при указании этих характеристик. Для этого можно использовать выпадающие списки Область -> Город/Населенный пункт. Есть ли где в свободном доступе полная база с детализацией населенных пунктов вплоть до улиц (интересует только РФ), например как база на основе которой работает, например http://www.moscowindex.ru/rus/?

[MIT]

http://www.gnivc.ru/Document.aspx?id=80 — не оно?

[dr.MIG]

Кажется оно. Огромное спасибо!

[dr.MIG]

При изучении структуры данной базы данных возникли некоторые вопросы. Каждый объект там идентифицируется цифровой строкой: СС РРР ГГГ ППП АА, где
СС – код субъекта Российской Федерации (региона);
РРР – код района;
ГГГ – код города;
ППП – код населенного пункта,
АА – признак актуальности адресного объекта.

Однако подобная структура вызывает некоторые опасения по поводу быстродействия — не затянется ли выборка подчиненных элементов — ведь для этого придется использовать в запросе Like для 192 тысяч элементов, содержащихся в базе. И не оптимальнее ли будет заменить это поле двумя — id (идентификатор элемента), pid (указатель на id родительского элемента)?

[alibek]

Запрос Like для строк типа 'abc%' не сильно сказывается на производительности (при наличии индексов и с учетом того, что работа со строками медленнее работы с числами).
Замена на дерево вида ID-ParentID часть вопросов снимет, но добавит другие. Скажем, в таком дереве отобрать ветку со всеми потомками в одном запросе будет не совсем тривиальной задачей.
Так что выделяй задачи, в которых тебе нужны эти данные и сам определяй, какая схема для тебя будет оптимальной.

[Денис]

не затянется ли выборка подчиненных элементов — ведь для этого придется использовать в запросе Like для 192 тысяч элементов, содержащихся в базе.

Я давно уже с КЛАДР не общался. Но кажется там так и есть:

И не оптимальнее ли будет заменить это поле двумя — id (идентификатор элемента), pid (указатель на id родительского элемента)?

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

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

[Ramzes]

Запрос Like для строк типа 'abc%' не сильно сказывается на производительности (при наличии индексов и с учетом того, что работа со строками медленнее работы с числами).
Замена на дерево вида ID-ParentID часть вопросов снимет, но добавит другие. Скажем, в таком дереве отобрать ветку со всеми потомками в одном запросе будет не совсем тривиальной задачей.
Так что выделяй задачи, в которых тебе нужны эти данные и сам определяй, какая схема для тебя будет оптимальной.

Поддерживаю, лучше как минимум вынести все кода в отдельные поля, как максимум в отдельные таблицы (и привести БД к 3-ей нормальной форме)

[Antonariy]

Замена на дерево вида ID-ParentID часть вопросов снимет, но добавит другие. Скажем, в таком дереве отобрать ветку со всеми потомками в одном запросе будет не совсем тривиальной задачей.

C появлением CTE эта задача стала почти тривиальной, правда нужен 2008й MSSQL. А при использовании Nested Sets — чуть более, чем полностью, и база может быть любой.

[alibek]

Поддерживаю, лучше как минимум вынести все кода в отдельные поля, как максимум в отдельные таблицы (и привести БД к 3-ей нормальной форме)

Причем тут третья нормальная форма?
В записях четвертого уровня два родительских поля из трех избыточны. До 3НФ тут далеко.

C появлением CTE эта задача стала почти тривиальной, правда нужен 2008й MSSQL.

С одной стороны да. С другой — есть ведь и другие СУБД, помимо MSSQL2000, в которых такое дерево строить сложно.

[Antonariy]

помимо MSSQL2000

MSSQL2008
Я так и сказал. Но автор пока не озвучил название БД.

[dr.MIG]

Про NestedSets думал, но не хотелось бы заморачиваться здесь с этим в данном случае. Суть задачи сводится вот к чему: предоставить пользователю указать свое место жительства в строго указанном формате (регион-район-населенный пункт). Это необходимо для предоставления различных отчетов по регионам, районам или отдельным городам. Таким образом пользователь будет характеризоваться идентификатором населенного пункта. Т.е. выбор пользователей одного региона будет непростой задачей при стандартном подходе. Так может в рамках данной задачи проще не увлекаться нормализацией, а добавить пользователю вместо одной характеристики (идентификатор населенного пункта) сразу три — идентификатор региона, района и населенного пункта?

ADD: БД MySQL.

[Antonariy]

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

[Proxy]

и запретом ввода данных, которых нет в базе, наиболее оптимальный вариант.

Ну-ну... А где гарантии, что с момента появления базы в неком городе Урюпинске улица Копылёва не была переименована в Путинную и т.п. Это нерациональная загрузка технической поддержки проекта. Или где гарантии, что улица Блуцевич не была по ошибке написана как "ул. Блуцевича"? В моём городе такая улица в картах написана в первом варианте (т.к. Блуцевич таки девушка), во всяческих департаментах числится как ул. Блуцевича и т.д, а подписи на домах 50 на 50.

Ещё вопрос на засыпку (вероятно для прописки тоже имеет актуальность): связывались когда-либо с разработкой ГИС? Знаете ли вы, что в РФ связь физическое строение и адрес постройки - многие ко многим? Я вот недавно заметил, что большинство ГИС этого не предполагают и дома с двойными адресами в ГИСе имеют только один адрес. А так же существуют дома, имеющие один номер и один буквенный префикс (индекс ли)? Ещё на прошлом месте работы столкнулся с таким (два здания после реконструкции были объединены переходом. На карте это одно здание, а фактически их два. И нумерация квартир повторяется. Т.е. в одном доме квартира номер 2 встречается дважды).

[dr.MIG]

Ещё вопрос на засыпку...

Да, об этом задумывался, но к счастью сами улицы не так актуальны и вполне достаточно простого ввода в произвольном форме. И раз уж речь зашла о пользовательском интерфейсе и полях ввода, то там существует еще одно узкое место — указание школы, где обучается регистрирующийся. Что можно придумать здесь, чтобы иметь возможность отобрать учеников одной школы конкретного населенного пункта. Ведь как показала практика одну и ту же школу назовут СОШ № 13, МОУ СОШ № 13, средняя общеобразовательная школа 13 и т.п. Можно это как-то побороть?

[Денис]

Можно это как-то побороть?

В одноклассниках и вконтакте не побороли. Так что, ответ будет пессимистичным.

[Antonariy]

и запретом ввода данных, которых нет в базе, наиболее оптимальный вариант.

Ну-ну... А где гарантии, что с момента появления базы в неком городе Урюпинске улица Копылёва не была переименована в Путинную и т.п. Это нерациональная загрузка технической поддержки проекта. Или где гарантии, что улица Блуцевич не была по ошибке написана как "ул. Блуцевича"? В моём городе такая улица в картах написана в первом варианте (т.к. Блуцевич таки девушка), во всяческих департаментах числится как ул. Блуцевича и т.д, а подписи на домах 50 на 50.

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

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

Ведь как показала практика одну и ту же школу назовут СОШ № 13, МОУ СОШ № 13, средняя общеобразовательная школа 13 и т.п. Можно это как-то побороть?

Сделать жирнющую надпись, предписывающую вводить идентификатор школы начиная с цифр, типа "13, МОУ СОШ". И обеспечить это технически. Плюс T9 и изложенное выше.