Apache Web Hosting, Affordable, PHP5, Comcast - Linux Blog

– Table structure for table `survey_answer` — CREATE TABLE `survey_answer` ( `survey_id` int(11) NOT NULL, `question_id` int(11) NOT NULL, `answer` varchar(30) NOT NULL, PRIMARY KEY (`survey_id`,`question_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `survey_answer`: — `question_id` — `survey_question` -> `id` — `survey_id` — `survey` -> `id` — — ——————————————————– — — Table structure for table `survey_question` — CREATE TABLE `survey_question` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– Summary We improved our data structure’s implementation by assessing the responsible person for each data element and by storing this information into column comments. We then saw how to use privileges and views to improve security, how to choose the best storage engine per table, and how to benefit from foreign key constraints. Performance issues were considered, and then we were presented with the final model for the car dealer’s case study.

– — Table structure for table `road_test` — CREATE TABLE `road_test` ( `internal_number` int(11) NOT NULL, `date` date NOT NULL, `customer_id` int(11) NOT NULL, `salesperson_id` int(11) NOT NULL, `customer_comments` varchar(255) NOT NULL, PRIMARY KEY (`internal_number`,`date`,`customer_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `road_test`: — `customer_id` — `person` -> `id` — `internal_number` — `vehicle` -> `internal_number` — `salesperson_id` — `person` -> `id` — — ——————————————————– — — Table structure for table `survey` — CREATE TABLE `survey` ( `id` int(11) NOT NULL, `date` date NOT NULL, `customer_id` int(11) NOT NULL, `salesperson_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `survey`: — `customer_id` — `person` -> `id` — `salesperson_id` — `person` -> `id` — — ——————————————————– –

Other tables — — Table structure for table `parameters` — CREATE TABLE `parameters` ( `dealer_number` varchar(30) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `organization` — CREATE TABLE `organization` ( `id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `address` varchar(300) NOT NULL, `city` varchar(50) NOT NULL, `postal_code` varchar(20) NOT NULL, `phone_area` varchar(20) NOT NULL, `phone_number` varchar(20) NOT NULL, `phone_extension` varchar(20) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `organization`: — `category_id` — `organization_category` -> `id` — — ——————————————————– — — Table structure for table `organization_category` — CREATE TABLE `organization_category` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————–

– — ——————————————————– — — Table structure for table `sale_financing` — CREATE TABLE `sale_financing` ( `internal_number` int(11) NOT NULL auto_increment, `date_sold` date NOT NULL, `financial_id` int(11) NOT NULL, `interest_rate` decimal(9,4) NOT NULL, `credit_rate_id` int(11) NOT NULL, `first_payment_date` date NOT NULL, `term_years` int(11) NOT NULL, PRIMARY KEY (`internal_number`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; — — RELATIONS FOR TABLE `sale_financing`: — `credit_rate_id` — `credit_rate` -> `id` — `financial_id` — `organization` -> `id` — `internal_number` — `vehicle` -> `internal_number` — — ——————————————————– — — Table structure for table `tax_rate` — CREATE TABLE `tax_rate` ( `start_date` date NOT NULL, `end_date` date NOT NULL, `rate` decimal(9,4) NOT NULL, PRIMARY KEY (`start_date`,`end_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————–

– — Table structure for table `credit_rate` — CREATE TABLE `credit_rate` ( `id` int(11) NOT NULL, `description` char(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `sale` — CREATE TABLE `sale` ( `internal_number` int(11) NOT NULL, `date_sold` date NOT NULL, `condition_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `salesperson_id` int(11) NOT NULL, `base_price` decimal(9,2) NOT NULL, `insurance_id` int(11) NOT NULL, `insurance_policy_number` varchar(40) NOT NULL, `preparation_cost` decimal(9,2) NOT NULL, `exchange_vehicle_id` int(11) NOT NULL, `exchange_price` decimal(9,2) NOT NULL, `down_payment` decimal(9,2) NOT NULL, PRIMARY KEY (`internal_number`,`date_sold`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `sale`: — `condition_id` — `condition` -> `id` — `customer_id` — `person` -> `id` — `exchange_vehicle_id` — `vehicle` -> `internal_number` — `insurance_id` — `organization` -> `id` — `internal_number` — `vehicle` -> `internal_number` — `salesperson_id` — `person` -> `id`

– — RELATIONS FOR TABLE `person`: — `category_id` — `person_category` -> `id` — `gender_id` — `gender` -> `id` — `salutation_id` — `salutation` -> `id` — — ——————————————————– — — Table structure for table `person_category` — CREATE TABLE `person_category` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `salutation` — CREATE TABLE `salutation` ( `id` TINYINT(4) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– Sale — — Table structure for table `condition` — CREATE TABLE `condition` ( `id` int(11) NOT NULL, `description` char(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————–

) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `vehicle_event`: — `event_id` — `event` -> `id` — `internal_number` — `vehicle` -> `internal_number` — `person_id` — `person` -> `id` — Person — — Table structure for table `gender` — CREATE TABLE `gender` ( `id` TINYINT(4) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `person` — CREATE TABLE `person` ( `id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `gender_id` TINYINT(4) NOT NULL, `salutation_id` TINYINT(4) NOT NULL, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `address` varchar(300) NOT NULL, `city` varchar(50) NOT NULL, `postal_code` varchar(20) NOT NULL, `phone_area` varchar(20) NOT NULL, `phone_number` varchar(20) NOT NULL, `phone_extension` varchar(20) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `vehicle` ( `internal_number` int(11) NOT NULL, `serial_number` varchar(50) NOT NULL, `brand_id` int(11) NOT NULL, `model_id` int(11) NOT NULL, `year` year(4) NOT NULL, `physical_key_id` int(11) NOT NULL, `color_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, PRIMARY KEY (`internal_number`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `vehicle`: — `brand_id` — `brand` -> `id` — `category_id` — `vehicle_category` -> `id` — `color_id` — `brand_color` -> `id` — `model_id` — `brand_model` -> `id` — — ——————————————————– — — Table structure for table `vehicle_category` — CREATE TABLE `vehicle_category` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `vehicle_event` — CREATE TABLE `vehicle_event` ( `internal_number` int(11) NOT NULL, `moment` date NOT NULL, `event_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`internal_number`,`moment`)

CREATE TABLE `brand_color` ( `brand_id` int(11) NOT NULL, `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`brand_id`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `brand_color`: — `brand_id` — `brand` -> `id` — — ——————————————————– — — Table structure for table `brand_model` — CREATE TABLE `brand_model` ( `brand_id` int(11) NOT NULL, `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`brand_id`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — — RELATIONS FOR TABLE `brand_model`: — `brand_id` — `brand` -> `id` — — ——————————————————– — — Table structure for table `event` — CREATE TABLE `event` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `vehicle` –

The following CREATE TABLE commands come directly from the Export feature of phpMyAdmin. To access this feature, simply open a database and choose the Export menu, then select all the tables, click the SQL checkbox and hit Go. The commands have been grouped into smaller chunks of related tables, even if ultimately there are relations between the tables of those groups. You’ll notice that phpMyAdmin adds in the form of comments in the export file information about the relations with other tables. Another point to note: the primary key for most tables is id, an integer. Thus, a column pointing to the id column of table brand is named brand_id. Vehicle — — Table structure for table `brand` — CREATE TABLE `brand` ( `id` int(11) NOT NULL, `description` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — ——————————————————– — — Table structure for table `brand_color` –