Creating SQL Tables - MySQL

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.