Deleting Items associated with a list

webweever

Here is my DB structure:

CREATE TABLE `item` (
  `item_id` int(11) NOT NULL auto_increment,
  `user_id` int(20) NOT NULL,
  `list_name` varchar(30) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `store` varchar(30) collate latin1_general_ci NOT NULL,
  `item` varchar(30) collate latin1_general_ci NOT NULL,
  `itemprice` decimal(10,2) unsigned NOT NULL,
  `itemnumber` tinyint(20) NOT NULL,
  `itemtotalprice` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`item_id`)
);

CREATE TABLE `lists` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate latin1_general_ci NOT NULL,
  `dt` date NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ;

The way the site is structured you have to create a list before an item, then you add / edit / delete items in that list. When each item is created I store the list_name in the item table.

The question is if a user wants to delete a complete list how do I delete the corresponding items associated with that list? I'm guess when I query to delete the list I should do a separate query to delete all items where list_name = the list being deleted and user_id = the currently logged in user? Is that the most efficient way of doing it?

Derokorian

MyISAM or InnoDB tables? If innodb you could do listname as a Foreign key and on delete cascade. if my isam, I would select the list first, then delete it and then delete from the items table where list_name = the list you just selected.

webweever

I'm just designing the site so nobody is using the DB yet. Is there a noticable performance difference in using innodb tables and deleting cascade over 2 serarate queries? I expect this DB could grow pretty large when it's in full use if that makes a difference.

Derokorian

As far as performance I believe innodb will be better for very large sets of data. Also setting up foreign keys helps prevent deleting a primary reference without deleting things referring to it. this way if you were to delete a list without explicitly deleting its items, the DB will delete the items for you.

Another difference is transactions, which are not supported in MyISAM IIRC, but they are in InnoDB. I believe that InnoDB uses row locking as opposed to table locking.