This is a standard SQL generator file used to create a product's database. This example creates the database behind the Budget Server product.

Instant Framework allows a developer to rapidly create a direct web interface atop the underlying database, by cutting and pasting Instant Framework screens that work directly on these tables. Because Instant Framework screens are small and easily modified, the interface can be built very quickly. An interface can be built atop any database, including a the datbase from an existing product or business system.

Soon the Instant Framework will read an SQL file like this and generate the complete web interface itself, so the developer need only customize it.

# This is the generator file for the budgeting database.
# BE CAREFUL - running this script deletes any existing tables
# and their contents!

use davidr1

drop table if exists site;
create table site
(
  site_id MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  site_name VARCHAR(40) NOT NULL
);
create index i_site_name on site(site_name);

drop table if exists product_group;
create table product_group
(
  product_group_id MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  product_group_name VARCHAR(40) NOT NULL
);

insert into product_group values (1, 'Core Networks');
insert into product_group values (2, 'News Channel');
insert into product_group values (3, 'UPN');
insert into product_group values (4, 'MAS Arizona');
insert into product_group values (5, 'Padres');

drop table if exists person;
create table person
(
  person_id MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  login_id CHAR(8) NOT NULL,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  email VARCHAR(40) NULL,
  password VARCHAR(20) NOT NULL,
  role ENUM('Account Exec', 'Sales Manager', 'Administrator') NOT NULL,
  site_id MEDIUMINT UNSIGNED NULL,
  sales_manager_id MEDIUMINT UNSIGNED NULL
);
create index i_person_login_id on person(login_id);

drop table if exists client;
create table client
(
  client_id MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  client_name VARCHAR(60) NOT NULL,
  type ENUM('Local', 'National') NOT NULL
);

drop table if exists sales_entry;
create table sales_entry
(
  sales_entry_id MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  month SMALLINT UNSIGNED NOT NULL, 
  year MEDIUMINT UNSIGNED NOT NULL, 
  amount DECIMAL(9,2) NOT NULL,
  person_id MEDIUMINT UNSIGNED NOT NULL, 
  client_id MEDIUMINT UNSIGNED NOT NULL, 
  site_id MEDIUMINT UNSIGNED NOT NULL, 
  type ENUM('Actual', 'Projected', 'Budgeted') NOT NULL,
  product_group_id MEDIUMINT UNSIGNED NOT NULL
);
create index i_sales_entry on sales_entry(site_id, year, month);

drop table if exists broadcast_weeks;
create table broadcast_weeks 
(
  year MEDIUMINT UNSIGNED PRIMARY KEY,
  january SMALLINT UNSIGNED NOT NULL, 
  february SMALLINT UNSIGNED NOT NULL, 
  march SMALLINT UNSIGNED NOT NULL, 
  april SMALLINT UNSIGNED NOT NULL, 
  may SMALLINT UNSIGNED NOT NULL, 
  june SMALLINT UNSIGNED NOT NULL, 
  july SMALLINT UNSIGNED NOT NULL, 
  august SMALLINT UNSIGNED NOT NULL, 
  september SMALLINT UNSIGNED NOT NULL, 
  october SMALLINT UNSIGNED NOT NULL, 
  november SMALLINT UNSIGNED NOT NULL, 
  december SMALLINT UNSIGNED NOT NULL
);

drop table if exists client_alias;
create table client_alias
(
  client_id MEDIUMINT UNSIGNED,
  client_name VARCHAR(60) PRIMARY KEY
);
create index i_alias_name on client_alias(client_name);