Home arrow PHP arrow Page 5 - Cracking The Vault (part 1)

Design Time - PHP

Electronic documents are all well and good - but when you work onthem collaboratively, they can end up being more difficult to handle thanordinary pieces of paper. Multiple versions, competing standards, accesspermissions and revision history tracking are just some of the issues thatarise in a paperless office. This article discusses building and deployinga document management system across your network - and also teachesbeginnners a little bit about designing Web-based applications with PHP andmySQL in the process.

TABLE OF CONTENTS:
  1. Cracking The Vault (part 1)
  2. Just Another Day At The Office
  3. An Evil Plan Is Born
  4. Setting The Ground Rules
  5. Design Time
  6. Start Me Up
  7. Entry Points
  8. Seeding The System
  9. Red And Green Clouds
  10. Digging Deeper
  11. Basic Maintenance
  12. The D Word
By: Vikram Vaswani, (c) Melonfire
Rating: starstarstarstarstar / 2
May 14, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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.zip

After 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.

 
 
>>> More PHP Articles          >>> More By Vikram Vaswani, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: