Hierarchical trees of links are basically large
databases, and LinkWeaver is built on a few
interrelated database tables. The SQL code for
creating the tables used by LinkWeaver is shown below
the explanation of each table.
The urls table contains information
that is specific to a particular link including a
default names and description, the status of the link,
how many times it has been visited via the
LinkWeaver system, contact info, etc. The
lines in this table that are italicized and in red are
specific to our particular site. You may have other
fields that are important to your specific
application, and the italicized fields can be
replaced however you would like.
create table urls (
id integer not null auto_increment primary key,
url varchar(255) not null,
name varchar(255),
description text,
status enum('ok','broken') not null,
total_hits integer default 0,
recent_hits float default 0.0,
last_hit date,
avg_rating float default 0.0,
rating_count integer default 0,
date_created date,
date_modified date,
license_type varchar(20)
);
The urls from these tables are then
grouped together to form the entryGroups
table. This is done so that entire groups of links
can be easily moved around in the tree if the tree
structure changes. The name and
description are both optional:
create table entryGroups (
id integer not null auto_increment primary key,
name varchar(255),
description text,
total_hits integer default 0,
recent_hits float default 0.0,
last_hit date,
date_created date,
date_modified date
);
The sections table contains
navigational information for the hierarchical tree.
Each section has a group_id that
associates a entryGroup with the section.
Since entryGroups can appear in the tree in
multiple locations, the name and
description fields override the default
name and
description fields for the
associated entryGroup.
create table sections (
id integer not null auto_increment primary key,
group_id integer not null references entryGroups,
parent_id integer not null references sections,
n_children integer default 0,
name varchar(255),
description text,
status enum('live','pending','suspended','deleted') not null,
total_hits integer default 0,
recent_hits float default 0.0,
last_hit date,
date_created date,
date_modified date,
contact_id integer references users
);
The entries table maps the entries in
urls into the entryGroups
table. A specific link may have entries in many
entryGroups. Since each place in the
hierarchy may want to describe the link differently,
the linkname, description,
and hitcount fields override the default
name, description and
hitcount when the data is displayed.
create table entries (
id integer not null auto_increment primary key,
group_id integer not null references entryGroups,
url_id integer not null references urls,
name varchar(255),
description text,
status enum('live','pending','suspended','deleted') not null,
total_hits integer default 0,
recent_hits float default 0.0,
last_hit date,
avg_rating float default 0.0,
rating_count integer default 0,
date_created date,
date_modified date,
contact_id integer references users
);
The users table defines a list of
users, passwords, and email addresses for the tree and
link editors:
create table users (
id integer not null auto_increment primary key,
username char(16) default '' not null,
password char(16) default '' not null,
email varchar(100)
);
The editors table maps users onto
their authority domains within the hierarchy. An
editor of a section can change anything that sits
below their section_id in the
hierarchical tree. This is abstracted out from the
users table so that a single user can have control
over multiple disconnected branches of the tree.
create table editors (
id integer not null auto_increment primary key,
user_id integer not null references users,
section_id integer not null references sections
);
The administrators table maps users
onto administrative functions (modifying the editor
entries, changing the structure of the table,
etc.):
create table administrators (
id integer not null auto_increment primary key,
user_id integer not null references users
);
