Normalization help

webweever

Does anyone see anything wrong with this structure?

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=InnoDB 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=InnoDB 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);
  );

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=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Undrium

Yes, I think you are ending two of the queries too early with a ";" too much. Want me to be more specific or do you see them?

Res

With a limited understanding of what you are trying to store, it looks pretty good. I'm not sure I understand your items table, and depending on what you want it to do it can be improved. It's called items, so I perceive it to store all the items you are dealing with. You also have a user_id in there, which is where I get confused. Are you storing the end users id that ordered the item or the user that added/created the item? If it's the later it looks good, if it's the former I would have one table for items to store the price and details, then another table as 'customer_order_items' that links a user_id and item_id. I guess really I would have customer_order_h and customer_order_d. Where h holds the invoice details, total price, tax and references to the shipping_id,store_id,customer_id, etc. Then the d table would be the details, and link items back to the order_id.

Also, the foreign key is a little confusing to me. In the items table you have

Foreign Key (item_id) references members(id));

Should item_id really be a reference to member id?

webweever

Thanks for the responses... The basic concept is a member can create multiple lists that can contain multiple items from multiple stores. The member can then display those items based on what list they are a part of.

I guess I should delete that FK in the items table that references the member id since an item is associated with a list not necessarily a member. Is my thought process correct on that?

Res

Ok, I would remove the reference to the user in items table and store just the list of possible items. One of the ideas behind normalization is to reduce the amount of redundant data you are storing. Looking at your item table, lets say you have one item that is on 1000 members lists. With the current structure of storing this association in the item table, you end up storing 1000 records for an item that only needs one. This ultimately will lead to slow downs, as when you try and grab the details of an item later on, there are 1000 records to search instead of one.

With that in mind, I would modify the lists table to store the association instead of the items table, which should just store the items available.

Store
- id
- store_name

lists
- id
- store_id
- user_id
- item_id
- name
- dt

items
- id (this is the item id)
- date
- store_id
- item
- itemprice
- itemnumber
- itemtotalprice

members
- id
- usr
- pass
- email
- regip
- dt

webweever

So i don't really need FKs anywhere?

Res

You don't need them, but I can't really speak to if it is preferred or not. I come from a time before foreign key support in mysql, so it's always been field indexes and flow control in the application logic for me. I can tell you that all your tables are MyISAM and last I checked foriegn keys were only supported when both tables were InnoDB.

In any case, that's a data flow/control issue. You can keep the structure we've discussed and update your foreign keys to reference where we have moved the fields.

bradgrafelman

webweever;10979642 wrote:
So i don't really need FKs anywhere?

I like the way johanafm explained the necessity of FKs:

johanafm;10979365 wrote:
Not necessary, but this is similar to asking if you need a seat belt to drive a car. You don't, but it's still a good idea. You don't have to specify ON DELETE CASCADE or ON UPDATE CASCADE either. But you can.

and then I later added:

bradgrafelman;10979427 wrote:
Data never "should be" inconsistent... and yet sometimes it is. Rule of thumb: add as many constraints as you can think of into the DB schema, rather than leaving it up in the air with a "should be" statement.

webweever

So, i can assuming with some certainty that FKs are a good idea. That said, does it appear I have them correct in my structure above.

All the items in the item table are user entered so there may be many items named "paint" for instance but be entered by many different users. So I could have 1000 records named paint with different prices. That sounds like a bad thing to me but I'm not sure I have another choice.

johanafm

You could have those 1000 records, but you should not have them because 1000 users have listed the exact same item. You should only have that if 1000 different stores each sell an item call "paint" at different prices. And even if two different stores sell the exact same item at the exact same price using the exact same name, you should have one item entry per store (and going with the suggested db schema above, you will), since it's possible that one store changes the price in the future while the other doesn't.
All you'd need to do is add the constraints, both primary keys and foreign keys

Store
- id
- store_name
PRIMARY KEY(id)

lists
- id
- store_id    -- I would remove this, since each item has a store_id
- user_id
- item_id
- name
- dt
PRIMARY KEY(id),
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (item_id) REFERENCES (id)

The same thing should of course be done for items and members. Looking at "FOREIGN KEY (item_id) REFERENCES (id)" above, this means that a member can't add a non-existing item to their list. Furthermore, it also prevents deleting this item unless all references to this item are deleted from "lists".
You could modify this behaviour by using

FOREIGN KEY (item_id) REFERENCES (id) ON DELETE CASCADE

which would mean that you could now delete an item from items and the DBMS would automatically remove all references to it in the lists table (assuming no other table has any references to this item, or that all such references also have ON DELETE CASCADE)

But since you also said that members can create multiple lists, you should also normalize the lists table

lists
- id
- member_id
- name
- dt

lists_items
- list_id,
- item_id
- dt 		-- if you want/need to know both when each individual item was added

and once again, foreign keys for lists.member_id, lists_items.list_id and lists_items.item_id.