URL Management and Acts As Tree
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:
| id | parent_id | domainname | review_id |
| 1 | \N | cn | \N |
| 2 | \N | net | \N |
| 3 | \N | org | \N |
| 4 | \N | uk | \N |
| 5 | \N | com | \N |
| 6 | 5 | foo | 100 |
| 7 | 5 | example | 101 |
| 8 | 7 | foo | 104 |
| 9 | 7 | bobo | \N |
| 10 | 9 | foo | 105 |
| 11 | 2 | example | 103 |
| 12 | 2 | bobo | 102 |
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 
rder => "domainname"
def url
domainname + ancestors.collect {|i| "." + i.domainname }.to_s
end
Looping through your domain records gives you something like the table below:
| id | parent_id | domainname | review_id | url |
|---|---|---|---|---|
| 1 | cn | cn | ||
| 2 | net | net | ||
| 3 | org | org | ||
| 4 | uk | uk | ||
| 5 | com | com | ||
| 6 | 5 | foo | 100 | foo.com |
| 7 | 5 | example | 101 | example.com |
| 8 | 7 | foo | 104 | foo.example.com |
| 9 | 7 | bobo | bobo.example.com | |
| 10 | 9 | foo | 105 | foo.bobo.example.com |
| 11 | 2 | example | 103 | example.net |
| 12 | 2 | bobo | 102 | bobo.net |
2 comments July 19th, 2008
