Geek Mama

URL Management and Acts As Tree

July 19th, 2008

I’ve been tasked with redesigning our database to optimize the way we store and search for URLs. Since DNS forms a tree-like hierarchy, it makes sense to store our urls this way. Most TLDs include second-level domains, but we want to be able to store an infinite number of subdomains to maintain flexibility.Here are a few example sites with ids from their current table structure:

100 foo.com
101 example.com
102 bobo.net
103 example.net
104 foo.example.com
105 foo.bobo.example.com


These sites are stored in our new tree table like this:

The table above allows us to pull a host domain and all of its subdomains in one shot. For example, what are all the domains for ‘example.com’?:
SET @root = 'com';
SET @d2 = 'example'

SELECT concat_ws('.', t3.domainname, t2.domainname, t1.domainname)
FROM domains AS t1
LEFT JOIN domains AS t2 ON t2.parent_id = t1.id
LEFT JOIN domains AS t3 ON t3.parent_id = t2.id
WHERE t2.domainname = @d2 and t1.domainname = @root ;

Result:
 foo.example.com
 bobo.example.com

To pull them out using RoR in human readable form, its as simple as adding the code below to your model:
acts_as_tree :order => "domainname"
def url
domainname + ancestors.collect {|i| "." + i.domainname }.to_s
end

Looping through your domain records gives you something like the table below:

Entry Filed under: Ruby on Rails