mysql - How do I store URL fragments in a database? -

how urls (fragments) stored in relational database?

in following url fragment:

 ~/house/room/table 

it lists information on table, , perhaps information table.

this fragment:

~/house 

outputs: street 13 , room, garage, garden

~/house/room 

outputs: my room , chair, table, window

what database schema looks like? if rename house flat?

possible solution

i thinking create hash url , store along parentid , information. if rename upper-level segment need update rows contain given segment.

then thought store each segment along information , level:

select items key=house , level=1 , key=room , level=2 

how solve problem if url can arbitrarily deep?

check the adjacency list model , the nested set model described in joe celko's trees , hierarchies in sql smarties

you should find plenty information topic. 1 article here

update

the nested set model if looking task 'retrieving single path'. have 'find immediate subordinates of node'. here adjacency list model better.

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

sql row name of fragment , it's direct sub items.

select   p.name,   group_concat(     c.name     separator '/'   ) sublist _table p inner join _table c   on p.id = c.p_id p.name = 'root' 

p.s. prefer where p.id = 1. id unique name can ambiguous.

see mysql group concat function more syntax details.