Normalization Question

[webweever]

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 ;

[mikosiko]

the answer depend on what each entity represent and how the relation among them is read (members is clear, item is suspicious, lists no clear)

 

BTW.: The PK on `lists` is incorrect

[mikosiko]

I did notice also that in `item` the attribute `item_id` is duplicated, and probably `store` should be a FK to another entity (stores)

[webweever]

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

[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 ;

[mikosiko]

no...

 

this is what you have (gif attached)... you will see that the relations are not correct based on your description.

 

Edit: I saw that you added a store entity... good... now think about the item table.... should be the item name (apparently field `item`) be present in that table?

 

 

[attachment deleted by admin]