tables.sql2

CREATE TABLE `visitor` (
  `vid` int(11) NOT NULL AUTO_INCREMENT,
  `vname` varchar(32) NOT NULL,
  `vphone` varchar(12) NOT NULL,
  `vgender` varchar(12) DEFAULT NULL,
  `vage` int(11) NOT NULL,
  `vaddress` varchar(256) NOT NULL,
  `vcomments` varchar(512) DEFAULT NULL,
  `vctime` datetime NOT NULL,
  `vmtime` datetime NOT NULL,
  `vatime` datetime NOT NULL,
  `vphoto` mediumblob,
  PRIMARY KEY (`vid`),
  UNIQUE KEY `vphone` (`vphone`)
) ENGINE=InnoDB AUTO_INCREMENT=2091 DEFAULT CHARSET=latin1
CREATE TABLE `vrecord` (
  `vid` int(11) NOT NULL,
  `vrecordid` int(11) NOT NULL AUTO_INCREMENT,
  `vvehicle_reg_num` varchar(32) DEFAULT NULL,
  `vvehicle_type` varchar(16) DEFAULT NULL,
  `vvehicle_details` varchar(128) DEFAULT NULL,
  `vitime` datetime NOT NULL,
  `votime` datetime DEFAULT NULL,
  `vflatnum` varchar(24) NOT NULL,
  `vblock` varchar(32) NOT NULL,
  `vpurpose` varchar(128) DEFAULT NULL,
  `vtomeet` varchar(32) NOT NULL,
  `vnum_visitors` int(11) DEFAULT NULL,
  `vrcomments` varchar(256) DEFAULT NULL,
  `vduration_fillup` int(11) DEFAULT NULL,
  `vgate` int(3) DEFAULT '0',
  `vogate` int(3) DEFAULT NULL,
  PRIMARY KEY (`vrecordid`),
  KEY `vid` (`vid`),
  CONSTRAINT `vrecord_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `visitor` (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=11151 DEFAULT CHARSET=latin1

CREATE TABLE `vpic` (
  `vpicid` int(11) NOT NULL AUTO_INCREMENT,
  `vid` int(11) NOT NULL,
  `vphoto` mediumblob,
  PRIMARY KEY (`vpicid`),
  KEY `vid` (`vid`),
  CONSTRAINT `vpic_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `visitor` (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
CREATE TABLE `vunit` (
  `vunitid` int(11) NOT NULL AUTO_INCREMENT,
  `vblock` varchar(32) NOT NULL,
  `vflatnum` varchar(24) NOT NULL,
  `vintercom` varchar(12) DEFAULT NULL,
  `vointercom` varchar(12) DEFAULT NULL,
  -- to record flats that have never been visited
  `lvrecordid` int(11) DEFAULT NULL,
  -- to record flats that have not been visited for a specific period
  `lvitime` datetime DEFAULT NULL,
  PRIMARY KEY (`vunitid`),
  UNIQUE KEY `unique_index` (`vblock`,`vflatnum`)
) ENGINE=InnoDB AUTO_INCREMENT=6573 DEFAULT CHARSET=latin1

CREATE TABLE `vconfig`(
        `cfield` varchar(32) NOT NULL,
        `cname` varchar(32) NOT NULL,
        UNIQUE KEY `unique_index` (`cfield`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;