If you can't or won't use a Graph DB (which should be the optimal DB for this type of application), then you can test a slightly more abstract breakdown of the data. Here is one approach I would take towards your task.
First, you have specific URL Links. That would be, for example, the link to joanolo, https://dba.stackexchange.com/users/112361/joanolo, found at the end of the other answer. There may be more than one such link on a page (i.e. joanolo's comments each have the same link). How do you want to handle multiple identical links on a page? I have chosen to handle identical links a specific way, discussed below. I define the table to handle the URL Links like this:
CREATE TABLE URLLinks (
URLLinksID bigint identity(1,1),
URLParams nvarchar(max) NULL,
URLsID bigint not null,
secure bit default 0 not null,
CONSTRAINT pk_URLLINKS Primary Key (URLsID, URLParams, Secure)
)
Note that an URL Link includes any parameters on the link, and whether or not the connection is secure or not. For this first pass, I am ignoring alternate types of links, such as ftp://
The URLsID field in the URLLinks table is a reference to the URLs table. This contains the text of the URL, e.g. dba.stackexchange.com/users/112361/joanolo
in the sample URL above. Any link that has this as the URL will reference this URLs entry by URLsID. Here is the very simple URLs table:
CREATE TABLE URLs (
URLsID bigint identity(1,1),
URLText nvarchar(max) not null
)
Between these two tables, we have all of the information we need to recreate the URL, including any parameters, without duplicating the URLText.
Note that if it was worthwhile, we could break the URLText into a chain of smaller pieces, and build a URL chain linking the correct pieces together. I wager that this linking, with an 8-byte ID pointing to each piece, the parent piece (if any), and the Chain (24 bytes + overhead) is going to be more costly in space and overhead than working with the URLs text directly, so I am not exploring that path now.
To associate a link with its page, there is the URLRefs table:
URLRefs (
URLID bigint not null,
ParentURLID bigint not null,
LinkText nvarchar(max) not null,
LinkCount int default 1 not null
)
Notice the field LinkCount
. This will count the number of times the same URLLink occurs on the page, rather than having a separate entry for each occurrence. This may or may not save space and effort overall, but it does hear on dba.stackexchange.com, so I went with this method. On sites where links are never duplicated on a page, this will always be 1.
With these elements, you should have the most compact non-graph depiction of pages with URLs pointing to pages with URLs pointing to... (Unless you build special cases to handle e.g. menus, as joanolo suggested.)
As an example, the joanolo link as shown above will have just one entry in URLLinks, one entry in URLs, and one entry in URLRefs for each different page the link appears on. If he has 500 such links across 100 pages, he will still have only one link in URLs and URLLinks, and 100 entries in URLRefs.