Database Model

All persistent data in gnizr is stored in MySQL. This document describes the database model of gnizr.

This model is used in the following releases:

• Gnizr 2.2.0 (view: gnizr-db.sql)
• Gnizr 2.3.0 (view: gnizr-db.sql)

Note: click 'All Sizes' on the flickr page to fetch a high-resolution of the diagram.

Entity Tables

Table: user

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `fullname` varchar(100) NOT NULL,
  `created_on` datetime NOT NULL,
  `email` varchar(50) NOT NULL,
  `acct_status` int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• username must be unique.
• password should be inserted with MD5 hash of a plain text password string -- e.g., using MySQL function md5().

When gnizr database is initialized for the first time, a special user account is created. The username of this account is gnizr. This is the superuser of a gnizr installation. User account gnizr should never be removed or renamed.

Table: link

CREATE TABLE `link` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `mime_type_id` int(10) unsigned default NULL,
  `url` text NOT NULL,
  `url_hash` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `url_hash_idx` USING BTREE (`url_hash`),
  KEY `FK_link_1` (`mime_type_id`),
  CONSTRAINT `FK_link_1` FOREIGN KEY (`mime_type_id`) 
    REFERENCES `mime_type_admin` (`id`) 
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• url_hash is a MD5 hash of url
• mime_type_id must exists in table mime_type_id.id

A link record describes a URL resource on the Web. Any given URL resource can only exists once in the link table. For example, if a URL http://www.google.com exists in the table, any subsequent SQL operation that tries to insert a record of the same URL will cause a DB exception to be thrown. The "uniqueness" of a URL is maintained via the column url_hash, which is the MD5 hash of column value url.

Table: tag

CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tag` varchar(45) collate utf8_bin NOT NULL,
  `count` int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

• tag must be unique

This table stores the list of tags that have been used to label bookmarks. This is the central place to store all tag strings. Tables that require references to a tag should define a database column to point to tag.id.

The column count stores the total number of bookmarks that have been labeled tag.tag. This count reflects the number of times used currently.

Table: bookmark

CREATE TABLE `bookmark` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `link_id` int(10) unsigned NOT NULL,
  `title` text collate utf8_bin NOT NULL,
  `notes` text collate utf8_bin NOT NULL,
  `created_on` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `bookmark_user_id_link_id_idx` USING BTREE (`user_id`,`link_id`),
  KEY `bookmark_user_id_idx` (`user_id`),
  KEY `bookmark_link_id` USING BTREE (`link_id`),
  KEY `bookmark_created_on_idx` (`created_on`),
  KEY `bookmark_lastup_idx` (`last_updated`),
  CONSTRAINT `FK_bookmark_1` FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_bookmark_2` FOREIGN KEY (`link_id`) 
    REFERENCES `link` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

• Foreign key: bookmark.user_id => user.id
• Deleting an existing user record will also delete all bookmarks saved by the user
• Updating the ID of the user will also update the referenced user ID in bookmarks saved by the user.
• Foreign key: bookmark.link_id => link.id
• Deleting an existing link record will also delete all bookmarks that reference this link
• Updating the ID of the link record will also update which referenced by the bookmarks.

Bookmarks are URL resources saved by the users. Unlike records in the link table, which only describe the intrinsic property of URL, records in the bookmark table adds a relationship between user and link.

A pair bookmark.user_id and bookmark.link_id defines a URL being saved by a user. A given user can only have one bookmark for a given URL. Different users can have their own bookmarks of the same URL.

Table: folder

