-1

I have a database that keeps track of email addresses observed on URLs, along with other information about domains (DNS lookups, etc). The queries I do expect to be getting are, "All emails associated with the suffix .co", and "All emails associated with second level domain foo", and so on. As such, I've broken the URL down into its components, and have done the same for emails. Here is the design:

-- contains http, https
CREATE TABLE url.scheme(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains foo.bar, foo, api, etc
CREATE TABLE url.subdomain(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains google, microsoft, etc
CREATE TABLE url.second_level_domain(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains com, co.uk, info, net, etc
CREATE TABLE url.suffix(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains /contact, /foo/bar
CREATE TABLE url.path(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains foo=bar&fizz=buz, etc
CREATE TABLE url.query(
  id INTEGER,
  value TEXT
);

-- contains bob.dole, bill.clinton, george.bush
CREATE TABLE email.local_part(
  id INTEGER PRIMARY KEY,
  value TEXT
);

-- contains all parts of an email
CREATE TABLE email(
  local_id INTEGER,
  subdomain_id INTEGER,
  second_level_domain_id INTEGER,
  suffix_id INTEGER
  PRIMARY KEY (local_id, subdomain_id, second_level_domain_id, suffix_id)
);

-- many-to-many relationship between urls (scheme://subdomain.sld.suffix/path?query) and emails
CREATE TABLE url_email(
  scheme_id INTEGER,
  subdomain_id INTEGER,
  second_level_domain_id INTEGER,
  suffix_id INTEGER,
  path_id INTEGER,
  query_id INTEGER,
  email_local_id INTEGER,
  email_subdomain_id INTEGER,
  email_second_level_domain_id INTEGER,
  email_suffix_id INTEGER
);

The problem I have is with the many-to-many relationship table. The components of an email combined together are definitely good candidates for a PK; yet, I still feel like I should introduce a surrogate key here, as well as create a URL table so that it looks like this:

CREATE TABLE url.url(
  id INTEGER PRIMARY KEY,
  scheme_id INTEGER,
  subdomain_id INTEGER,
  second_level_domain_id INTEGER,
  suffix_id INTEGER,
  path_id INTEGER,
  query_id INTEGER,
  UNIQUE (scheme_id, subdomain_id, second_level_domain_id, suffix_id, path_id, query_id)
)

-- contains bob.dole, bill.clinton, george.bush
CREATE TABLE email.local_part(
  id INTEGER PRIMARY KEY,
  value TEXT
);
-- contains all parts of an email
CREATE TABLE email(
  id INTEGER PRIMARY KEY,
  local_id INTEGER,
  subdomain_id INTEGER,
  second_level_domain_id INTEGER,
  suffix_id INTEGER,
  UNIQUE (local_id, subdomain_id, second_level_domain_id, suffix_id)
);

-- many-to-many relationship between urls (scheme://subdomain.sld.suffix/path?query) and emails
CREATE TABLE url_email(
  url_id INTEGER REFERENCES url.url(id),
  email_id INTEGER REFERENCES email.email(id),
);

Or, potentially the table is too normalized? Maybe it does not make sense to break everything down to components. For example, if I create a URL table with all the components of a URL, do I also create a FQDN table with all the components of a FQDN (subdomain_id, second_level_domain_id, suffix_id)? Should the URL table then have fqdn_id instead of subdomain_id, second_level_domain_id, suffix_id? In short, is breaking it down like this just complicating things?

What is the best approach for a design like this?

1
  • 1
    Have you considered using a GENERATED field in this case?
    – Vérace
    Apr 3 at 20:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.