How do I store URL fragments in a database?

How are URLs (fragments) stored in a relational database?

In the following URL fragment:

 ~/house/room/table

it lists all the information on a table, and perhaps some information about the table.

This fragment:

~/house

outputs: Street 13 and Room, Garage, Garden

~/house/room

outputs: My room and Chair, Table, Window

What does the Database schema looks like? What if I rename house to flat?

Possible solution

I was thinking that I could create a hash for the URL and store it along with parentID and information. If I rename some upper-level segment I would then need to update all the rows which contain the given segment.

Then I thought would store开发者_Go百科 each segment along with information and its level:

SELECT FROM items WHERE key=house AND level=1 AND key=room AND level=2

How do I solve this problem if the URL can be arbitrarily deep?

check The Adjacency List Model and The Nested Set Model described in Joe Celko's Trees and Hierarchies in SQL for Smarties

you should find plenty information to this topic. one article is here

Update

The Nested Set Model is very good if you are looking for a task like 'Retrieving a Single Path'. What you have is 'Find the Immediate Subordinates of a Node'. Here the Adjacency List Model is better.

| id | p_id | name  |
|  1 | null | root  |
|  2 |    1 | nd1.1 |
|  3 |    2 | nd1.2 |
|  4 |    1 | nd2.1 |

SQL to get a row with name of a fragment and it's direct sub items.

SELECT
  p.name,
  GROUP_CONCAT(
    c.name
    SEPARATOR '/'
  ) AS subList
FROM _table p
INNER JOIN _table c
  ON p.id = c.p_id
WHERE p.name = 'root'

P.S. prefer WHERE p.id = 1. Id is unique where as name can be ambiguous.

see MySQL GROUP CONCAT function for more syntax details.