I am designing a database system that will have a PHP frontend for a dieting club, and have just got round to designing my schema for the MySQL DB and have come to a stop.
My question is, when recording the clients initial weight, would it be stored in the clients table or the wieights table? I want to allow for people leaving the club and then re-joining. My thinking is that it would be stored in the weights table with a type of initial, and then all other weights for the client_id recorded as sub weights.
Would this be the correct way of going about this? And if not, what is?
Thanks in advance.
Mike
PS: Here's where I'm at so far with the design:
Code: Select all
-- ---
-- Globals
-- ---
-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;
-- ---
-- Table 'clients'
-- Relates to clients
-- ---
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
`client_id` INTEGER(20) NULL AUTO_INCREMENT DEFAULT NULL,
`client_username` VARCHAR(30) NULL DEFAULT NULL,
`client_name1` VARCHAR(50) NULL DEFAULT NULL,
`client_dob` DATE NULL DEFAULT NULL,
`client_name2` VARCHAR(30) NULL DEFAULT NULL,
`client_contacttel1` INTEGER(11) NULL DEFAULT NULL,
`client_contacttel2` INTEGER(11) NULL DEFAULT NULL,
`client_contacttelmobile` INTEGER(11) NULL DEFAULT NULL,
`client_email` VARCHAR(100) NULL DEFAULT NULL,
`client_barcode` INTEGER(20) NOT NULL DEFAULT NULL,
`client_regtimestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`new field` INTEGER NULL DEFAULT NULL,
PRIMARY KEY (`client_id`)
) COMMENT='Relates to clients';
-- ---
-- Table 'weights'
-- Holds client weights for all weigh-ins.
-- ---
DROP TABLE IF EXISTS `weights`;
CREATE TABLE `weights` (
`weighin_id` INTEGER(20) NOT NULL AUTO_INCREMENT DEFAULT NULL,
`client_id` INTEGER(20) NOT NULL DEFAULT NULL,
`client_id_clients` INTEGER(20) NULL DEFAULT NULL,
`weighin_timstamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`weighin_id`)
) COMMENT='Holds client weights for all weigh-ins.';
-- ---
-- Foreign Keys
-- ---
ALTER TABLE `weights` ADD FOREIGN KEY (client_id_clients) REFERENCES `clients` (`client_id`);
-- ---
-- Table Properties
-- ---
-- ALTER TABLE `clients` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `weights` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ---
-- Test Data
-- ---
-- INSERT INTO `clients` (`client_id`,`client_username`,`client_name1`,`client_dob`,`client_name2`,`client_contacttel1`,`client_contacttel2`,`client_contacttelmobile`,`client_email`,`client_barcode`,`client_regtimestamp`,`new field`) VALUES
-- ('','','','','','','','','','','','');
-- INSERT INTO `weights` (`weighin_id`,`client_id`,`client_id_clients`,`weighin_timstamp`) VALUES
-- ('','','','');