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';