Cacher is the code snippet organizer for pro developers

SQLite Relational Database Examples

This is a example of a many to many relationship in sqlite which takes a list of people and connects them to a set of groups to efficiently store who is in which group.

CREATE TABLE users (
  u_id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT
); 
CREATE TABLE groups (
  g_id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT
);
CREATE TABLE p_g_bridge(
  u_id INTEGER NOT NULL REFERENCES users,
  g_id INTEGER NOT NULL REFERENCES groups,
  PRIMARY KEY ( pid, gid )
);

The previous example would be great for a thesarus/dictionary database which could be used as the knowledge that a text analysis algorithm needs to get started.

CREATE TABLE words(
  w_id INTERGER PRIMARY KEY AUTOINCREMENT,
  text VARCHAR(64) NOT NULL
);
CREATE TABLE usage_type( /* ex: noun, verb, adverb */
  t_id INTERGER PRIMARY KEY AUTOINCREMENT,
  type VARCHAR(16) NOT NULL
)
CREATE TABLE definitions(
  w_id INTERGER NOT NULL PRIMARY KEY REFERENCES words(w_id),
  type INTERGER NOT NULL REFERENCES usage_type(t_id),
  text TEXT NOT NULL
);
CREATE TABLE synonyms( /* many to many key table */
  w_id INTERGER NOT NULL REFERENCES words,
  syn_id INTERGER NOT NULL REFERENCES words,
  PRIMARY KEY ( w_id, syn_id )
);

This would also be a good design for an efficient friends list for users.

CREATE TABLE users(
  u_id INTERGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(64) NOT NULL
);
CREATE TABLE friends(
  u_id INTERGER NOT NULL REFERENCES users,
  f_id INTERGER NOT NULL REFERENCES users,
  PRIMARY KEY ( u_id, f_id )
);