Keeping Bees: Database

It is a good idea to keep records about your bees. The traditional method is hive-cards. I think that I can design and build a database for this purpose. I'll probably use mySQL, cgi-app, dbix-class with:

CREATE DATABSE bk;

USE bk;
CREATE TABLE `apiary` (
  `ap_id` int(255) NOT NULL AUTO_INCREMENT,
  `ap_name` int(255) default '1',
  `ap_location` varchar(255) default '1' COMMENT 'where is it',
  `ap_date_added` datetime default NULL,
  `ap_land_owner` varchar(255) default NULL,
  `ap_contract` varchar(255) default NULL,
  `ap_acid` int(255) unsigned,
  `ap_notes` blob default NULL,
  PRIMARY KEY  (`ap_id`),
  UNIQUE KEY `ap_id` (`ap_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `hives` (

  `hi_id` int(255) NOT NULL AUTO_INCREMENT,

  `hi_apiary` int(255) default '1' COMMENT 'apiary.ap_id',

  `hi_type` varchar(255) default NULL COMMENT 'Nationa, WBC, TBH, hole-in-a-tree',

  `hi_marks` varchar(255) default NULL COMMENT 'what does it look like',

  `hi_keeper` int(255) unsigned  COMMENT 'who looks after it',

  `hi_owner` varchar(255) default NULL COMMENT 'who owns it',

  `hi_date_added` datetime default NULL,

  PRIMARY KEY  (`hi_id`),

  UNIQUE KEY `hi_id` (`hi_id`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `records` (
  `re_id` int(255) NOT NULL AUTO_INCREMENT,
  `re_hive` int(255) DEFAULT '1' COMMENT 'which colony within the apiary hives.hi_id',
  `re_date` datetime default NULL,
  `re_relocation` varchar(255) default NULL COMMENT 'New location',
  `re_move_reason` varchar(255) default NULL COMMENT 'why the new location',
  `re_palace` int(1) NOT NULL default '0' COMMENT 'Queen from?',
  `re_queen` int(1) NOT NULL default '0' COMMENT 'Queen seen?',
  `re_queen_marked` varchar(1) default NULL COMMENT 'W,Y,R,G,B',
  `re_queen_cells` int(16) default NULL COMMENT 'number of queen cells found',
  `re_brood_state` int(4) default NULL COMMENT 'how happy are they from 1..10',
  `re_brood_frames` int(4) default NULL COMMENT 'how many frames are the brood on',
  `re_eggs` int(1) default NULL COMMENT 'are there eggs in the brood?',
  `re_stores` int(1) default NULL COMMENT 'number of supers',
  `re_room` int(1) default NULL COMMENT 'number of brood frames',
  `re_health` varchar(16) default NULL COMMENT 'ok,CB=chalk brood,AFB,EFB,',
  `re_varroa` varchar(1) default NULL COMMENT 'n=none,l=low,m=medium,h=high,i=infestation',
  `re_varroa_count` int(32) unsigned default NULL COMMENT 'count of dead mites',
  `re_temperament` int(1) default NULL COMMENT '9=nice,8=agitated,6=stinging,4=follow,1=murder,0=burn',
  `re_feed` varchar(10) default NULL COMMENT '',
  `re_supers` int(10) default NULL COMMENT 'added or removed',
  `re_weather` varchar(16) default NULL COMMENT 'sunny,cloudy,rainy',
  `re_temperature` varchar(10) default NULL COMMENT 'degrees celcius',
  `re_notes` blob default NULL COMMENT 'Work that is requireda',
  PRIMARY KEY  (`re_id`),
  UNIQUE KEY `re_id` (`re_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I don't actually use this because I have Notice with its Asset management system and asset record cards. So the hives are abstracted into my Asset database and the hive-record cards are specific asset record cards. (Because the Notice::Asset system can nest assets within assets, the apiary is simply another asset that contains the hives.)