Cracking The Vault (part 1) - Design Time
(Page 5 of 12 )
Now that the functional requirements have been decided, it's time to find someone I don't particularly like and shift the whole job over to him while I enjoy a long lunch and a comfortable nap...
Just kidding. The next step is actually to begin thinking about the database design, in accordance with the feature set above.
This is a good time for you to download the source code, so that you can refer to it throughout this article (you will need a Web server capable of running PHP and a mySQL database).
vault.zipAfter much thought and a few abortive experiments, I came up with the following database schema - five tables, one for each of the hours I won't get to sleep tonight.
# --------------------------------------------------------
#
# Table structure for table 'user'
#
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id tinyint(4) unsigned NOT NULL auto_increment,
username varchar(25) NOT NULL,
password varchar(50) NOT NULL,
PRIMARY KEY (id)
);
#
# id - user ID
# username - account username
# password - account password
#
This table contains a list of users allowed to enter The
Vault - each user is assigned a unique ID, which will be used throughout the application. Here are some dummy entries.
#
# Dumping data for table 'user'
#
INSERT INTO user (id, username, password) VALUES ( '1', 'john',
'2ca0ede551581d29');
INSERT INTO user (id, username, password) VALUES ( '2', 'joe',
'7b57f28428847751');
INSERT INTO user (id, username, password) VALUES ( '3', 'tom',
'675bd1463e544441');
INSERT INTO user (id, username, password) VALUES ( '4', 'bill',
'656d52cb5d0c13cb');
Next,
# --------------------------------------------------------
#
# Table structure for table 'category'
#
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id tinyint(4) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - category ID
# name - category name
#
I plan to allow users to categorize the documents stored
in The Vault - this table contains a list of available categories. Here are some dummy entries.
#
# Dumping data for table 'category'
#
INSERT INTO category (id, name) VALUES ( '1', 'Billing');
INSERT INTO category (id, name) VALUES ( '2', 'Company Policies');
INSERT INTO category (id, name) VALUES ( '3', 'Administrivia');
INSERT INTO category (id, name) VALUES ( '4', 'Document Templates');
INSERT INTO category (id, name) VALUES ( '5', 'Process Flows');
It should be noted that both these tables would typically
need to be controlled by an administrator, who would be in charge of adding new users and categories to the system.
# --------------------------------------------------------
#
# Table structure for table 'data'
#
DROP TABLE IF EXISTS data;
CREATE TABLE data (
id tinyint(4) unsigned NOT NULL auto_increment,
category tinyint(4) unsigned DEFAULT '0' NOT NULL,
owner tinyint(4) unsigned DEFAULT '0' NOT NULL,
realname varchar(255) NOT NULL,
created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
description varchar(255),
comment text,
status tinyint(4) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
#
# id - file ID
# category - category ID
# owner - owner's user ID
# realname - the original name of the file
# created - date on which file was first checked in
# description - one-line description of contents
# comment - author's note
# status - check in/out status; either 0 (available) or userID of user
document is checked out to
#
This table contains information on the documents stored
in the system. Each document is assigned a unique ID, and the table also stores information on the document category, the creation date, the document "owner" (the user who initially added the file), and a user-specified description and comment. The last field stores the current status of the document (checked in/out).
# --------------------------------------------------------
#
# Table structure for table 'perms'
#
DROP TABLE IF EXISTS perms;
CREATE TABLE perms (
fid tinyint(4) DEFAULT '0' NOT NULL,
uid tinyint(4) DEFAULT '0' NOT NULL,
rights tinyint(4) DEFAULT '0' NOT NULL
);
#
# fid - file ID from "data" table
# uid - user ID from "user" table
# rights - rights user "uid" possesses for file "fid"; 1 = view, 2 = modify
#
This table stores permissions on a per-file basis;
permissions may be defined as "view" (1) or "modify" (2). The unique combination of these three columns makes it possible to identify any user's rights for any file in the system.
# --------------------------------------------------------
#
# Table structure for table 'log'
#
DROP TABLE IF EXISTS log;
CREATE TABLE log (
id tinyint(4) unsigned DEFAULT '0' NOT NULL,
modified_on datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
modified_by tinyint(4) unsigned DEFAULT '0' NOT NULL,
note text
);
#
# id - file ID
# modified_on - date of revision
# modified_by - ID of user modifying file
# note - description of modification
#
Finally, this table has been created specifically to
store revision information when documents are checked back into the system. Don't worry about this one for the moment, I'll be discussing it in detail at a later stage.
At this point, I should say that the schema above constitutes an initial draft only. As the software evolves over time, I plan to modify this to add support for new features, and to optimize existing code.
This article copyright Melonfire 2001. All rights reserved.Next: Start Me Up >>
More PHP Articles
More By Vikram Vaswani, (c) Melonfire
