Database table to hold URLs...

Question

I'm writing a game that is played in a pseudo-Internet like environment. I'm just doing the "DNS" to the game. Basically, a database which links strings ("URLs") to machines (another entity in the database).

Pretty much like in real life:

• Each machine can have zero or more urls
• Each url has a tld. For simplicity, there are only TLDs and no TLDs with more than one extension (uhh, fix my terminology there?). So .com and .net is valid, but .co.uk and .org.uk isn't.
• urls can have zero or more subdomains
• Each subdomain can be linked to a different machine
• Each subdomain can have zero or more subdomains, each linking to different machines

My first instinct was to do something like this:

domain_tld table;
tld_id, tld

domain_hostname table;
hostname_id, hostname, tld, parent

In which, hostname was the URL, tld links to the domain_tld, parent is null if it's the root domain name. If it's a subdomain then parent is the parent's hostname_id, and the hostname is the subdomain. But then I realised that it's being assigned a redundant tld... Though I suppose that could just be given a null value.

I was wondering if there were any smarter ideas?

Answer 1

You could use a third table for subdomains if desired. That would eliminate the redundancy issue you mentioned. Instead of having a parent column on domain_hostname create a new table as follows:

domain_subdomain;
subdomain_id, subdomain_name, hostname_id

Where hostname_id is a foreign key back to the domain_hostname table. This is essentially normalization of the domain_hostname table.

Answer 2

It depends on what operations you need to perform on this data. Do you really need to model the tree structure of subdomains? That's (sort of) like how the real DNS delegates zone authority, but if all you really need is the "url => machine" aspect of DNS, then a simple 'url' table with a 'machine_id' column would be far simpler and probably more performant.

If you do need to model a tree structure, why bother distinguishing between TLDs and subdomains? You could simply have a "domain" table with "name", "parent_domain_id", and "machine_id" columns, and set the parent to null for TLDs. Indexing the parent column would make fetching the list of TLDs reasonably performant, probably, depending on the size of your dataset and what operations you need to optimize. This would be a simpler model than creating two separate tables, and would more closely match the real DNS system (there isn't really anything magic about "com", other than the implementation).