Example MySQL schemas for multi-user feed reader

create_tables.sql

CREATE TABLE `postmeta` (
  `post_id` char(16) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `postmeta_read` bit(2) NOT NULL DEFAULT b'0',
  `postmeta_starred` bit(2) NOT NULL DEFAULT b'0',
  `postmeta_liked` bit(2) NOT NULL DEFAULT b'0',
  `postmeta_shared` bit(2) NOT NULL DEFAULT b'0',
  `postmeta_lastupdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `postmeta_cache` longtext,
  `postmeta_annotation` longtext,
  PRIMARY KEY (`post_id`,`user_id`),
  KEY `post_user_read` (`post_id`,`user_id`,`postmeta_read`),
  KEY `read_state` (`postmeta_read`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `posts` (
  `post_id` char(16) NOT NULL,
  `feed_id` int(10) unsigned NOT NULL,
  `post_time` bigint(20) unsigned DEFAULT NULL,
  `post_author` mediumtext,
  `post_pubdate` bigint(20) unsigned DEFAULT '0',
  `post_description` longtext,
  `post_description_original` longtext,
  `post_title` mediumtext,
  `post_link` varchar(255) DEFAULT NULL,
  `post_link_mobile` varchar(255) DEFAULT NULL,
  `post_guid` varchar(255) DEFAULT NULL,
  `post_source_title` varchar(255) DEFAULT NULL,
  `post_source_link` varchar(255) DEFAULT NULL,
  `post_enclosures` varchar(255) DEFAULT NULL,
  `post_title_or_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_uniq` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_cache` longtext,
  PRIMARY KEY (`post_id`,`feed_id`),
  KEY `post_link` (`post_link`),
  KEY `post_title_or_time` (`post_title_or_time`),
  KEY `post_uniq` (`post_uniq`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `subscriptions` (
  `user_id` bigint(20) unsigned NOT NULL,
  `feed_id` int(10) unsigned NOT NULL,
  `feed_folder` varchar(255) NOT NULL DEFAULT '',
  `feed_url` varchar(255) NOT NULL DEFAULT '',
  `feed_title` varchar(255) NOT NULL DEFAULT '',
  `feed_htmlurl` varchar(255) DEFAULT NULL,
  `feed_description` varchar(255) DEFAULT NULL,
  `feed_cache` longtext,
  `last_update` int(100) unsigned DEFAULT NULL,
  `last_update_status` tinyint(3) unsigned DEFAULT NULL,
  `last_token` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`feed_id`,`feed_url`),
  UNIQUE KEY `feed_folder` (`feed_folder`,`user_id`,`feed_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Each row represents a unique subscription';

CREATE TABLE `usermeta` (
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `meta_key` varchar(255) NOT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`user_id`,`meta_key`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  `user_cookie_token` varchar(100) NOT NULL,
  `google_username` varchar(64) NOT NULL DEFAULT '',
  `google_password` varchar(64) NOT NULL DEFAULT '',
  `delicious_username` varchar(64) NOT NULL,
  `delicious_password` varchar(64) NOT NULL,
  `twitter_username` varchar(64) NOT NULL,
  `twitter_password` varchar(64) NOT NULL,
  `user_sid` varchar(255) DEFAULT NULL,
  `user_sid_time` int(100) unsigned DEFAULT NULL,
  `user_auth_token` varchar(255) DEFAULT NULL,
  `user_auth_token_time` int(100) unsigned DEFAULT NULL,
  `user_token` varchar(64) DEFAULT NULL,
  `user_token_time` int(100) unsigned DEFAULT NULL,
  `subscription_cache` longtext,
  `subscription_last_update` int(100) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;