Help with my table structure

Question

I'm trying to make an address book. And have made my tables like this:

CREATE TABLE `list_`.`contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `group` varchar(128) NOT NULL,
  `first_name` varchar(128) NOT NULL,
  `last_name` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip` int(5) NOT NULL,
  `phone_number` varchar(16) NOT NULL,
  `cell_number` varchar(16) NOT NULL,
  `work_number` varchar(16) NOT NULL,
  `fax_number` varchar(16) NOT NULL,
  `email` varchar(128) NOT NULL,
  `company` varchar(55) NOT NULL,
  `title` varchar(56) NOT NULL,
  `notes` text NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `group` (`group`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;


CREATE TABLE `list_`.`groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `position` int(8) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;

My logic here is that I have all the contacts in the contacts table, from there I have a column called group that I use to filter the contacts into groups.

Then I have a table called groups that I'll use to keep track of what groups where created by a specific user and fill a with these groups so they can move the contacts around.

When a group is deleted I will throw back an error no letting it be deleted if the group contains contacts. I can probably query the contacts to see what group they belong to and if they belong to the group being deleted then I'll move them into a group called Uncategorized or something.

But if they choose to accept and delete ALL contacts within a group then go ahead and delete the group and all child rows that belong to that group.

I am having trouble creating my Foreing Keys in the contacts table. No matter what combination of index and keys I try I still can't make it work.

--
-- Constraints for table `contacts`
-- 
ALTER TABLE `list_`.`contacts`
  ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`group`) REFERENCES `groups` (`name`) ON UPDATE CASCADE;

-- 
-- Constraints for table `groups`
-- 
ALTER TABLE `list_`.`groups`
  ADD CONSTRAINT `group_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

Also, can someone help me with the ON DELETE and ON UPDATE. To help me figure out how I can delete all child rows that reference the name column in groups

Answer 1

I do not think you should have MySQL do so much heavy lifting, specifically deleting a group if it is empty. Is there any specific reason to do this, i.e. can the group no longer be used if it is empty? You could probably achieve this with mysql triggers if you really wanted to.

As for preventing the deletion of a group that is simply done by the foriegn key on contacts that you already have on groups. However I highly recommend that you use ids rather than names for keys.

What you are saying seems to conflict, however: you want to prevent deletion of a group that has contacts, but you want all contacts in a group to be deleted when the group is deleted?