I have a database with two tables each with 22 fields, one table has 2500 rows the other has 6000, and I was wondering how I should structure the db for optimal performance, also how would I optimize it?

We're going to need a lot more information than that . It depends on what types of fields there are, how the tables relate to each other, what they're going to be primarily (or exclusively) used for (SELECT vs INSERT/UPDATE), etc?

Generally, you want to normalize your database. That'll be a good start.

normalising is a good move, as is making sure you index virtually any column which you are going use in a where or having or sort order clause.

However this doesn't mean index every possible column - there is a trade off every index costs on insert or update. if your db is going to be used in a high transaction / volume data entry app then fewer indexes are better, if its going to be used in a data analysis app then more indexs are better.

you also need to carefully design / tune your SQL especially if you are joining tables. if you are in doubt use the explain function to analyse the relative cost of the query.
HTH

there is no easy answer

the database is for displaying information only, although I will be adding information to it through phpmyadmin, but I don't think thats an issue. I have attached an excel file with the data types, field names, and examples of data, I'm still using the access database at the moment so you can view how the database is used at the following link
http://www.themazecomicstore.com/html/Marvel_comics.asp

Sorry for the seriously long post, I thought it might be easier to post the data types and desciption straight in to the forum instead of people having to download the excel file

data field removed

have you read section on nromalistion that jfulton provided
there is also a good bit on Rudy's site
jsut having a quick scan of your data I'd guess you haven't, or if you have you havent yet applied it to your model.

have a try, and of / as / when you get stuck come back. you are liable to get a better response if you make it look like you have tried and failed, rather than expect to be gifted an answer. Equally you are going to learn more by going through the process than being gifted a solution if for no better reason that you know you requirements better than anyone else here
HTH

Thanks for the link healdem, I'm just reading it now.

if a database is primarily for displaying does it have to be normalized? the problem I have at the moment is that my site isn't fully automated, when a person orders items from my web site none of the details are sent to the database, I use a free shopping cart(mals) so I can't set it up to return order information. I know this is a little backwards but I edit stock details manually, would this make a difference when it comes to structure and normalization? I will go down the road of fully automating the site and database at some stage but at the moment I just don't have the knowledge or funds to do it, so for right now I would be happy with a database that can handle multiple users.

basically its your database, you can do whatever you want to, what ever you feel familiar with. its you applcation.

however, having said that I would hesitate diverging from the established principles of normalisation. The reason

lets say you have a category of comic called "Manga" you would need to ensure that every Manga comic had its category correctly specified. Not a major problem with Manga, but lets say you put in a typo you wouldn't see the missed itme if you searched for Manga. the same thing applies to any other element that is common to more than row, eg publisher.

the reason that normalisation as a theory has been so widly adopted is its a way of designing a data storage mechanism which is flexible and adaptable.

so in summary I wouldn't run with your design, but its your design, not mine. It may well work for now, it may well work for ever, but it is prone to problems if you want to change things.

The comics table has a field which is called productGroup, the productGroup field is used to break the comics table into groups of publishers, e.g. Marvel, DC Comics etc. should I break the comics table up into seperate tables by there publishers? and then join them in a look up table? is this what you mean. forgive me if I'm wrong!

NO NO No Noooooooooooo.

your comics should be in one table
you move the publisher into a separate table, linked on a nmeric key

so you have something like

DTPublisher
PubID - autonumnber
PubName - text(50)


DTComic
ComicID - autonumber (arguably this could be the ISBN number), but probably and autonumber.
other fields as required
PubID - long - foreign key to DTPublisher

it is arguable that you should have one table, called say DTProduct, in place of DTPublisher you call that DTManufacturer or possibly DTSupplier.

If you put a relationship / foreign key constraint effectively saying for a record to exist in DTComic it must have a publisher which exists in DTPublisher. You could argue the samae thing for genre
eg
DTComicGenre
ComicGenreID - autonumber
ComicGenreDesc - text (50)

adsd a column to DTComic
ComicGenreID type long

it means you can search by publisher, genre or whatever.

have an attempt at normalsiing your data, the key is eliminate redundancy of data, remoive duplication.

Kind of...anything that is not specific to a comic should be in its own separate table. So, publishers, authors, even the comic book title (this is specific to a series of comic books, right?) should be in their own table.

A super quick start to normalizing your db would look something like this. Although some of the relationships would probably be different. I have set up comic book series to authors as 1:1 relationship. I can imagine there are comics with more than one author?

good luck.

I learn best by example, so hopefully my previous post will get you started. If not, check out this tutorial: http://www.phlonx.com/resources/nf3/. It breaks it all down pretty well

to help I have had a quick look at your data model and come up with a stab at the start of what I think is a your data model
its not complete, its not perfect, its probably not even right...
I'm just trying to encourage you to think about what the aims are of normalising your data.
HTH

Hi Lads
Thanks for all the help so far, believe me I really appreciate it. I think I'm finally beginning to understand, I did a mock up with a small table in access, I created 3 tables, comics, DTproductGroup, DTpublisher

The relations between them are as follows
in the comics table I replaced the ProductGroup field with the PubGroupID field from DTproductGroup(table 2) and replaced the Publisher field with the PublisherID field from DTpublishers(table 3)

DTcomics (table 1)
ProductName - Text 50
Grade - Text 50
PubGroupID - auto long int
PublisherID - auto long int

DTproductGroup (table 2)
ProductGroup - text 50
PubGroupID - auto long int

DTpublishers (table 3)
Publishers - text 50
PublisherID - auto long int

am I on the right track?