Page 1 of 1

Database design question - should I add another table?

Posted: Thu Dec 14, 2006 5:32 pm
by Luke
I think I know the answer to this question already, but I thought I'd get a second opinion. I have three tables as of right now teams, teams_members, and teams. The reason I set the tables up like this is because I wanted to be able to have members be on more than on team and teams to have more than one member. So this setup made sense:

Code: Select all

CREATE TABLE `members` (
  `id` mediumint(9) NOT NULL auto_increment,
  `first_name` varchar(25) collate utf8_unicode_ci NOT NULL,
  `middle_name` varchar(25) collate utf8_unicode_ci default NULL,
  `last_name` varchar(40) collate utf8_unicode_ci NOT NULL,
  `nick_name` varchar(25) collate utf8_unicode_ci default NULL,
  `spouse_name` varchar(75) collate utf8_unicode_ci default NULL,
  `home_phone` int(10) default NULL,
  `home_fax` int(10) default NULL,
  `cell_phone` int(10) default NULL,
  `personal_email` varchar(100) collate utf8_unicode_ci default NULL,
  `home_address` varchar(100) collate utf8_unicode_ci NOT NULL,
  `home_city` varchar(50) collate utf8_unicode_ci NOT NULL,
  `home_state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `home_zip` mediumint(9) NOT NULL,
  `gender` varchar(1) collate utf8_unicode_ci default NULL,
  `join_date` datetime NOT NULL,
  `birth_date` datetime NOT NULL,
  `photo` varchar(255) collate utf8_unicode_ci NOT NULL,
  `business_name` varchar(100) collate utf8_unicode_ci NOT NULL,
  `business_phone` int(10) NOT NULL,
  `business_phone2` int(10) default NULL,
  `business_email` varchar(100) collate utf8_unicode_ci default NULL,
  `business_website` varchar(255) collate utf8_unicode_ci default NULL,
  `business_address` varchar(100) collate utf8_unicode_ci NOT NULL,
  `business_city` varchar(50) collate utf8_unicode_ci NOT NULL,
  `business_state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `business_zip` mediumint(9) NOT NULL,
  `business_classification` varchar(255) collate utf8_unicode_ci NOT NULL,
  `business_keywords` text collate utf8_unicode_ci NOT NULL,
  `business_description` text collate utf8_unicode_ci NOT NULL,
  `last_modified` datetime NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `business_keywords` (`business_keywords`,`business_description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `teams` (
  `id` mediumint(9) NOT NULL auto_increment,
  `name` varchar(50) collate utf8_unicode_ci NOT NULL,
  `ytd_points` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `teams_members` (
  `member_id` mediumint(9) NOT NULL,
  `coordinator` tinyint(1) NOT NULL,
  `captain` tinyint(1) NOT NULL,
  `cocaptain` tinyint(1) NOT NULL,
  `team_id` mediumint(9) NOT NULL,
  KEY `teams_members` (`member_id`,`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
But just now I was informed that members can only be on one team at a time. So of I'm guessing that I should move the columns from teams_members into members. I'm almost positive that's the right thing to do... are there any reasons not to?

Posted: Thu Dec 14, 2006 5:51 pm
by RobertGonzalez
If a team member can only be on one team at a time, then it makes sense to carry the team information in the teams table and the team id of the team the member is on in the member table.

Posted: Fri Dec 15, 2006 4:03 am
by onion2k
I think I'd keep it the same as it is now to be honest. I can think of plenty of scenarios where a team member might be in more than one team (on loan for example). Could a team have more than one cocaptain? It's the sort of thing that may change one day and you'll be faced with a massive hassle to impliment. I'd go with a relatively future-proof solution from the start.

Posted: Fri Dec 15, 2006 9:00 am
by Luke
I asked my boss... he said you can NEVER be on more than one team at a time. I said "In the future?" he said no? I said well... would you like me to make that option possible... he said "no". So I agree with you, but he doesn't :lol:

Posted: Fri Dec 15, 2006 9:05 am
by feyd
It's such a simple thing to add now, just add that ability now. Code like it isn't there (for the most part) to satisfy the current need.

Posted: Fri Dec 15, 2006 9:10 am
by Luke
hmm.. well I'm glad I started this thread then, I will keep it. I can't for the life of me figure out why I named the thread "should I add another table" it should have been "should I remove a table"

Posted: Fri Dec 15, 2006 9:52 am
by RobertGonzalez
The Ninja Space Goat wrote:hmm.. well I'm glad I started this thread then, I will keep it. I can't for the life of me figure out why I named the thread "should I add another table" it should have been "should I remove a table"
It's because you're The Ninja Space Goat.

Posted: Fri Dec 15, 2006 10:04 am
by Luke
oh yea :wink: