source: bookmarks/trunk/bookmarks.sql @ 15

drop table if exists tags;
drop table if exists resources;
drop table if exists bookmarks;

-- bookmarks of resources
-- each resource can only have one bookmark
-- bookmarks have creation and modification times
create table bookmarks (
    id integer primary key,
    uri varchar references resources(uri) on update cascade,
    ctime integer, -- creation time of the bookmark
    mtime integer,  -- modification time of the bookmark
    constraint unique_uri unique (uri)
);

-- resources by URI
-- this is where we would store information about the resource,
-- such as its title, last known status, etc.
create table resources (
    uri varchar primary key,
    title varchar -- URI title (e.g., page title)
);

/* old resources table
create table resources (
    uri varchar primary key,
    title varchar, -- URI title (e.g., page title)
    ctime integer, -- creation time of the bookmark
    mtime integer  -- modification time of the bookmark
);
*/

-- tags that describe the resource
-- TODO: machine-tag style tags? e.g. format:video or creator:NASA; implicit tag prefix is "subject:"
create table tags (
    uri varchar references resources(uri) on update cascade,
    tag varchar,
    constraint unique_tag primary key (uri, tag)
);

/*
create a new resource:
insert into resources (uri, title) values ('http://echodin.net/', 'More Space');

create a bookmark of that resource:
insert into bookmarks (uri, ctime, mtime) values ('http://echodin.net/', 1323407821, 1323407821);

tag that resource:
insert into tags (uri, tag) values ('http://echodin.net/', 'homepage');

The resource's primary key is the URI. The bookmark id identifies the bookmark only, NOT the bookmarked resource

get the bookmark and its resource values
select id,resources.uri,title,ctime,mtime from bookmarks join resources on bookmarks.uri=resources.uri where id=1;
*/