Saving URI in database parsed vs plain

Question

So I want to save different URIs in database.

I want format to be forced as well to look up different URIs easily.

Is it better to create table like this:

CREATE TABLE uris (
  id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
  scheme VARCHAR(255) NOT NULL,
  host VARCHAR(255) NOT NULL,
  path VARCHAR(255) NOT NULL,
  port SMALLINT UNSIGNED,
  query VARCHAR(255),
  fragment VARCHAR(255)
);

Or just

CREATE TABLE uris (
    id INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    uri TEXT NOT NULL
);

UPDATE: Use

Read, store maybe do some analytics. Build back original URI and if it's web URL redirect. May do some searching that's why I think first is better.

I am more interested in what advantage does one have over another or are there any problems in using one of them ?

@AndresF. Read, store maybe do some analytics. Build back original URI and if it's web URL redirect. May do some searching that's why I think first is better. I am more interested in what advantage does one have over another or are there any problems in using one of them ? — 8bra1nz, Mar 07 '17 at 13:31
@nikachx Is there any reason you can't just do *both*? Go with the first one, but add a column `uri` and store the unparsed version there. That way you can quickly search against that, or run analytics over the individual parts. Best of both worlds. The only argument against this is data redundancy, but I'd argue it's not all that redundant. — neilsimp1, Mar 07 '17 at 13:54
@neilsimp1 Good idea, post as answer. I want to get others opinion as well. — 8bra1nz, Mar 07 '17 at 14:05
How are you going to use your data. Do you plan to query for information like "what is average query string length for port numbers given that protocol is https"? — el.pescado - нет войне, Mar 07 '17 at 14:30

score 4 · Answer 1 · answered Mar 07 '17 at 16:01

Seriously, are you ever going to do a search for scheme, path, port, query or fragment? Probably not. About the only thing you might want to search is host.

Now, if your URIs mostly have the same scheme (http), you can build an index on the free-form text column and search it using the LIKE operator with a prefix match, if you want to do host search. If there are few different schemes, just search for "http://foo%", "ftp://foo%" etc.

So, chances are the only search you will ever need can be implemented by storing the URI as free-form text. Thus, I would recommend not trying to be clever and simply storing the URI as free-form text.

Also, how many URIs are you going to store? If it's in the thousands and not millions, you should consider that premature optimization is the root of all evil. Thus, building indices you will never need is not going to help you in any way. This also favors storing the URI as free-form text.

Furthermore, there is the minor possibility that the format of URIs is going to change in the future. This means you need to update your application to accept the new format. By storing URI as free-form text, you don't need to do that.

score 2 · Answer 2 · answered Mar 07 '17 at 14:22

Uris have a well defined format, but even so I think you will be hard pressed to represent it as a table schema.

For example, your example has a number of problems

you will run out of ints
query string can be longer than 255 characters
sometimes the port is not stated etc

score 1 · Answer 3 · answered Mar 07 '17 at 14:21

I don't see any reason that you can't just do both here:

CREATE TABLE uris (
  id INT(11) UNIQUE PRIMARY KEY AUTO_INCREMENT,
  uri TEXT NOT NULL,
  scheme VARCHAR(255) NOT NULL,
  host VARCHAR(255) NOT NULL,
  path VARCHAR(255) NOT NULL,
  port SMALLINT UNSIGNED,
  query VARCHAR(255),
  fragment VARCHAR(255)
);

That way you can quickly search against that, or run analytics over the individual parts. Best of both worlds. The only argument I can think of to not do this is because of redundant data, but it's only a short string that's getting repeated and I think the benefit and simplicity would outweigh a string that's duplicated.

Saving URI in database parsed vs plain

3 Answers3