Duiklogboek: basis tabellen
Zoals in mijn vorige post vertelde ben ik bezig met het bouwen van mijn eigen duiklogboek. Stap 1 is denk ik de tabellen waar de data in op moet slaan.
Het grootste gedeelte van de tabellen heb ik al aangemaakt.
duiktabel, buddies, materiaal, weer, gebruik van flessen/consumptie, maar wat ik nog mis zijn duiklokaties en duikprofiel.
De duiktabel:
CREATE TABLE `divelog_dives`
(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'systemid'
,`diveno` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'dive number'
,`datetime` DATETIME NOT NULL
COMMENT 'date & time'
,`buddy1ID` INT UNSIGNED NOT NULL
COMMENT 'Buddy 1'
,`buddy2ID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Buddy 2'
,`evenementID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Dive part of evenement'
,`reason` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'reason to dive'
,`courseID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'diveCourse'
,`instructorID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Instructor'
,`depthMax` FLOAT UNSIGNED NULL DEFAULT NULL
COMMENT 'Maximum depth'
,`depthAvg` FLOAT UNSIGNED NULL DEFAULT NULL
COMMENT 'Average depth'
,`diveTime` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Time spend under water'
,`bottomTime` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Time spend on the bottom'
,`diveTimeTotal` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Sum of time spend under water'
,`tempWater` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Temperature of the water'
,`tempAir` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Temperature of the air'
,`weatherID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Type of weather'
,`visibility` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Visibility under water'
,`regProfile` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'
COMMENT 'Dive Profile present?'
,`regSightings` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'
COMMENT 'Sightings registration?'
,`regMaterials` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'
COMMENT 'Materials registration?'
,`regStages` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'
COMMENT 'Extra Stages registration?'
,`description` LONGTEXT CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Visibility under water'
,`picture0ID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Main & mini picture'
,`picture1ID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'picture 2'
,`picture2ID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'picture 3'
,PRIMARY KEY (`id`)
,INDEX (`id`)
)
ENGINE = MyISAM COMMENT = 'divetable';
De tabel met duik metadata en de bij behorende koppeltabel:
CREATE TABLE `divelog_divemeta`
(`metaID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'weatherID'
,`meta` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'meta'
,`description` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Description'
,PRIMARY KEY (`weatherID`)
,INDEX (`weatherID`)
)
ENGINE = MyISAM COMMENT = 'Metada dive';
CREATE TABLE `divelog_dive_divemeta`
(`diveID` INT UNSIGNED NOT NULL
COMMENT 'id to divetable'
,`metaID` INT UNSIGNED NOT NULL
COMMENT 'id to metatable'
,INDEX (`diveID`)
)
ENGINE = MyISAM COMMENT = 'connection table dive2clothing';
De tabel die kleding met duiken verbindt:
CREATE TABLE `divelog_dive_clothing`
(`diveID` INT UNSIGNED NOT NULL
COMMENT 'id to divetable'
,`clothingID` INT UNSIGNED NOT NULL
COMMENT 'id to clothingtable'
,INDEX (`diveID`)
)
ENGINE = MyISAM COMMENT = 'connection table dive2clothing';
De tabel met kleding:
CREATE TABLE `divelog_clothing`
(`clothingid` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'id'
,`description` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'describe the clothing'
,`symbol` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'symbol of item'
,`equipmentID` INT UNSIGNED NOT NULL
COMMENT 'link to the equipment'
,PRIMARY KEY (`clothingid`)
,INDEX (`clothingid`)
)
ENGINE = MyISAM COMMENT = 'clothing';
De tabel waarin gebruikte flessen staan:
CREATE TABLE `divelog_gasconsumption`
(`consumptionid` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'id'
,`diveid` INT UNSIGNED NOT NULL
COMMENT 'id of the dive'
,`tankvolume` SMALLINT UNSIGNED NOT NULL
COMMENT 'Volume of the tank'
,`gasnumber` TINYINT UNSIGNED NOT NULL
COMMENT 'Number to define gas, 0 is always main'
,`oxygenPct` SMALLINT UNSIGNED NULL DEFAULT 21
COMMENT 'Percentage of oxygen'
,`heliumPct` SMALLINT UNSIGNED NULL DEFAULT 0
COMMENT 'Percentage of helium'
,`startPressure` INT UNSIGNED NULL DEFAULT 200
COMMENT 'Pressure at the start of the dive'
,`endPressure` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Pressure at the end of the dive'
,`description` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Description of the gas'
,`avgConsumption` FLOAT UNSIGNED NULL DEFAULT NULL
COMMENT 'Average consumption on the tank'
,PRIMARY KEY (`consumptionid`)
,KEY `consumptionid` (`consumptionid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='air consumption' AUTO_INCREMENT=1 ;
De tabel met de rest van de duikuitrusting:
CREATE TABLE `divelog_equipment`
(`equipmentid` int(10) unsigned NOT NULL auto_increment
COMMENT 'id',
`name` varchar(32) character set utf8 collate utf8_swedish_ci default NULL
COMMENT 'Name of the equipment',
`description` varchar(64) character set utf8 collate utf8_swedish_ci default NULL
COMMENT 'Description',
`purchased` datetime NOT NULL
COMMENT 'Date of purchase',
`lastService` datetime default NULL
COMMENT 'Date of last service',
`serviceCount` int(10) unsigned NOT NULL default '0'
COMMENT 'Number of services it got',
`sellDate` datetime default NULL
COMMENT 'Date when we sold/lost it',
`picture` int(10) unsigned default NULL
COMMENT 'Picture of the equipment',
`manualURL` varchar(64) character set utf8 collate utf8_swedish_ci default NULL
COMMENT 'Manual',
PRIMARY KEY (`equipmentid`),
KEY `equipmentid` (`equipmentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
COMMENT='dive equipment' AUTO_INCREMENT=1 ;
De tabel met dingen die je onder wat kan zien:
CREATE TABLE `divelog_objects`
(`objectid` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'id'
,`name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Name of the objects'
,`nameAlt` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Alternative name (like latin)'
,`category` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Category'
,`picture` INT NULL DEFAULT NULL
COMMENT 'Picture of the object'
,`description` TEXT CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Description'
,`infoURL` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'More information via URL'
,PRIMARY KEY (`objectid`)
,INDEX (`objectid`)
)
ENGINE = MyISAM COMMENT = 'dive objects';
De optie tabel waar voorkeuren in opgeslagen worden:
CREATE TABLE `divelog_options`
(`optionID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'id'
,`name` VARCHAR(24) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'name'
,`value` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'value'
,PRIMARY KEY (`optionID`)
,INDEX (`optionID`)
)
ENGINE = MyISAM COMMENT = 'options-table';
De tabel met personen zoals buddies, instructeurs:
CREATE TABLE `divelog_people`
(`personID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'PersonID'
,`lastname` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Last name of person'
,`firstname` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'First name of person'
,`nickname` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Nickname on forum/board'
,`displayname` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Name to be displayed'
,`certificationID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Certification of this person'
,`connection` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Connection to person'
,`picture` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Picture of person'
,`phone` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Phonenumber'
,`email` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Emailaddres'
,`address` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Address'
,`city` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'City'
,`zipcode` VARCHAR(8) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Zip code'
,`userID` INT UNSIGNED NULL DEFAULT NULL
COMMENT 'Connected useraccount'
,`privacy` TINYINT(1) UNSIGNED NULL DEFAULT NULL
COMMENT 'Privacy level to show names'
,`lastupdate` DATETIME NOT NULL
COMMENT 'Last updated on'
,PRIMARY KEY (`personID`)
,INDEX (`personID`)
)
ENGINE = MyISAM COMMENT = 'connected people';
De tabel met de plaatjes/foto’s:
CREATE TABLE `divelog_pictures`
(`pictureID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'pictureID'
,`description` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'description'
,`filename` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'filename'
,PRIMARY KEY (`pictureID`)
,INDEX (`pictureID`)
)
ENGINE = MyISAM COMMENT = 'pictures';
De tabel met tags en de connectie tabel om het aan duiken te koppelen:
CREATE TABLE `divelog_tags`
(`tagid` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'id'
,`tag` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'tag'
,PRIMARY KEY (`tagid`)
,INDEX (`tagid`)
)
ENGINE = MyISAM COMMENT = 'tags in dives';
CREATE TABLE `divelog_dive_tags`
(`diveID` INT UNSIGNED NOT NULL
COMMENT 'id to divetable'
,`tagid` INT UNSIGNED NOT NULL
COMMENT 'id to clothingtable'
,INDEX (`diveID`)
)
ENGINE = MyISAM COMMENT = 'connection table dive2tags';
De tabel met de plaatjes/foto’s:
CREATE TABLE `divelog_pictures`
(`pictureID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'pictureID'
,`description` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'description'
,`filename` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'filename'
,PRIMARY KEY (`pictureID`)
,INDEX (`pictureID`)
)
ENGINE = MyISAM COMMENT = 'pictures';
De tabel met gebruikers die kunnen inloggen op het logboek:
CREATE TABLE `divelog_users`
(`UID` mediumint(20) unsigned NOT NULL auto_increment
COMMENT 'unique ID'
,`login` varchar(60) NOT NULL default ''
COMMENT 'user login'
,`pass` varchar(64) NOT NULL default ''
COMMENT 'password'
,`rights` varchar(100) NOT NULL default ''
COMMENT 'user rights, 4 low, 0 high'
,`lastaccess` datetime NOT NULL default '0000-00-00 00:00:00'
COMMENT 'last access date/time'
,PRIMARY KEY (`UID`)
,KEY `user_login_key` (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
De tabel met het weer:
CREATE TABLE `divelog_weather`
(`weatherID` INT UNSIGNED NOT NULL AUTO_INCREMENT
COMMENT 'weatherID'
,`description` VARCHAR(48) CHARACTER SET utf8 COLLATE utf8_swedish_ci NULL
COMMENT 'Description of the weather'
,PRIMARY KEY (`weatherID`)
,INDEX (`weatherID`)
)
ENGINE = MyISAM COMMENT = 'Weather';
