Database design question - should I add another table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Database design question - should I add another table?

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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"
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

oh yea :wink:
Post Reply