Page 1 of 1

DB Design help please.

Posted: Wed Oct 26, 2011 7:55 am
by mikeashfield
Hi,

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
-- ('','','','');

Re: DB Design help please.

Posted: Wed Oct 26, 2011 11:14 am
by manohoo
Hi Mike, 3 comments:

1. Don't you think that weights.client_id should be the foreign key, instead of weights.client_id_clients?
2. Which field in weights holds the actual client's weight? I don't see any
3. What was your intent for weights.client_id_clients?

Re: DB Design help please.

Posted: Wed Oct 26, 2011 11:30 am
by mikeashfield
manohoo wrote:Hi Mike, 3 comments:

1. Don't you think that weights.client_id should be the foreign key, instead of weights.client_id_clients?
2. Which field in weights holds the actual client's weight? I don't see any
3. What was your intent for weights.client_id_clients?
1. It's something i was taught back in college to do, put '_tblname' after the field name so you know what you're referencing.
2. None do so far, I haven't finished the schema design yet, it's here where I stopped.
3. see 1.