Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Edward

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!

 

 

 

 

 

 

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...