USE quasicms; CREATE TABLE `usergroup` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_usergroup PRIMARY KEY (`id`), UNIQUE KEY uk_usergroup (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO usergroup (name) VALUES ('Everyone'); INSERT INTO usergroup (name) VALUES ('Members'); INSERT INTO usergroup (name) VALUES ('Customers'); INSERT INTO usergroup (name) VALUES ('Designers'); INSERT INTO usergroup (name) VALUES ('Administrators'); INSERT INTO usergroup (name) VALUES ('Engineers'); INSERT INTO usergroup (name) VALUES ('Manufacturers'); INSERT INTO usergroup (name) VALUES ('Suppliers'); INSERT INTO usergroup (name) VALUES ('Editors'); CREATE TABLE `permission_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_permission_type PRIMARY KEY (`id`), UNIQUE KEY uk_permssion_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO permission_type (name) VALUES ('None'); INSERT INTO permission_type (name) VALUES ('View'); INSERT INTO permission_type (name) VALUES ('Modify'); INSERT INTO permission_type (name) VALUES ('Delete'); /*********************************************************************** Notes on the person table and its associate dependant tables 1. INSERT INTO person(is_virtual) VALUES(false); 2. INSERT INTO account(username,password,now(),true,1,1,person.id) 3. INSERT INTO address, using person.id FK So, we create a row in the person table using data in other tables. This means we need to insert the person first, get the insert id, then insert the others with the id and then update the person row with the ids from the others to maintain referential integrity. Case 2 illustrates the purpose of the design - we want to allow members to send gifts to friends periodically. So, they can create a new "virtual" person with name and address and store it making it available as an selection when they choose a shipping address during a checkout. This way, we can have other names and addresses tied to the Person instead of the Account thus keeping the one to one relationship of person - account while allowing multiple names and addresses within normal forms (without duplication). Any additional name and addresses must be other than type primary and type primary must exist for a Person in order for them to make a purchase. Use case - Member Registration: * Chooses username and login, optional address info (this would define them as a customer) .. 1. create person object with defaults 2. create account object using person.id - an account is tied to a single person 3. if address info, create address object FK person_id gets person.id, type is set to primary else - error. 4. repeat for phone, email, shipping, billing - all optional Use case - Member addition (ie, adds a gift address/person ): 1. User fills in fields - collect and perform inserts as above 2. Set address.person_id to new person.id 3. Set type to billing, friend, etc - mandatory, if not, then this defaults to an address change Use case - Administrator addition: 1. If no account exists, steps 1 - 2 of Use Case 1. 2. Else, (or after 1.) proceed as in Use Case 2 assigning FK id columns to an existing or newly created row in person (eg. address.person_id = person.id) ******************************************************************************/ CREATE TABLE `person` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name_prefix` VARCHAR(32) DEFAULT '', -- 'eg. Mr., Ms ..', `first_name` VARCHAR(256) NOT NULL, `middle_name` VARCHAR(128) DEFAULT '', `last_name` VARCHAR(256) NOT NULL, `name_suffix` VARCHAR(32) DEFAULT '', -- 'eg. PhD. MD, etc ..', `nick_name` VARCHAR(128) DEFAULT 'Anonymous', `email_address` VARCHAR(128) NOT NULL, `phone_number` VARCHAR(32) DEFAULT 'N/A', `avatar_uri` VARCHAR(256) , -- optional avatar image for person `company_name` VARCHAR(256) DEFAULT '', `owner_person_id` MEDIUMINT UNSIGNED, `is_virtual` BOOL DEFAULT FALSE, -- true for persons in members' addressbook, suppliers, manufacturers ... CONSTRAINT pk_person PRIMARY KEY (`id`), INDEX idx_person_firstname (`first_name`), INDEX idx_person_last_name (`last_name`), INDEX idx_person_email_address (`email_address`), -- INDEX idx_person_address (`address_id`), FOREIGN KEY (`owner_person_id`) REFERENCES person(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `account_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_account_status_type PRIMARY KEY (`id`), UNIQUE KEY uk_account_status_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO account_status_type (name) VALUES ('Active'); INSERT INTO account_status_type (name) VALUES ('Cancelled'); INSERT INTO account_status_type (name) VALUES ('Suspended'); CREATE TABLE `account_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_account_type PRIMARY KEY (`id`), UNIQUE KEY uk_account_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO account_type (name) VALUES ('Member'); INSERT INTO account_type (name) VALUES ('Customer'); INSERT INTO account_type (name) VALUES ('Employee'); INSERT INTO account_type (name) VALUES ('Administrator'); INSERT INTO account_type (name) VALUES ('Supplier'); INSERT INTO account_type (name) VALUES ('Manufacturer'); CREATE TABLE `account` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `registration_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `username` VARCHAR(128) NOT NULL, `password` VARCHAR(128) NOT NULL, `notes` TEXT, `last_login` TIMESTAMP, `login_count` INT UNSIGNED DEFAULT 0, `online` BOOL NOT NULL DEFAULT FALSE, `onetime_password` BOOL NOT NULL DEFAULT FALSE, `valid_password` BOOL NOT NULL DEFAULT TRUE, `type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `status_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `person_id` MEDIUMINT UNSIGNED NOT NULL, CONSTRAINT pk_account PRIMARY KEY (`id`), UNIQUE KEY idx_account_username (`username`), UNIQUE KEY idx_account_person (`person_id`), INDEX idx_account_type (`type_id`), INDEX idx_account_status (`status_id`), FOREIGN KEY (`person_id`) REFERENCES person(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'Corresponds to the normal users | customers | members table'; CREATE TABLE `name_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_name_type PRIMARY KEY (`id`), UNIQUE KEY idx_name_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO name_type (name) VALUES ('Primary'); INSERT INTO name_type (name) VALUES ('Shipping'); INSERT INTO name_type (name) VALUES ('Billing'); INSERT INTO name_type (name) VALUES ('Historical'); -- people sometimes change names .. INSERT INTO name_type (name) VALUES ('Alias'); INSERT INTO name_type (name) VALUES ('Friend'); CREATE TABLE `country_type` ( `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL DEFAULT '', `iso_code_2` CHAR(2) NOT NULL DEFAULT '', `iso_code_3` CHAR(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `idx_country_name` (`name`), INDEX `idx_iso_2` (`iso_code_2`), INDEX `idx_iso_3` (`iso_code_3`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `zone_type` ( `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, `country_id` SMALLINT UNSIGNED NOT NULL DEFAULT 255, `code` VARCHAR(32) NOT NULL DEFAULT 'N/A', PRIMARY KEY (`id`), UNIQUE KEY uk_zone_type_name (`name`), INDEX idx_zone_code (`code`), FOREIGN KEY (`country_id`) REFERENCES country_type(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `address_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_address_type PRIMARY KEY (`id`), UNIQUE KEY idx_address_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO address_type (name) VALUES ('Primary'); INSERT INTO address_type (name) VALUES ('Shipping'); INSERT INTO address_type (name) VALUES ('Billing'); INSERT INTO address_type (name) VALUES ('Company'); INSERT INTO address_type (name) VALUES ('Friend'); INSERT INTO address_type (name) VALUES ('Manufacturer'); INSERT INTO address_type (name) VALUES ('Supplier'); INSERT INTO address_type (name) VALUES ('Historical'); /*historical includes typo fixes and moved - for order lookups, eventually dated should move to address_history */ CREATE TABLE `address` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(256) DEFAULT 'My Address', -- for human reference, eg. "Dad" or "Uncle Ed" `person_id` MEDIUMINT UNSIGNED NOT NULL, `street_1` VARCHAR(256) , `street_2` VARCHAR(256) , `suburb` VARCHAR(256) , `city` VARCHAR(256) , `county` VARCHAR(256) , -- text version of zone/district `zone_id` SMALLINT UNSIGNED NOT NULL DEFAULT 13, -- 'state, province or district `country_id` SMALLINT UNSIGNED NOT NULL DEFAULT 223, `postal_code` VARCHAR(32), -- get a table for this `is_current` BOOL NOT NULL DEFAULT TRUE, `type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modification_date` TIMESTAMP, CONSTRAINT pk_address PRIMARY KEY (`id`), INDEX idx_address_person (`id`), INDEX idx_address_type (`type_id`), INDEX idx_address_zone (`zone_id`), INDEX idx_address_country (`country_id`), FOREIGN KEY (`person_id`) REFERENCES person(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `product_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_product_type PRIMARY KEY (`id`), UNIQUE KEY idx_product_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO product_type (name) VALUES ('Service'); INSERT INTO product_type (name) VALUES ('Storefront'); INSERT INTO product_type (name) VALUES ('Internal'); INSERT INTO product_type (name) VALUES ('Assembly'); INSERT INTO product_type (name) VALUES ('Download'); INSERT INTO product_type (name) VALUES ('Virtual'); INSERT INTO product_type (name) VALUES ('Other'); CREATE TABLE `product_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_product_status_type PRIMARY KEY (`id`), UNIQUE KEY idx_product_status_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO product_status_type (name) VALUES ('Restricted'); INSERT INTO product_status_type (name) VALUES ('Active'); INSERT INTO product_status_type (name) VALUES ('Disabled'); INSERT INTO product_status_type (name) VALUES ('Retired'); CREATE TABLE `product` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `manufacturer_id` MEDIUMINT UNSIGNED DEFAULT 1, -- in house `supplier_id` MEDIUMINT UNSIGNED DEFAULT 1, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` VARCHAR(128) NOT NULL, `model` VARCHAR(128) NOT NULL, `short_description` VARCHAR(128) , `long_description` TEXT, `msrp` DECIMAL(12,2) UNSIGNED, `wholesale_price` DECIMAL(12,2) UNSIGNED, `retail_price` DECIMAL(12,2) UNSIGNED, `cost` DECIMAL(12,2) UNSIGNED, `weight` FLOAT(10,6) UNSIGNED, -- ounces .. `height` FLOAT(10,6) UNSIGNED, -- inches, X .. `width` FLOAT(10,6) UNSIGNED, -- inches, Y `depth` FLOAT(10,6) UNSIGNED, -- inches, Z `is_virtual` BOOL NOT NULL DEFAULT FALSE, `type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `status_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, -- active, disabled .. `view_count` BIGINT UNSIGNED, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, -- ie. none .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 2, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_product PRIMARY KEY (`id`), UNIQUE KEY (`model`), INDEX idx_product_retail (`retail_price`), INDEX idx_product_type (`type_id`), INDEX idx_product_status (`status_id`), INDEX idx_product_public_perms (`public_permissions_id`), INDEX idx_product_user_perms (`user_permissions_id`), INDEX idx_product_group_perms (`group_permissions_id`), FOREIGN KEY (`manufacturer_id`) REFERENCES person(`id`) ON DELETE SET NULL, FOREIGN KEY (`supplier_id`) REFERENCES person(`id`) ON DELETE SET NULL ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `image_size_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_image_size_type PRIMARY KEY (`id`), UNIQUE KEY idx_image_size_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO image_size_type (name) VALUES ('Icon'); INSERT INTO image_size_type (name) VALUES ('Thumb'); INSERT INTO image_size_type (name) VALUES ('Small'); INSERT INTO image_size_type (name) VALUES ('Medium'); INSERT INTO image_size_type (name) VALUES ('Large'); INSERT INTO image_size_type (name) VALUES ('FullScreen'); INSERT INTO image_size_type (name) VALUES ('Intergalactic'); CREATE TABLE `product_image` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `product_id` MEDIUMINT UNSIGNED NOT NULL, `title` VARCHAR(128), `alt_tag` VARCHAR(128), `description` VARCHAR(256) , `uri` VARCHAR(256), `x_size` SMALLINT UNSIGNED, -- pixels, X .. `y_size` SMALLINT UNSIGNED, -- pixels, Y `size_type` TINYINT UNSIGNED DEFAULT 2, CONSTRAINT pk_product_image PRIMARY KEY (`id`), INDEX idx_size_type(`size_type`), FOREIGN KEY (`product_id`) REFERENCES product(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `order_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_order_status_type PRIMARY KEY (`id`), UNIQUE KEY idx_order_status_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO order_status_type (name) VALUES ('Shopping'); -- ie. in the cart .. INSERT INTO order_status_type (name) VALUES ('Pending'); INSERT INTO order_status_type (name) VALUES ('Paid'); INSERT INTO order_status_type (name) VALUES ('Processing'); INSERT INTO order_status_type (name) VALUES ('Packaged'); INSERT INTO order_status_type (name) VALUES ('Shipped'); INSERT INTO order_status_type (name) VALUES ('Cancelled'); INSERT INTO order_status_type (name) VALUES ('Returned'); -- Note: may need to move to order_item .. INSERT INTO order_status_type (name) VALUES ('Problem'); CREATE TABLE `tax_rate` ( `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `zone_id` SMALLINT UNSIGNED NOT NULL, -- 'state, province or district `rate` DECIMAL(4,4) UNSIGNED NOT NULL, CONSTRAINT pk_tax_rate PRIMARY KEY (`id`), UNIQUE INDEX idx_tax_zone (`zone_id`), INDEX idx_tax_rate(`rate`), FOREIGN KEY (`zone_id`) REFERENCES zone_type(`id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; -- INSERT INTO tax_rate ( `rate` ) VALUES (19.6); -- INSERT INTO tax_rate ( `rate` ) VALUES (5.5); CREATE TABLE `shipping_rate` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `rate` DECIMAL(2,2) NOT NULL, `zone_id` SMALLINT UNSIGNED NOT NULL, -- 'state, province or district CONSTRAINT pk_shipping_rate PRIMARY KEY (`id`), UNIQUE INDEX idx_shipping_zone (`zone_id`), INDEX idx_shipping_rate(`id`), FOREIGN KEY (`zone_id`) REFERENCES zone_type(`id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; -- INSERT INTO shipping_rate ( `rate` ) VALUES (19.6); -- INSERT INTO shipping_rate ( `rate` ) VALUES (5.5); CREATE TABLE `shipping_method` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(128) DEFAULT 'Shipping Method', -- for display `carrier` VARCHAR(128), `service_type` VARCHAR(128), `class_name` VARCHAR(128), `transit_time` VARCHAR(16), -- in days, eg. 2 - 3 `description` TEXT, `image_filename` VARCHAR(128), `active` BOOL DEFAULT FALSE, `is_international` BOOL DEFAULT FALSE, `test_mode` BOOL DEFAULT TRUE, `sort_order` TINYINT UNSIGNED DEFAULT 0, -- ie, first or top CONSTRAINT pk_shipping_method PRIMARY KEY (`id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Local Pickup', 'PickUp', 'Counter', 'NoClass', 'Pick up at the store.', '0',false); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','FIRST CLASS', 'USPS','Basic ground delivery','2 - 4',false); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','PRIORITY', 'USPS','Priority Mail','2 - 3',false); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','EXPRESS', 'USPS','Express Mail','1 - 2',false); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','Global Express Guaranteed', 'USPSIntlRateCalculator','Premium international service','3 - 5',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','Express Mail International', 'USPSIntlRateCalculator','Expedited International service','3 - 5',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','Priority Mail International', 'USPSIntlRateCalculator','Reliable, economical accelerated airmail','4 - 9',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('United States Postal Service', 'USPS','First Class Mail International', 'USPSIntlRateCalculator','Generic, no frills, low cost service','5 - 28',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXG','FEDEX_GROUND','Fedex', 'Basic ground service','4 - 5',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXE','FEDEX_2_DAY','Fedex','2 day service','2',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXE','STANDARD_OVERNIGHT','Fedex','Standard Overnight service','1',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXE','INTERNATIONAL_ECONOMY','Fedex','Economy International service','3 - 5',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXE','INTERNATIONAL_PRIORITY','Fedex','Priority International service',' 2 - 3 ',true); INSERT INTO shipping_method (title, carrier, service_type, class_name, description, transit_time, is_international) VALUES ('Federal Express', 'FDXE','INTERNATIONAL_FIRST','Fedex','First class International service','2',true); CREATE TABLE `payment_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_payment_type PRIMARY KEY (`id`), UNIQUE KEY idx_payment_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO payment_type (name) VALUES ('Online'); INSERT INTO payment_type (name) VALUES ('MailIn'); INSERT INTO payment_type (name) VALUES ('Cash'); INSERT INTO payment_type (name) VALUES ('Credit'); INSERT INTO payment_type (name) VALUES ('GiftCertificate'); INSERT INTO payment_type (name) VALUES ('Complimentary'); CREATE TABLE `payment_method` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(128) DEFAULT 'Payment Method', `service_provider` VARCHAR(128), `service_type` VARCHAR(128), `action_class_name` VARCHAR(128), `description` TEXT, `image_uri` VARCHAR(128), `active` BOOL DEFAULT FALSE, `requires_cc_number` BOOL DEFAULT FALSE, `save_cc_number` BOOL DEFAULT FALSE, `test_mode` BOOL DEFAULT TRUE, `sort_order` TINYINT UNSIGNED DEFAULT 0, -- ie, first or top `payment_type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_payment_method PRIMARY KEY (`id`), INDEX idx_payment_method_type(`payment_type_id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO `payment_method` ( title, service_provider, service_type, action_class_name, description, payment_type_id, active, sort_order) VALUES ('Check/Money Order', 'Our Store', 'Mail', 'PayByMailAction', 'Payment sent by mail', 3, true, 0); INSERT INTO `payment_method` ( title, service_provider, service_type, action_class_name, description, payment_type_id, sort_order) VALUES ('PayPal Express Checkout', 'Paypal.com', 'Express Checkout', 'PayPalNVPAction', 'Implementation of PayPal Express Checkout', 1, 2); INSERT INTO `payment_method` ( title, service_provider, service_type, action_class_name, description, payment_type_id, sort_order) VALUES ('Credit Card', 'Authorize.net', 'Credit Card', 'AuthorizeNetAction', 'Authorize.net credit card payment processing', 1, 3); CREATE TABLE `paypal_transaction` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` BIGINT UNSIGNED NOT NULL, `correlation_id` VARCHAR(128), `transaction_id` VARCHAR(128), `pp_token` VARCHAR(128), `payer_id` VARCHAR (128), `payer_status` VARCHAR (128), `payment_status` VARCHAR (128), `ack_returned` VARCHAR(32), `api_action` VARCHAR(32), `time_stamp` DATETIME, `api_version` VARCHAR(4), `messages` TEXT, `amount` DECIMAL(12,2), /* `shipping_charged` DECIMAL(12,2), `handling_charged` DECIMAL(12,2), `tax` DECIMAL(12,2), `product_total` DECIMAL(12,2),*/ `pp_fee` DECIMAL(12,2), `payment_method_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_pp_transaction PRIMARY KEY (`id`), INDEX idx_pp_transaction_correlationid(`correlation_id`), INDEX idx_pp_transaction_orderid(`order_id`), INDEX idx_pp_transaction_payment_methodid(`payment_method_id`), FOREIGN KEY (`order_id`) REFERENCES `order`(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `authorize_net_transaction` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` BIGINT UNSIGNED NOT NULL, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `response_code` VARCHAR(8), `response_subcode` VARCHAR(8), `response_reason_code` VARCHAR(8), `response_reason_text` TEXT, `authorization_code` VARCHAR(8), `transaction_id` VARCHAR(128), `transaction_type` VARCHAR(128), `amount` DECIMAL(12,2), `avs_response_code` VARCHAR(8), `ccv_response_code` VARCHAR(8), `cav_response_code` VARCHAR(8), CONSTRAINT pk_authnet_transaction PRIMARY KEY (`id`), INDEX idx_authnet_transaction_transactionid(`transaction_id`), INDEX idx_authnet_transaction_orderid(`order_id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `order_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_order_type PRIMARY KEY (`id`), UNIQUE KEY idx_order_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO order_type (name) VALUES ('Normal'); INSERT INTO order_type (name) VALUES ('Internal'); INSERT INTO order_type (name) VALUES ('Employee'); INSERT INTO order_type (name) VALUES ('Affiliate'); CREATE TABLE `order` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `account_id` MEDIUMINT UNSIGNED NOT NULL DEFAULT 1, -- in house `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modification_date` TIMESTAMP, `completion_date` DATETIME DEFAULT '0000-00-00 00:00:00', `product_total_cost` DECIMAL(12,2), `shipping_cost` DECIMAL(12,2), `shipping_charged` DECIMAL(12,2), -- `handling_cost` DECIMAL(12,2), `handling_charged` DECIMAL(12,2), `tax` DECIMAL(12,2), `product_total_charged` DECIMAL(12,2), `shipping_name_prefix` VARCHAR(8), `shipping_first_name` VARCHAR(128), `shipping_middle_name` VARCHAR(128), `shipping_last_name` VARCHAR(128), `shipping_name_suffix` VARCHAR(8), `shipping_company` VARCHAR(128), `shipping_street1` VARCHAR(128), `shipping_street2` VARCHAR(128), `shipping_suburb` VARCHAR(128), `shipping_county` VARCHAR(128), `shipping_city` VARCHAR(128), `shipping_zone_id` SMALLINT UNSIGNED, `shipping_country_id` SMALLINT UNSIGNED, `shipping_postal_code` VARCHAR(16), `billing_name_prefix` VARCHAR(8), `billing_first_name` VARCHAR(128), `billing_middle_name` VARCHAR(128), `billing_last_name` VARCHAR(128), `billing_name_suffix` VARCHAR(8), `billing_company` VARCHAR(128), `billing_street1` VARCHAR(128), `billing_street2` VARCHAR(128), `billing_suburb` VARCHAR(128), `billing_county` VARCHAR(128), `billing_city` VARCHAR(128), `billing_zone_id` SMALLINT UNSIGNED, `billing_country_id` SMALLINT UNSIGNED, `billing_postal_code` VARCHAR(16), `notes` TEXT , `shipping_method_id` TINYINT UNSIGNED DEFAULT 1, `payment_method_id` TINYINT UNSIGNED DEFAULT 1, `status_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `type_id` TINYINT UNSIGNED DEFAULT 1, CONSTRAINT pk_order PRIMARY KEY (`id`), INDEX idx_order_account (`account_id`), INDEX idx_order_shipping_method (`shipping_method_id`), INDEX idx_order_payment_method (`payment_method_id`), INDEX idx_order_status (`status_id`), INDEX idx_order_type (`type_id`), INDEX idx_order_shipping_zone (`shipping_zone_id`), INDEX idx_order_billing_zone (`billing_zone_id`), INDEX idx_order_shipping_country (`shipping_country_id`), INDEX idx_order_billing_country (`billing_country_id`), FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `tracking_number` ( `order_id` BIGINT UNSIGNED NOT NULL, `number` VARCHAR(64), CONSTRAINT pk_tracking_number PRIMARY KEY (`order_id`, `number`), INDEX idx_tracking_number_orderid(`order_id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `order_change_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64), CONSTRAINT pk_order_change_type PRIMARY KEY (`id`), UNIQUE KEY idx_order_change_type(name) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO order_change_type (name) VALUES ('Refund'); INSERT INTO order_change_type (name) VALUES ('OrderDiscount'); INSERT INTO order_change_type (name) VALUES ('ItemDiscount'); INSERT INTO order_change_type (name) VALUES ('ItemQuantity'); INSERT INTO order_change_type (name) VALUES ('ItemAddition'); -- i _think_ this can support back orders INSERT INTO order_change_type (name) VALUES ('ShippingAddition'); /* Note: we may have to add discount type, nullable ..*/ CREATE TABLE `order_change` ( `order_id` BIGINT UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `notes` TEXT, `value` DECIMAL(12,2), `type_id` TINYINT UNSIGNED NOT NULL, CONSTRAINT pk_order_change PRIMARY KEY (`order_id`,`date`), INDEX `idx_order_change_type` (`type_id`), FOREIGN KEY (`order_id`) REFERENCES `order`(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `order_status_history` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` BIGINT UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `notes` TEXT , `status_id` TINYINT UNSIGNED NOT NULL, CONSTRAINT pk_order_status_history PRIMARY KEY (`id`), INDEX idx_order_status_history_order (`order_id`), INDEX idx_order_status_history_status (`status_id`), FOREIGN KEY (`order_id`) REFERENCES `order`(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `shopping_cart` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modification` TIMESTAMP, `account_id` MEDIUMINT UNSIGNED NOT NULL, CONSTRAINT pk_shopping_cart PRIMARY KEY (`id`), -- INDEX idx_shopping_cart_status (`order_id`), FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `block_location_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_block_location_type PRIMARY KEY (`id`), UNIQUE KEY uk_block_location_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO block_location_type (name) VALUES ('PageHeader'); INSERT INTO block_location_type (name) VALUES ('PageFooter'); INSERT INTO block_location_type (name) VALUES ('RightPanel'); INSERT INTO block_location_type (name) VALUES ('CenterPanel'); INSERT INTO block_location_type (name) VALUES ('LeftPanel'); INSERT INTO block_location_type (name) VALUES ('ExtraPanel1'); INSERT INTO block_location_type (name) VALUES ('ExtraPanel2'); INSERT INTO block_location_type (name) VALUES ('ExtraPanel3'); INSERT INTO block_location_type (name) VALUES ('ExtraPanel4'); INSERT INTO block_location_type (name) VALUES ('PageBody'); -- ie.
is parent, render outside container .. CREATE TABLE `content_block` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference, also used for CSS id `cssclass` VARCHAR(128) , -- CSS class `title` VARCHAR(128) , -- optionally visible text `description` VARCHAR(256) , -- optionally visible text, eg. on list pages `show_title` BOOL NOT NULL DEFAULT FALSE, `show_description` BOOL NOT NULL DEFAULT FALSE, `collapsable` BOOL NOT NULL DEFAULT FALSE , -- optionally visible text, eg. on list pages `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0, -- ie, first or top in ul `parent_content_block_id` MEDIUMINT UNSIGNED, `location_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_content_block PRIMARY KEY (`id`), INDEX idx_content_block_parent (`parent_content_block_id`), INDEX idx_content_block_location (`location_id`), UNIQUE KEY idx_content_block_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `content_category` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference `title` VARCHAR(128) , -- optionally visible text `description` VARCHAR(256) , -- optionally visible text, eg. on list pages `image_uri` VARCHAR(256) , -- optional image for the category `parent_content_category_id` MEDIUMINT UNSIGNED, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 2, -- view .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_content_category PRIMARY KEY (`id`), INDEX idx_content_category_parent (`parent_content_category_id`), INDEX idx_content_category_public_perms (`public_permissions_id`), INDEX idx_content_category_user_perms (`user_permissions_id`), INDEX idx_content_category_group_perms (`group_permissions_id`), UNIQUE KEY idx_content_category_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `product_category` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference `title` VARCHAR(128) , -- optionally visible text `description` VARCHAR(256) , -- optionally visible text, eg. on list pages `image_uri` VARCHAR(256) , -- optional image for the category `parent_product_category_id` MEDIUMINT UNSIGNED, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 2, -- view .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_product_category PRIMARY KEY (`id`), INDEX idx_product_category_parent (`parent_product_category_id`), INDEX idx_product_category_public_perms (`public_permissions_id`), INDEX idx_product_category_user_perms (`user_permissions_id`), INDEX idx_product_category_group_perms (`group_permissions_id`), UNIQUE KEY idx_product_category_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `content_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_content_status_type PRIMARY KEY (`id`), UNIQUE KEY uk_content_status_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `content_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_content_type PRIMARY KEY (`id`), UNIQUE KEY uk_content_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO content_status_type (name) VALUES ('Published'); INSERT INTO content_status_type (name) VALUES ('Unpublished'); INSERT INTO content_status_type (name) VALUES ('Draft'); INSERT INTO content_status_type (name) VALUES ('Internal'); -- eg. the home page center column, or "about us" .. INSERT INTO content_type (name) VALUES ('PageBody'); -- a news article, faq, blog - something likely listed in a pagebody with teasers .. INSERT INTO content_type (name) VALUES ('Article'); -- likely to be listed, or displayed and also can be in a shopping cart .. INSERT INTO content_type (name) VALUES ('Product'); INSERT INTO content_type (name) VALUES ('Image'); INSERT INTO content_type (name) VALUES ('Video'); INSERT INTO content_type (name) VALUES ('Audio'); INSERT INTO content_type (name) VALUES ('Comment'); INSERT INTO content_type (name) VALUES ('ForumPost'); INSERT INTO content_type (name) VALUES ('BlogPost'); -- little things that go near something else - a form instruction, image descript - small text area; ie a INSERT INTO content_type (name) VALUES ('Description'); CREATE TABLE `content_item` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, -- human reference, also used for CSS id `cssclass` VARCHAR(128) , -- CSS class `title` VARCHAR(128) , -- optionally visible text `description` VARCHAR(256) , -- optionally visible text `text` TEXT, -- use either this or uri, if this is null, we try to get uri .. `sort_order` MEDIUMINT UNSIGNED DEFAULT 0, -- ie, first or top in ul `show_title` BOOL NOT NULL DEFAULT TRUE, `show_description` BOOL NOT NULL DEFAULT FALSE, `show_creator` BOOL NOT NULL DEFAULT TRUE, `show_creation_date` BOOL NOT NULL DEFAULT TRUE, `show_last_modification` BOOL NOT NULL DEFAULT TRUE, `creator_id` MEDIUMINT UNSIGNED DEFAULT 1, `copyright_notice` VARCHAR(256), `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modification` TIMESTAMP, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 2, -- view .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, -- `is_virtual` BOOL DEFAULT FALSE, -- eg. from somewhere else in a frame .. `type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `status_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_content_item PRIMARY KEY (`id`), UNIQUE KEY uk_content_name (`name`), INDEX idx_content_title (`title`), INDEX idx_content_creator (`creator_id`), INDEX idx_content_type (`type_id`), INDEX idx_content_status (`status_id`), INDEX idx_content_public_perms (`public_permissions_id`), INDEX idx_content_user_perms (`user_permissions_id`), INDEX idx_content_group_perms (`group_permissions_id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `module` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, -- human reference, also used for CSS id `cssclass` VARCHAR(128) , -- CSS class `title` VARCHAR(128) , -- optionally visible text `description` VARCHAR(256) , -- optionally visible text `class_name` VARCHAR(256), -- name of the active class object, a "View" `show_title` BOOL NOT NULL DEFAULT TRUE, `show_description` BOOL NOT NULL DEFAULT FALSE, `content_block_id` MEDIUMINT UNSIGNED, `parent_module_id` INT UNSIGNED, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 2, -- ie. view .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_module PRIMARY KEY (`id`), UNIQUE KEY uk_module_name (`name`), FOREIGN KEY (`parent_module_id`) REFERENCES module(`id`), INDEX idx_module_block (`content_block_id`), INDEX idx_module_public_perms (`public_permissions_id`), INDEX idx_module_user_perms (`user_permissions_id`), INDEX idx_module_group_perms (`group_permissions_id`), INDEX idx_module_title (`title`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `page_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_page_type PRIMARY KEY (`id`), UNIQUE KEY uk_page_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO page_type (name) VALUES ('Basic'); INSERT INTO page_type (name) VALUES ('Home'); INSERT INTO page_type (name) VALUES ('Login'); INSERT INTO page_type (name) VALUES ('UserHome'); INSERT INTO page_type (name) VALUES ('EditContactInfo'); INSERT INTO page_type (name) VALUES ('EditPersons'); INSERT INTO page_type (name) VALUES ('Product'); INSERT INTO page_type (name) VALUES ('ProductList'); INSERT INTO page_type (name) VALUES ('ShoppingCartView'); INSERT INTO page_type (name) VALUES ('Checkout'); INSERT INTO page_type (name) VALUES ('Payment'); INSERT INTO page_type (name) VALUES ('ShippingInfo'); INSERT INTO page_type (name) VALUES ('OrderHistory'); INSERT INTO page_type (name) VALUES ('Forum'); INSERT INTO page_type (name) VALUES ('ForumList'); INSERT INTO page_type (name) VALUES ('Article'); INSERT INTO page_type (name) VALUES ('ArticleList'); INSERT INTO page_type (name) VALUES ('Admin'); CREATE TABLE `page_doc_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_page_doc_type PRIMARY KEY (`id`), UNIQUE KEY uk_page_doc_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES ('PDF'); INSERT INTO page_doc_type (name) VALUES ('TEXT'); INSERT INTO page_doc_type (name) VALUES ('RSS'); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); INSERT INTO page_doc_type (name) VALUES (''); CREATE TABLE `page_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_page_status_type PRIMARY KEY (`id`), UNIQUE KEY uk_page_status_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO page_status_type (name) VALUES ('Published'); INSERT INTO page_status_type (name) VALUES ('Unpublished'); INSERT INTO page_status_type (name) VALUES ('Draft'); INSERT INTO page_status_type (name) VALUES ('Internal'); CREATE TABLE `html_meta_tag`( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference `content` VARCHAR(256) , `type` ENUM('NAME','HTTP-EQUIV') DEFAULT 'NAME', CONSTRAINT pk_html_meta_tag PRIMARY KEY (`id`), INDEX idx_html_meta_tag_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `style_sheet`( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference `description` VARCHAR(256) , `filename` VARCHAR(128) , `type` ENUM('HTML','XML') DEFAULT 'HTML', CONSTRAINT pk_style_sheet PRIMARY KEY (`id`), INDEX idx_style_sheet_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `java_script`( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128) , -- human reference `description` VARCHAR(256) , `filename` VARCHAR(128) , CONSTRAINT pk_java_script PRIMARY KEY (`id`), INDEX idx_java_script_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `page` ( `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_modification` TIMESTAMP, `name` VARCHAR(128) , -- human reference `title` VARCHAR(256) , `uri` VARCHAR(256) DEFAULT 'index.php', `has_header` BOOL NOT NULL DEFAULT TRUE, `has_left_column` BOOL NOT NULL DEFAULT TRUE, `has_right_column` BOOL NOT NULL DEFAULT TRUE, `has_footer` BOOL NOT NULL DEFAULT TRUE, `public_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, -- ie. none .. `user_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `group_permissions_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `type_id` TINYINT UNSIGNED DEFAULT 1, `doc_type_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, `status_id` TINYINT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT pk_page PRIMARY KEY (`id`), INDEX idx_page_doc_type (`doc_type_id`), INDEX idx_page_type (`type_id`), INDEX idx_page_status (`status_id`), INDEX idx_page_public_perms (`public_permissions_id`), INDEX idx_page_user_perms (`user_permissions_id`), INDEX idx_page_group_perms (`group_permissions_id`), UNIQUE KEY idx_page_name (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `menu_status_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_menu_status_type PRIMARY KEY (`id`), UNIQUE KEY uk_menu_status_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO menu_status_type (name) VALUES ('Active'); INSERT INTO menu_status_type (name) VALUES ('Disabled'); CREATE TABLE `menu_item_type` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(128), CONSTRAINT pk_menu_item_type PRIMARY KEY (`id`), UNIQUE KEY uk_menu_item_type (`name`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO menu_item_type (name) VALUES ('ListMenuItem'); -- the usual