webweever

Does this look more appropriate? CREATE TABLE `item` ( `id` int(11) NOT NULL auto_increment, `date` date NOT NULL, `store_id` int(11) 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, `user_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, PRIMARY KEY (`id`) Foreign Key (item_id) references members(id)); ) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=91 ; CREATE TABLE `members` ( `id` int(11) NOT NULL auto_increment, `usr` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `pass` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `email` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `regip` varchar(15) character set utf8 collate utf8_unicode_ci NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `usr` (`usr`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; CREATE TABLE `lists` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `dt` datetime NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), Foreign Key (user_id) references members(id)); ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; CREATE TABLE `store` ( `id` int(11) NOT NULL auto_increment, `store_name` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

The basic gist of it is a member can create multiple lists that can contain multiple items from multiple stores.

Does this look like good DB structure? CREATE TABLE `item` ( `item_id` int(11) NOT NULL auto_increment, `user_id` int(20) 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, `item_id` int(11) NOT NULL, PRIMARY KEY (`item_id`) Foreign Key (user_id) references members(id)); ) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=91 ; CREATE TABLE `members` ( `id` int(11) NOT NULL auto_increment, `usr` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `pass` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `email` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `regip` varchar(15) character set utf8 collate utf8_unicode_ci NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `usr` (`usr`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; CREATE TABLE `lists` ( `list_id` int(11) NOT NULL auto_increment, `name` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL, `dt` datetime NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), Foreign Key (user_id) references members(id)); ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;