house script

# SQL Manager 2007 for MySQL 4.1.2.1
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : house


SET FOREIGN_KEY_CHECKS=0;

USE `house`;

#
# Structure for the `t_house_type` table :
#

DROP TABLE IF EXISTS `t_house_type`;

CREATE TABLE `t_house_type` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(128) COLLATE gbk_chinese_ci NOT NULL COMMENT '房型简单描述 如 两室一厅一卫',
  `total_room` TINYINT(4) DEFAULT NULL COMMENT '房间数',
  `total_hall` TINYINT(4) DEFAULT NULL COMMENT '厅数 s',
  `total_toilet` TINYINT(4) DEFAULT NULL COMMENT '卫生间数  T',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB COMMENT='InnoDB free: 11264 kB; InnoDB free: 11264 kB' CHECKSUM=0 DELAY_KEY_WRITE=0 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

#
# Structure for the `t_house` table :
#

DROP TABLE IF EXISTS `t_house`;

CREATE TABLE `t_house` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(512) COLLATE gbk_chinese_ci DEFAULT NULL,
  `address` VARCHAR(512) COLLATE gbk_chinese_ci DEFAULT NULL,
  `forward` VARCHAR(16) COLLATE gbk_chinese_ci DEFAULT NULL,
  `type_id` INTEGER(11) DEFAULT NULL COMMENT '房屋类型 关联表t_house_type',
  `build_area` DECIMAL(9,2) DEFAULT NULL COMMENT '建筑面积  L',
  `use_area` DECIMAL(9,2) DEFAULT NULL COMMENT '使用面积  L',
  `unit_price` DECIMAL(9,2) DEFAULT NULL,
  `total_prices` DECIMAL(9,2) DEFAULT NULL,
  `floor` SMALLINT(6) DEFAULT NULL,
  `total_floor` SMALLINT(6) DEFAULT NULL,
  `house_age` TINYINT(4) DEFAULT NULL COMMENT '房龄 s',
  `is_sold` BIT(1) DEFAULT NULL COMMENT '是否已出售 d e',
  `landlord_name` VARCHAR(32) COLLATE gbk_chinese_ci DEFAULT NULL COMMENT '房东名字  R',
  `landlord_phone` VARCHAR(32) COLLATE gbk_chinese_ci DEFAULT NULL COMMENT '房东电话  R',
  `remark` VARCHAR(1024) COLLATE gbk_chinese_ci DEFAULT NULL,
  `input_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  CONSTRAINT `t_house_fk` FOREIGN KEY (`type_id`) REFERENCES `t_house_type` (`id`)
)ENGINE=InnoDB COMMENT='InnoDB free: 11264 kB; (`type_id`) REFER `house/t_house_type; InnoDB free: 11264' CHECKSUM=0 DELAY_KEY_WRITE=0 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

#
# Structure for the `t_role` table :
#

DROP TABLE IF EXISTS `t_role`;

CREATE TABLE `t_role` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `role_name` VARCHAR(64) COLLATE utf8_general_ci NOT NULL,
  `descprition` VARCHAR(128) COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB COMMENT='InnoDB free: 11264 kB' CHECKSUM=0 DELAY_KEY_WRITE=0 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

#
# Structure for the `t_user` table :
#

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) COLLATE utf8_general_ci NOT NULL,
  `password` VARCHAR(64) COLLATE utf8_general_ci DEFAULT NULL,
  `role_id` INTEGER(11) DEFAULT NULL,
  `locked` BIT(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `t_user_fk` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`)
)ENGINE=InnoDB COMMENT='InnoDB free: 11264 kB; (`role_id`) REFER `house/t_role`(`id`)' CHECKSUM=0 DELAY_KEY_WRITE=0 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';