Proper way to store lots of links? I'm doing a small crawl over multiple sites and I have a lot of links which are represented by ID's (example.com/foo = 354).

I'm currently storing the link -&gt; link references and the link text. So in the following table page "2845" contains a link to 4479 with the text "About Us". Nothing big, just 3NF.

+----------+----------+-----------------+
| url_1_id | url_2_id | text            |
+----------+----------+-----------------+
|     2845 |     4479 | About Us        |
|     2845 |     4480 | Who We Are      |
|     2845 |     4481 | What We Do      |
|     2845 |     4482 | Core Principles |
|     2845 |     4483 | Research Staff  |
+----------+----------+-----------------+


However, by my calculations (most of the pages containing 500-1000 links each) I should have 6GB of link data by the time I've only parsed 200k pages.

Is there a better way to store link data? Especially if there is a good way to normalize repeated navigation menus for all site pages.
Possible approaches:


If your problem is well represented by a graph (and I think it is), I'd suggest that you consider using a Graph Database. It's optimised for this kind of scenarios: you have pages (nodes), you have links between pages (verticies), and you have some attributes associated to either one of them. Check Neo4J or OrientDB to get a feeling for what they could do in your scenario.





If you want to save some space, consider a way to represent menus in a compact way. That is, there will be a very large number of cases like:

page1 - links_to - home
page1 - links_to - legalese
page1 - links_to - the_company
page1 - links_to - our_products
page1 - links_to - our_services
page1 - links_to - contacts  

page1 - links_to - something unique or not common 1
page1 - links_to - something unique or not common 2  

and then again

(same for page2)
(same for page3)   

if you identify this structure, you can create some "meta-structure" such as "standard_menu". (The difficult part of this approach is deciding what collection of links should be considered a menu and which are not, and small differences such as a page containing a menu with a slight variations [such as "I don't link to myself"]).


This data structure should help you:

CREATE TABLE urls
(
    id_url serial PRIMARY KEY,
    url text NOT NULL,
    UNIQUE(url)
) ;

CREATE TABLE menus
(
    id_menu serial PRIMARY KEY
) ;

CREATE TABLE menu_links
(
    id_menu integer not null REFERENCES menus(id_menu),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_menu, id_url_to)
) ;

CREATE TABLE from_page_to_page
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_url_from, id_url_to, link_text)
) ;

CREATE TABLE from_page_to_menu
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_menu integer not null REFERENCES menus(id_menu),
    PRIMARY KEY (id_url_from, id_menu)
) ;


And finally, you can have a view that puts together the equivalent of your existing table by making a UNION of the "menu links" and the "non-menu links":

CREATE VIEW all_links AS

SELECT 
    id_url_from, id_url_to, link_text 
FROM 
    from_page_to_page
UNION ALL
SELECT 
    id_url_from, id_url_to, link_text
FROM 
    from_page_to_menu
    JOIN menu_links ON menu_links.id_menu = from_page_to_menu.id_menu ;


Alternatively, you could always change the name "menu" by "link_group", every time you have something like:

page1 - links_to - page_a
   page1 - links_to - page_b
   page1 - links_to - page_c
   page1 - links_to - page_z  

page2 - links_to - page_a
   page2 - links_to - page_b
   page2 - links_to - page_c
   page2 - links_to - page_y  

page3 - links_to - page_a
   page3 - links_to - page_b
   page3 - links_to - page_c
   page3 - links_to - page_x  

(ignoring text labels, these are actually 12 pairs of numbers)

you could convert it into:

link_grup_1  - page_a
   link_grup_1  - page_b
   link_grup_1  - page_c  

page_1 - link_grup - link_grup_1
   page_1 - links_to - page_z
   page_2 - link_grup - link_grup_1
   page_2 - links_to - page_y
   page_3 - link_grup - link_grup_1
   page_3 - links_to - page_x  

(these are only 9 pairs of numbers).

The savings will grow as soon as the number of items common to many pages is more than just two or three.