Дизайн базы данных — элементы и регионы

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

Регионы находятся в иерархии - пример:

subregion_1
  subregion_11
    region_111
    region_112
  subregion_12
    region_121
    region_122
subregion_2
  subregion_21
    region_221

Теперь я хочу сохранить в базе данных offer_1 и регионы для этого предложения. Я приведу Вам 3 примера того, чего я должен достичь:

  • когда мое предложение_1 хранится в регионе_111, я хотел бы отображать это предложение, когда пользователь просматривает субрегион_1, субрегион_11 и регион_111.
  • Если предложение_1 хранится в регионах субрегион_11 и регион_121, то предложение должно отображаться, когда пользователь просматривает субрегион_1, субрегион_11 и всю ветвь субрегиона_11, субрегиона_12 и региона_121.
  • когда мое предложение_1 хранится в субрегионе_1, предложение отображается на странице субрегиона_1 и во всей ветке в субрегионе_1.

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

Вот что у меня есть до сих пор.

Regions
------------------------------------------------------------
| id     | level1 | level2 | level3 | name         | level |
------------------------------------------------------------
| 02     | 02     | null   | null   | subregion_1  | 1     |
| 0201   | 02     | 01     | null   | subregion_11 | 2     |
| 020103 | 02     | 01     | 03     | region_111   | 3     |
------------------------------------------------------------

Offers to regions
------------------------
| offer_id | region_id |
------------------------
| 1        | 020103    |
| 1        | 0202      |
------------------------

Я создал идентификатор для регионов, объединив level1, level2 и level3. В таблице Offers_to_regions храню оффер и регион. Здесь у меня есть регион на уровне 3 (020103) и регион на уровне 2 (0202) для предложения 1. С этим дизайном у меня возникают проблемы с тем, как запрашивать количество различных предложений для каждого региона и как запрашивать предложения для регионов на уровне 1, уровне 2. и регионы уровня 3.


database-design hierarchical-data regions
person Marcin Kapusta    schedule 02.12.2011    source источник
comment
Всегда полезно показать что-то, что у вас уже есть, а затем задать конкретные вопросы о том, где вы сейчас находитесь.   -  person Bert    schedule 03.12.2011
comment
С какой базой данных вы работаете?   -  person omarello    schedule 03.12.2011
comment
@BertEvans - я знаю. Я просто спрашиваю, потому что после одного дня размышлений об этом я не придумал никакого решения. У меня есть только иерархия регионов. Каждый регион имеет идентификатор и ссылку на родительский регион на уровне 1 и уровне 2. Существует 3 уровня. Я обновлю свой вопрос как можно скорее.   -  person Marcin Kapusta    schedule 03.12.2011

Ответы (1)


arrow_upward
1
arrow_downward

Ну, есть очевидный способ, который использует идентификатор, чтобы указать на родителя, как это

CREATE TABLE Regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    region_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES Regions(region_id)
);

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

Другим подходом может быть использование чего-то вроде перечисления путей, где вы сохраняете путь иерархии, аналогичный, например, путям unix. Например.

CREATE TABLE Regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY,
    path VARCHAR(100),
    region_name VARCHAR(100) NOT NULL
);

Это позволит вам сохранить вашу иерархию, как это

---------------------------------------------
| region_id | path       | region_name      |
---------------------------------------------
| 1         | 1/         | subregion_1      |
| 2         | 1/2/       | subregion_11     |
| 3         | 1/2/3/     | region_111       |
| 4         | 1/2/4/     | region_112       |
---------------------------------------------

Таким образом, при запросе таблицы ваших предложений (где каждое предложение будет иметь ссылку на region_id) и при просмотре, скажем, предложения для субрегиона_1 (с идентификатором 1), ваш запрос может выглядеть примерно так.

select Offers.SOME_COLUMN, ......
from Offers, Regions
where Offers.region_id = Regions.region_id
and   Regions.path like '1/%'

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

ИЗМЕНИТЬ:

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

person omarello    schedule 02.12.2011
comment
Но что, если предложение будет добавлено в базу данных с субрегионом_1 (id=1). Это предложение должно быть видно во всей ветке '1/%', но в этом случае оно будет видно только на странице subregion_1? - person Marcin Kapusta; 03.12.2011
comment
если предложение находится в субрегионе_1, должно ли оно отображаться при просмотре, например, регион_111, субрегион_11 и субрегион_1? Кроме того, если предложение находится в регионе_111, оно должно быть видно в регионе_111, субрегионе_11 и субрегионе_1? Если это так, то я больше не вижу необходимости в иерархии, чтобы быть отточенной?!?! Просто используйте отношение «многие ко многим», чтобы определить видимость предложения. - person omarello; 03.12.2011
comment
Да точно. Это для водителей и обслуживания пассажиров. Если пассажир ищет водителя в области_1, то он видит все предложения от водителей из этой области_1, так что это означает, что он видит предложения из меньших областей внутри области_1, но он не видел предложений из области_2, например, потому что это другая географическая территория. Когда водитель добавит предложение для области_1, его предложение будет видно во всем поддереве под областью_1. Но когда он добавляет предложение в область_1_1, которая является субрегионом внутри области_1, тогда, когда пассажир посещает сайт области_1, он должен увидеть предложение, потому что область_1 содержит область_1_1. - person Marcin Kapusta; 03.12.2011
comment
Также см. мой ответ, объясняющий таблицу закрытия на SO: -into-a-tree/192462#192462">Какой самый эффективный/элегантный способ преобразовать плоскую таблицу в дерево?. Для поддержки предложений во многих регионах вам просто понадобится таблица пересечений «многие ко многим», ссылающаяся на предложения и регионы. - person Bill Karwin; 03.12.2011
comment
@BillKarwin Ваша идея таблицы закрытия с небольшими изменениями - это именно то, что мне нужно. Сейчас работает очень быстро. Иерархия в моем сценарии не меняется со временем, потому что это регионы в стране, такие как провинция, район, город, поэтому я загружаю свой индекс таблицы закрытия в кеш Mysql, и это очень-очень быстрое решение :) Спасибо. - person Marcin Kapusta; 03.12.2011
comment
@Marcin: Да, Closure Table может быть очень быстрым, если вы определите правильные индексы покрытия. Какие индексы являются правильными, зависит от запросов, которые вам нужно выполнить. - person Bill Karwin; 04.12.2011