Database Schema Draft

A graphical quasi-ER diagram of the database can be found here.

UserTable
This table is intended as the main identifier. The UID field is the primary and unique key for any given user. This table can later be expanded to include system preferences.

CREATE TABLE UserTable
(
	uid INTEGER UNSIGNED NOT NULL,
	fname VARCHAR(50) NOT NULL,
	lname VARCHAR(50) NOT NULL,

	PRIMARY KEY(uid)
)

ActiveDeviceTable
This table contains the list that a given user is actively using at the moment. This is how the server knows which devices to update.

CREATE TABLE ActiveDeviceTable
(
	uid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,
	ip INTEGER(4) UNSIGNED NOT NULL
)

ConfigTable
This table contains misc configuration information necessary for DB management. This table contains one and only one row. It is the columns that define the various configuration information.

• The nextid entry is necessary because JDBC actually does not support the AUTO_INCREMENT information. Thus, we need to generate uids manually.

CREATE TABLE ConfigTable
(
	nextid INTEGER UNSIGNED NOT NULL
)

TodoTable
Simple todo list data

CREATE TABLE TodoTable
(
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,

	isdone TINYINT,
	priority TINYINT,
	txt TEXT
)

MemoTable
Simple Memo Pad

CREATE TABLE MemoTable
(
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,

	title VARCHAR(255),
	txt BLOB
)

MessageTable
This contains not only a history of the instant messages sent to the user, but also allows us to queue message to a user not immediately available.

CREATE TABLE MessageTable
(
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,
	fromuid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,
	ts TIMESTAMP NOT NULL,

	isread TINYINT NOT NULL,
	txt TEXT
)

CalendarTable

CREATE TABLE CalendarTable
(
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid INTEGER UNSIGNED NOT NULL REFERENCES UserTable.uid,

	dt DATE NOT NULL,
	start TIME NOT NULL,
	end TIME NOT NULL,
	txt TEXT
)

ContactTable
Contact table contains the set of contacts for a user.

CREATE TABLE ContactTable
(
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid INTEGER UNSIGNED REFERENCES UserTable.uid,

	fname VARCHAR(50) NOT NULL,
	lname VARCHAR(50) NOT NULL,
	company VARCHAR(50),
	worktel CHAR(10),
	hometel CHAR(10),
	mobile CHAR(10),
	pager CHAR(10),
	workaddr TINYTEXT,
	homeaddr TINYTEXT
)