CREATE TABLE `folder` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `folder_name` varchar(45) collate utf8_bin NOT NULL,
  `owner_id` int(10) unsigned NOT NULL,
  `description` text collate utf8_bin NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `folder_owner_folder_idx` USING BTREE (`folder_name`,`owner_id`),
  KEY `FK_folder_admin_1` (`owner_id`),
  CONSTRAINT `FK_folder_admin_1` FOREIGN KEY (`owner_id`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

• Pair folder_name and owner_id must be unique
• Foreign key: folder.owner_id => user.id
• Deleting an existing user record will also delete all folders created by this user.
• Updating the ID of an existing user record will also update which referenced by the folders.

Users can organize their saved bookmarks using folders. This table stores information about user-created folders. Folder names (i.e., folder.folder_name) may be any non-empty strings. Some folder names have special purpose.

Special folder names:

Tagging Relationship Tables

Table: user_tag_idx

CREATE TABLE `user_tag_idx` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_user_id_tag_id` (`user_id`,`tag_id`),
  KEY `FK_user_tag_idx_2` (`tag_id`),
  KEY `idx_user_tag_id_user_id` (`user_id`),
  CONSTRAINT `FK_user_tag_idx_1` FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_user_tag_idx_2` FOREIGN KEY (`tag_id`) 
    REFERENCES `tag` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• id must be unique
• Foreign key: user_tag_idx.user_id => user.id
• On deleting an existing user record, all user_tag_idx records of the same user will be deleted.
• On updating the user.id of a user record, records of the same user_tag_idx.user_id will updated automatically.
• Foreign key: user_tag_idx.tag_id => tag.id
• On deleting an existing tag record, all user_tag_idx records of the same tag.id will be deleted.
• On updating the tag.id of a tag record, records of the same user_tag_idx.tag_id will be updated automatically.

user_tag_idx.id uniquely identifies the relationship between a tag and the user who has used the tag. This id is used to form the subject and the object of a tag assertion.

The column count stores the total number of times a given user has used user_tag_idx.tag_id to label saved bookmarks. This count reflects the number of times used currently.

Table: link_tag_idx

CREATE TABLE `link_tag_idx` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `link_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_link_id_tag_id` (`link_id`,`tag_id`),
  KEY `FK_link_tag_idx_2` (`tag_id`),
  KEY `idx_link_id` (`link_id`),
  CONSTRAINT `FK_link_tag_idx_1` FOREIGN KEY (`link_id`) 
    REFERENCES `link` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_link_tag_idx_2` FOREIGN KEY (`tag_id`) 
    REFERENCES `tag` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• id must be unique
• Foreign key: link_tag_idx.link_id => link.id
• On deleting an existing link record, all link_tag_idx records of the same link will be deleted.
• On updating the link.id of a link record, records of the same link_tag_idx.link_id will updated automatically.
• Foreign key: link_tag_idx.tag_id => tag.id
• On deleting an existing tag record, all link_tag_idx records of the same tag.id will be deleted.
• On updating the tag.id of a tag record, records of the same link_tag_idx.tag_id will be updated automatically.

The column count stores the total number of times a given link has been labeled by users using link_tag_idx.tag_id. This count reflects the number of times used currently.

Table: bookmark_tag_idx

CREATE TABLE `bookmark_tag_idx` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookmark_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `position` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `Index_4` (`bookmark_id`,`tag_id`),
  KEY `Index_2` (`bookmark_id`),
  KEY `Index_3` (`tag_id`),
  CONSTRAINT `FK_bookmark_tag_idx_1` FOREIGN KEY (`bookmark_id`) 
    REFERENCES `bookmark` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_bookmark_tag_idx_2` FOREIGN KEY (`tag_id`) 
    REFERENCES `tag` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

• id must be unique
• Foreign key: bookmark_tag_idx.link_id => bookmark.id
• On deleting an existing bookmark record, all bookmark_tag_idx records of the same bookmark will be deleted.
• On updating the bookmark.id of a bookmark record, records of the same bookmark_tag_idx.bookmark_id will updated automatically.
• Foreign key: bookmark_tag_idx.tag_id => tag.id
• On deleting an existing tag record, all bookmark_tag_idx records of the same bookmark.id will be deleted.
• On updating the tag.id of a tag record, records of the same bookmark_tag_idx.tag_id will be updated automatically.

The column count stores the total number of times a given bookmark has been labeled using bookmark_tag_idx.tag_id. This count reflects the number of times used currently. Typically this count value is either 0 or 1.

Table: folder_tag_idx

CREATE TABLE `folder_tag_idx` (
  `folder_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`folder_id`,`tag_id`),
  KEY `FK_folder_tag_idx_2` (`tag_id`),
  CONSTRAINT `FK_folder_tag_idx_1` FOREIGN KEY (`folder_id`) 
    REFERENCES `folder` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_folder_tag_idx_2` FOREIGN KEY (`tag_id`) 
    REFERENCES `tag` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

• id must be unique
• Foreign key: folder_tag_idx.link_id => folder.id
• On deleting an existing folder record, all folder_tag_idx records of the same folder will be deleted.
• On updating the folder.id of a folder record, records of the same folder_tag_idx.folder_id will updated automatically.
• Foreign key: folder_tag_idx.tag_id => tag.id
• On deleting an existing tag record, all folder_tag_idx records of the same folder.id will be deleted.
• On updating the tag.id of a tag record, records of the same bookmark_tag_idx.tag_id will be updated automatically.

The column count stores the total number of times a tag appeared in all bookmarks that are saved in a given folder. This count reflects the number of times used currently.

Tag Hierarchy Tables

Table: tag_prpt

CREATE TABLE `tag_prpt` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `ns_prefix` varchar(10) NOT NULL default 'gn',
  `description` varchar(255) default NULL,
  `prpt_type` enum('spatial','temporal','system','reference','default') NOT NULL default 'default',
  `cardinality` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `tag_prpt_unique_prpt_idx` (`name`,`ns_prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• name: a non-empty string with no white-spaces.
• ns: any valid XML namespace prefix
• description: text description of this tag property
• prpt_type: must be one of five ENUM types
1. spatial: for describing spatial property
2. temporal: for describing temporal property
3. system: for describing gnizr system functional property
4. reference: for describing relationships with other web resources
5. default: for describing everything else

This table defines the set of valid property can be used to construct tag assertion. The semantic of a tag property follows the RDF property semantic. By default, gnizr comes with a set of tag property definitions.

Table: tag_assertion

CREATE TABLE `tag_assertion` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `subject_id` int(10) unsigned NOT NULL,
  `prpt_id` int(10) unsigned NOT NULL,
  `object_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `tag_asrt_uniq_asrt_idx` (`subject_id`,`prpt_id`,`object_id`,`user_id`),
  KEY `FK_tag_asrt_prpt_id` USING BTREE (`prpt_id`),
  KEY `FK_tag_asrt_subject_id` USING BTREE (`subject_id`),
  KEY `FK_tag_asrt_object_id` USING BTREE (`object_id`),
  KEY `FK_tag_asrt_user_id` USING BTREE (`user_id`),
  CONSTRAINT `FK_tag_asrt_object_id` FOREIGN KEY (`object_id`) 
    REFERENCES `user_tag_idx` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tag_asrt_prpt_id` FOREIGN KEY (`prpt_id`) 
    REFERENCES `tag_prpt` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tag_asrt_subject_id` FOREIGN KEY (`subject_id`) 
    REFERENCES `user_tag_idx` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tag_asrt_user_id` FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• id: an unique ID of a tag assertion.
• subject_id: the subject of a tag assertion.
• prpt_id: the property of a tag assertion.
• object_id: the object of a tag assertion.
• user_id: the user who created this tag assertion.

A tag assertion is an RDF statement, which consists of three parts: subject, property (predicate) and object (value). In the tag_assertion table, subject_id, prpt_id and object_id represent those three parts, respectively. These columns link to the 'id' column of the user_tag_idx relationship. This allows different users to make different assertions about the same tag, and doesn't pollute the tag assertion space of each other.

Bookmark Relationship Tables

Table: bookmark_folder

CREATE TABLE `bookmark_folder` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookmark_id` int(10) unsigned NOT NULL,
  `folder_id` int(10) unsigned NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `bookmark_folder_unique_bmark` (`bookmark_id`,`folder_id`),
  KEY `FK_bookmark_folder_1` (`bookmark_id`),
  KEY `FK_bookmark_folder_2` (`folder_id`),
  KEY `bookmark_folder_lastup_idx` (`last_updated`),
  CONSTRAINT `FK_bookmark_folder_1` FOREIGN KEY (`bookmark_id`) 
    REFERENCES `bookmark` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_bookmark_folder_2` FOREIGN KEY (`folder_id`) 
    REFERENCES `folder` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

• id: an unique ID that identifies a given bookmark is saved in a particular folder.
• a bookmark can't be saved in the same folder more than once.
• Foreign key: bookmark_folder.bookmark_id => bookmark.id
• On deleting an existing bookmark record, all bookmark_folder records whose bookmark_id are the same as bookmark.id will also be deleted.
• On updating bookmark.id of an existing record, all bookmark_folder records whose bookmark_id are the same as bookmark.id will also be updated.
• Foreign key: bookmark_folder.folder_id => folder.id
• On deleting an existing folder record, all bookmark_folder records whose folder_id are the same as folder.id will also be deleted.
• On updating folder.id of an existing record, all bookmark_folder records whose folder_id are the same as folder.id will also be updated.

Geotagging Tables

Table: point_marker

CREATE TABLE `point_marker` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `geom` point NOT NULL,
  `notes` varchar(255) NOT NULL,
  `icon_id` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  SPATIAL KEY `geom_idx` (`geom`(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• id: an unique ID that identifies a Point marker for describing the geographical location of a thing
• geom: a POINT geometry for describing latitude and longitude. Format POINT(X,Y), in which X is longitude and Y is latitude.
• notes: short text description of this Point marker. This is usually defined by the user who has created this Point marker.
• icon_id: this column is not currently used

This table stores a set of Point markers that can used to geotag bookmarks. Because MySQL MyISAM engine doesn't support the use of FOREIGN KEY, the application will be responsible to delete geotagged bookmarks whenever a referenced Point marker is deleted from this table.

Table: bookmark_point_marker_idx

CREATE TABLE `bookmark_point_marker_idx` (
  `bookmark_id` int(10) unsigned NOT NULL,
  `point_marker_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`bookmark_id`,`point_marker_id`),
  CONSTRAINT `FK_bookmark_point_mark_idx_1` FOREIGN KEY (`bookmark_id`) 
    REFERENCES `bookmark` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

• Pair bookmark_id and point_marker_id represents a geotagged bookmark relation.
• A bookmark can have more than one geotag.
• Foreign key: bookmark_point_marker_idx.bookmark_id => bookmark.id
• On deleting an existing bookmark of bookmark.id, all bookmark_point_marker_idx records whose bookmark_id is the samee as bookmark.id will also be deleted.
• On updating the bookmark.id of an existing bookmark, all bookmark_point_marker_idx records whose bookmark_id is the same as bookmark.id will also be updated.

Auxiliary Tables

Table: search_idx

CREATE TABLE `search_idx` (
  `bookmark_id` int(10) unsigned NOT NULL,
  `text` text NOT NULL,
  `tags` text NOT NULL,
  PRIMARY KEY  (`bookmark_id`),
  FULLTEXT KEY `search_idx_text` (`text`),
  FULLTEXT KEY `search_idx_tags` (`tags`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• bookmark_id: the ID of an existing bookmark in bookmark
• text: the concatenation of bookmark.title and bookmark.notes
• tags: the concatenation of all tags of a bookmark

This table is created for support full-text search in MySQL. Because MySQL MyISAM engine doesn't support the use of FOREIGN KEY, when a bookmark is deleted, the application must remove it's corresponding record in the search_idx table.

Table: mime_type_admin

CREATE TABLE `mime_type_admin` (
  `id` int(10) unsigned NOT NULL default '0',
  `mime_type` varchar(45) NOT NULL,
  `label` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• id: an unique ID that identifies a known MIME-type in gnizr
• mime_type: mime type string
• label: text description of the mime type

Predefine MIME type in gnizr:

Define MIME types help to identify the type of resources that the users have bookmarked. Objects of mime_type_admi are referenced by link.mime_type_id.