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..


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)