954,580 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article

Should items table be divided into categories??

I am currently working on an invoice type of database and was wondering the proper way to set up the items table..

As of now I have all my items in one table and am using the auto_increment id of each item for that specific item's id.. Everything works fine, but I was thinking that this seems sort of half ass.. What if down the line I wanted to add more items to the table.. Then this huge list of items would start to become scattered.. Would this slow down my queries?? Should I divide all the item categories into their own individual table, or will the queries handle the scattered table of items just fine??

I've been reading about indexes, but I'm not quite understanding how to use them to my advantage.. Can one table have multiple indexes?? And if so would that solve my problem??

Here is my items table:

CREATE TABLE `dw_items` (
  `item_id` INTEGER unsigned NOT NULL auto_increment,
  `item_category` varchar(255) NOT NULL default '',
  `item_description` text NOT NULL,
  `item_price` float default NULL,
  PRIMARY KEY  (`item_id`)
)


Just looking for some feedback by the experienced.. Not sure which way to go with this one..

Thanks

Scooby08
Newbie Poster
20 posts since Aug 2008
Reputation Points: 10
Solved Threads: 0
 

I think you would be okay with that setup, but you may want to consider having a category table that is seperate and use an FK to link them. That way you can change the name of the category without worry, and you'll also save server storage that way to (you won't be storing the word "blanket" 50 times)

unixmonkey
Newbie Poster
15 posts since Nov 2007
Reputation Points: 10
Solved Threads: 2
 

This article has been dead for over three months

Post: Markdown Syntax: Formatting Help
You