Data Management Made Easy Using Nennius: Creating a Web Application - Creating SQL Tables
(Page 3 of 5 )
Creating SQL Tables In many cases, Nennius will be configured to manage p-existing data. However, this is not the case for our sample application. We will need to create three basic SQL tables: one to hold Nennius users, one to hold the news releases we will be creating, and another to act as a Threshold lookup table. The queries for doing this are shown below:
-- create basic news table to contain date, title, and body of release
CREATE TABLE `news` (
`id` int(3) unsigned zerofill NOT NULL auto_increment,
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`title` varchar(100) NOT NULL default '',
`body` text NOT NULL,
PRIMARY KEY (`id`)
);
-- create nennius user table holding basic information about user record(s)
CREATE TABLE `users` (
`id` int(2) unsigned zerofill NOT NULL auto_increment,
`username` varchar(35) NOT NULL default '',
`password` varchar(35) NOT NULL default '',
`name` varchar(35) NOT NULL default '',
`email` varchar(35) NOT NULL default '',
`threshold_id` int(2) unsigned zerofill NOT NULL default '00',
PRIMARY KEY (`id`),
KEY `threshold_id` (`threshold_id`)
);
-- insert default user (for demo purposes) of 'admin' w/ password 'admin'
INSERT INTO `users` VALUES (01, 'admin', 'admin', 0, 'admin@corporatewebsite.com', 06);
-- create threshold lookup table
CREATE TABLE `thresholds` (
`id` int(2) unsigned zerofill NOT NULL auto_increment,
`name` varchar(25) NOT NULL default '',
`value` int(3) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
);
-- insert all Nennius-supported threshold types into lookup table
INSERT INTO `thresholds` VALUES (01, 'PUBLIC', 0);
INSERT INTO `thresholds` VALUES (02, 'CLIENT', 10);
INSERT INTO `thresholds` VALUES (03, 'USER', 30);
INSERT INTO `thresholds` VALUES (04, 'REVIEWER', 50);
INSERT INTO `thresholds` VALUES (05, 'MANAGER', 70);
INSERT INTO `thresholds` VALUES (06, 'ADMIN', 90);
As you can see, the structures for our news releases and users tables are fairly basic. The threshold table may not be as intuitive, but we will take a closer at this in Part 3. For now we will continue on to setup our application.
Next: Creating a Nennius Component, Defining the Entry-Point, and the Descriptor File >>
More MySQL Articles
More By Brian Vaughn
