Database Design - Items and Regions

Question

I have design problem of database structure. The goal is to have database for offers that our clients offer per some geographical region. Each offer can be offered in many regions.

The regions are in hierarhy - example:

subregion_1
  subregion_11
    region_111
    region_112
  subregion_12
    region_121
    region_122
subregion_2
  subregion_21
    region_221

Now I want to store in database the offer_1 and regions for that offer. I will give You 3 examples what I have to ahieve:

• when my offer_1 is stored in region_111 then I would like to display this offer when user are browsing the subregion_1, subregion_11 and region_111
• If offer_1 is stored in regions subregion_11 and region_121 then the offer should be displayed when user are browsing the subregion_1, subregion_11 and all branch of subregion_11, subregion_12 and region_121
• when my offer_1 is stored in subregion_1 then the offer is displayed on subregion_1 page and all branch under subregion_1.

Also I have to provide a way to calculate the number of diffrent offers in each regions dynamicaly and very fast. Does somebody have some advice how to aproach this design?

Here is what I have so Far.

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      |
------------------------

I created id for regions from concatenating level1, level2 and level3. In the table Offers_to_regions I store the offer and the region. Here I have region on level 3 (020103) and region on level 2 (0202) for offer 1. With this design I have problems how to query the numbers of difrent offers per region, and how to query offers for regions on level1, level2 and level3 regions.

Answer 1

Well there is the obvious way which uses an id to point to a parent like this

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)
);

But in your situation this could be considered an anti-pattern, since it is not so easy to query through the hierarchy (specially if the number of levels changes)

Another approach could be using something like Path Enumeration, where you store the hierarchy path similar to for example unix paths. E.g.

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

This will allow you to store your hierarchy like this

---------------------------------------------
| 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       |
---------------------------------------------

This way, when querying your offers table (where each offer will have a ref. to the region_id), and while browsing lets say offer for the subregion_1 (with id 1) your query can look something like this.

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

There are other patterns to model your hierarchical data, such as Nested Sets and Closure Table (maybe relevant) which you might be interested to look into as well. each has different pros and cons in terms of select/insert/delete performance

EDIT:

I just noticed you edited your question, also that offers could belong to more than one region. The above might need adjustments to support assigning more than one region, but the basic idea could still be applied.