Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Unanswered: database structure and optimizing

    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?

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by MAZER
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2005
    Posts
    31
    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
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2005
    Posts
    31
    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
    Last edited by MAZER; 03-28-06 at 07:46.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2005
    Posts
    31
    Thanks for the link healdem, I'm just reading it now.

  8. #8
    Join Date
    Aug 2005
    Posts
    31
    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.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2005
    Posts
    31
    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!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    should I break the comics table up into seperate tables by there publishers? and then join them in a look up table?
    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?

    Code:
    comic_book_series
    ---------------------
    comic_book_seriesid,
    series_name,
    catgoryid,
    publisherid,
    authorid,
    illustratorid
    
    comic_books
    ----------------------
    comic_bookid,
    comic_book_seriesid,
    number,
    edition,
    date_published,
    value
    
    publishers
    ----------------------
    ...
    
    authors
    ----------------------
    ...
    
    illustrators
    ----------------------
    ...
    
    categories
    ----------------------
    ...
    good luck.

  13. #13
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    Attached Thumbnails Attached Thumbnails basiccomicmodel.png  
    Last edited by healdem; 03-28-06 at 13:05.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2005
    Posts
    31
    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?
    Last edited by MAZER; 03-28-06 at 15:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •