HBase: The Definitive Guide

Appendix E. Hush SQL Schema

Here is the HBase URL Shortener, or Hush, schema expressed in SQL:

CREATE TABLE user (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  username CHAR(20) NOT NULL,
  credentials CHAR(12) NOT NULL,
  roles CHAR(10) NOT NULL, // could be a separate table "userroles", but \
    for the sake of brevity it is folded in here, eg. "AU" == "Admin,User"
  firstname CHAR(20),
  lastname CHAR(30),
  email VARCHAR(60),
  CONSTRAINT pk_user PRIMARY KEY (id),
  CONSTRAINT idx_user_username UNIQUE INDEX (username)
);

CREATE TABLE url (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  url VARCHAR(4096) NOT NULL,
  refShortId CHAR(8),
  title VARCHAR(200),
  description VARCHAR(400),
  content TEXT,
  CONSTRAINT pk_url (id),
)

CREATE TABLE shorturl (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  userId INTEGER,
  urlId INTEGER,
  shortId CHAR(8) NOT NULL,
  refShortId CHAR(8),
  description VARCHAR(400),
  CONSTRAINT pk_shorturl (id),
  CONSTRAINT idx_shorturl_shortid UNIQUE INDEX (shortId),
  FOREIGN KEY fk_user (userId) REFERENCES user (id),
  FOREIGN KEY fk_url (urlId) REFERENCES url (id)
)

CREATE TABLE click (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  datestamp DATETIME,
  shortId CHAR(8) NOT NULL,
  category CHAR(2),
  dimension CHAR(4),
  counter INTEGER UNSIGNED,
  CONSTRAINT pk_clicks (id),
  FOREIGN KEY fk_shortid (shortId) REFERENCES shortid (id);
)