CODEWITHSUNDEEP
mysqlsqldata-structures
flxh
flxh

Reputation: 565

Hierarchical structure with (My)SQL database

I want to create a website where items are only available in specific areas/regions. The regions/locations where these items are available are selected by the following pattern:

  1. Either there are states selected otherwise the item is available nation wide.
  2. For every selected state there might be counties selected otherwise it is available state wide.
  3. For every county there might be cities selected otherwise it is available county wide.

And so on... You probably got the idea.

Now my question is how would you implement such a structure with a MySql DB? Or is there an even better DB model with an suitable GUI ? (That would be perfect) The number of items is fairly small (~20).

When the customer is visiting the page he would enter his location, and every item available for his location shall be listed. How would a query for this purpose look like?

Many Thanks in advance, Flo

Upvotes: 1

Views: 479

Answers (2)

Tuncay Göncüoğlu
Tuncay Göncüoğlu

Reputation: 1767

Here you can find a rather detailed explanation of different approaches to the problem. Basically your options are a) construct a ParentID/ChildID tree, or b) a nested set. Since, while other rdbms systems support recursion MySQL does not (at least not without the use of an elaborate stored procedure), and especially since your data seems to be quite static, ie wont really change much; I suggest you go the nested set way. It would make things for you much easier.

Upvotes: 1

Timekiller
Timekiller

Reputation: 3136

You can create a table locations with following columns:

id | type | name        | parent_id
 1 |    1 | State 1     |         0
 2 |    1 | State 2     |         0
 3 |    2 | County 1    |         1
 etc

In this example, County 1 belongs to State 1, and "id 0" is assumed to be "nationwide".

With that model, creating GUI for setting item availability is quite straightforward: you first query by parent_id = 0 and offer it as a list, with additional nationwide as default option; if a state is selected, you generate another list, using selected id as parent_id and offering state wide as default option, etc. After user hits "save", get the deepest level id and save it in item table as location_id.

When it comes to actually getting the item list, though, it becomes less simple. For example, if your user says that his location is County 1, you should return a combined result for items that have location_id = 3, 1 or 0, going up the tree by parent_id. If you used an RDBMS that supported recursive queries, you could get those in a single query; MySQL doesn't support that, so you'll have to work around it by creating a loop to query items with specific location id, then selecting parent_id from locations, and repeating the query for that.

The negative side is that you don't control tree integrity with that structure. That is, you don't control that a city can only have a county as a parent - though you can create insert/update triggers to help with that. It's also possible to create a loop (by parenting items to one another), and it could potentially lead to problems.

Upvotes: 1

Related Questions