SQL Database Schema Summary (Work In Progress)

1. A user //can create many collection -> many links

Agreed set of facts:

• A specific collection can be retrieved in the URL form: www.curates.io/user/unique_collection_name
• Collections must have a unique name
• When querying the database for links belonging to a specific collection, the url will be used to make the query.
  • Database: curates
  • Table: collection
  • Query string (MySQL): SELECT * FROM collection WHERE collection_url='unique_collection_name';

---

Tables were generated using Bookshelf.js script in server/dbconfig.js. See this wiki post for further directions on database installation: https://github.com/Next-Mars/curates/wiki/Database-Installation

SQL Syntax to Create Collections Table:

CREATE TABLE `collections` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `collection_url` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `stars` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `collections_collection_url_unique` (`collection_url`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

SQL Syntax to Create Links Table:

CREATE TABLE `links` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `link_url` varchar(255) DEFAULT NULL,
  `link_title` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `click_count` int(11) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL Syntax to Create Users Table:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `github` varchar(64) DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;