General Links

part of the ArsDigita Community System by dh@arsdigita.com and tzumainn@arsdigita.com

The Big Idea

This module serves two related purposes: Display/Maintains categorized links on at a single location (hot list), Solicit links to associate with any piece of information in the database.

Users may rate links on the hot list (a scale of 0 to 10), and this rating may be used in display of the links.

The Medium Sized Idea

A simple breakdown of the possible actions:

Under the Hood

General links are stored in one table general_links, with their properties (title, description, etc); the associations between items in the database are stored in a mapping table general_link_db_map; and the user's rating are keep in the table general_link_user_ratings.

As in the general-comments module, references to items in the database via id on_what_id in the table on_which_table. 

create sequence general_link_id_sequence start with 1;

create table general_links (
	link_id                 integer primary key,
	url                     varchar(300) not null,
	link_title              varchar(100) not null,
	link_description        varchar(4000),
	-- meta tags defined by HTML at the URL
	meta_description        varchar(4000),
	meta_keywords           varchar(4000),
	-- when was this submitted?
	creation_time	    	    date default sysdate not null,
	creation_user		    not null references users(user_id),
	creation_ip_address	    varchar(20) not null,
	last_modified		    date,
	last_modifiying_user	    references users(user_id),
	-- last time this got checked
	last_checked_date       date,
	last_live_date          date,
	last_approved           date,
	-- has the link been approved? ( note that this is different from
	-- the approved_p in the table wite_wide_link_map ) 
	approved_p              char(1) default 't' check(approved_p in ('t','f'))
);

create sequence general_link_map_id start with 1;

-- This table associates urls with any item in the database

create table site_wide_link_map (
	map_id          integer primary key,
	link_id                 not null references general_links,
	-- the table is this url associated with 
	on_which_table          varchar(30) not null,
	-- the row in *on_which_table* the url is associated with
	on_what_id              integer not null,
	-- a description of what the url is associated with
	one_line_item_desc      varchar(200) not null,
	-- who made the association
	creation_time	    	    date default sysdate not null,
	creation_user		    not null references users(user_id),
	creation_ip_address	    varchar(20) not null,
	last_modified		    date,
	last_modifiying_user	    references users(user_id),
	-- has the link association  been approved ?
	approved_p              char(1) check(approved_p in ('t','f')) not null
);

-- We want users to be able to rate links
-- These ratings could be used in the display of the links
-- eg, ordering within category by rating, or displaying 
-- fav. links for people in a given group..

create table general_link_user_ratings (
	user_id         not null references users,
 	link_id         not null references general_links,
	-- a user may give a url a rating between 0 and 10
	rating          integer not null check(rating between 0 and 10 ),
	-- require that the user/url rating is unique
	primary key(link_id, user_id) 
);

The module contains one core procedure, ad_general_links_list (based on ad_general_comments_list), that will show links associated with an item in the database and make appropriate links to files in /general-links and for recording and editing links.

The arguments to ad_general_links_list are:

Default approval policy is toggled by the DefaultLinkApprovalPolicy parameter.

If AllowGeneralLinksSuggestionsP is set to 1, then a user will be able to suggest links from /general-links/ index page.

In addition, by toggling GeneralLinksClickthroughP, one can toggle on/off the ability to keep track of link clickthroughs. These statistics are stored in a table defined in community-core.sql: 


create table clickthrough_log (
        local_url       varchar(400) not null,
        foreign_url     varchar(300) not null,  -- full URL on the foreign server
        entry_date      date,   -- we count referrals per day
        click_count     integer default 0,
        primary key (local_url, foreign_url, entry_date)
);
The links are marked by setting local_url to ad_link_linkid.

Administration

To support central administration of links, we rely on a helper table defined in community-core.sql: 

create table table_acs_properties (
             table_name      varchar(30) primary key,
             section_name    varchar(100) not null,
             user_url_stub   varchar(200) not null,
             admin_url_stub  varchar(200) not null
);
As with site-wide search and the user profiling system, this helper table enables us to make a single query and yet connect links over to the appropriate admin or user pages. Another part of this system is the one-line item description column in the general_links table.

For the purpose of the "hot list" page, links are put in different categories (one link could be placed in more than one category) by means of the tables categories and category_heirarchy found discussed in the /doc/user-profiling.html.

dh@arsdigita.com