Как организовать данные улиц с одинаковыми названиями в разных городах в базе данных

Что проверяют

Умеете ли вы нормализовать данные и понимаете ли, что уникально не название, а пара «город и название». Сильный ответ идёт дальше и замечает, что города тоже повторяются. Следом обычно спрашивают, как это описать в JPA и какие индексы понадобятся.

Как отвечать

Базовая схема:

• cities: суррогатный ключ id bigint generated always as identity, название, ссылка на регион. Одноимённых населённых пунктов много, поэтому город определяется не именем, а местом в иерархии «страна, регион, город»;
• streets: id, city_id с внешним ключом на cities, тип улицы отдельной колонкой (улица, проспект, переулок) и название;
• уникальность: ограничение unique (city_id, type, name). Составной индекс под ним сразу обслуживает поиск улицы внутри города, потому что работает слева направо. Отдельный индекс по name нужен, только если ищут улицу по всем городам;
• написание: «Ленина ул.» и «улица Ленина» должны давать одну запись. Храните нормализованное название для поиска (нижний регистр, «ё» заменена на «е») или делайте уникальный индекс по выражению lower(name).

В JPA у Street поле @ManyToOne(fetch = FetchType.LAZY) City city. Уникальность можно продублировать в @Table(uniqueConstraints = ...), но источником правды остаётся ограничение в миграции Flyway или Liquibase.

Если приложение работает с реальными адресами России, не изобретайте справочник заново: у государственного адресного реестра ГАР, преемника ФИАС, есть устойчивые идентификаторы объектов, их удобно хранить рядом со своим ключом.

Где спотыкаются

Делают название улицы первичным ключом или уникальным полем. Вторая «Садовая» в другом городе просто не запишется.

Не учитывают, что и внутри одного города бывают одноимённые улицы, например после присоединения соседних посёлков. Тогда в иерархию нужен ещё уровень: район или населённый пункт внутри города.

Оставляют @ManyToOne без LAZY. По умолчанию связь загружается жадно, и список из тысячи улиц тянет за собой города лишними запросами.