Jump to content
Larry Ullman's Book Forums

Normalization Is Unclear.


Recommended Posts

I have an item table:

 

CREATE TABLE item (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
price INT(10) UNSIGNED NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_updated DATETIME DEFAULT NULL,
PRIMARY KEY (id),
INDEX fk_item_user_idx (user_id ASC)
)ENGINE=InnoDB;

 

but what i don't find clear is should i make a separate table for the category_id which links to a table with all the catogories like:

 

CREATE TABLE item_category (
item_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
PRIMARY KEY (item_id, category_id),
INDEX fk_item_category_item_idx (item_id ASC),
INDEX fk_item_category_category_idx (category_id ASC)
)ENGINE=InnoDB;

 

or should i include the category_id inside the item table. This is really not clear to me as i can find examples everywhere online of with both senerios. What is the correct thing to do have more tables or put everything in one. Because even if we have a middle table to glue the item and category table, the item_category table will still have repeated category values as would of had the item table??? I have some other tables that i need associating with the item so now, I am confused!

 

 

 

 

 

 

Link to comment
Share on other sites

If one category can have multiple items, and an item will never be in more than one category, then I would have a table to describe all the categories, and then in a separate table that contains all the items, I would have a column for the category ID to act as a foreign key to link the two tables.

 

That make sense?

  • Upvote 1
Link to comment
Share on other sites

Yes that makes sense, so only if an item has more than one category we need the intermediary table.

 

Thanks this point is clear and i can base all my other tables from this, i think i can see now why i was confused. Those examples online must of been for the other example. Thanks Hartley San.

Link to comment
Share on other sites

No problem.

 

While there are exceptions to every rule, in general, one-to-one relations can go in the same table, one-to-many relations can go in two tables, and many-to-many relations should be spread across three tables, with the intermediary one between the other two.

  • Upvote 2
Link to comment
Share on other sites

 Share

×
×
  • Create New...