CODEWITHSUNDEEP
database-design
Shane
Shane

Reputation: 1075

Database table to hold URLs

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:

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?

Upvotes: 1

Views: 171

Answers (2)

John Hyland
John Hyland

Reputation: 6872

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).

Upvotes: 3

defines
defines

Reputation: 10534

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.

Upvotes: 1

Related Questions