SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimal design for a review database

    I am trying to determine the best design for a product review database that balances speed and database maintainence.

    The review database will contain around 15 categories of products. Usually these categories will remain the same, but it is likely that occasionally new categories will be added and others removed.

    I have come up with two possible designs. The first involves creating a table for each category. Each table will probably contain the same fields as the other tables.

    The second method involves dumping all the reviews into one table containing all the necessary fields and an additional Category column so I will be able to select all the reviews for CategoryX.

    I am considering the first simply because of possible performance benefits. The second method seems like it will be much easier to maintain, but I am concerned that performance will be much slower due to having to search through possibly hundreds of thousands of reviews when I just want a report of reviews for CategoryZ.

    I predict that the record count of each category will vary greatly. Records will contain fields like reviewer name, product name, date, and a the actual review which may reach the size of this forum message. Total review count at some point may reach 100k-500k. I am using MySQL.

    What do you think?
    Last edited by DataLore; Oct 7, 2002 at 11:04.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the first method is truly cumbersome

    the second method is considered "best practice"

    put an index on the category column

    rudy

  3. #3
    SitePoint Member
    Join Date
    Aug 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I think indexing is the trick I was looking for. Thanks so much.


Bookmarks

Posting Permissions

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