Member Avatar for Scooby08

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

  • 2 Contributors
  • 1 Reply
  • 100 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by unixmonkey
Member Avatar for unixmonkey

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.