Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Which of the following two is the best?

    Hey everyone,

    We have the following two tables in the database:
    photo {photonum, title, filename, filesize}
    website {websitenum, name}

    To capture which photos belong to which websites, I have two possible approaches:

    Approach 1: Create another table
    photowebsite {photnum, websitenum}

    Pros:
    1. Traditional database design. Will satisfy the Normal Forms.

    Cons:
    1. Queries involving a website will always involve a join operation.

    Approach 2. Add a boolean field to the photo table for each website.
    photo {photonum,.... , website1, website2 }

    Pros:
    1. Queries will not have a join operation and will be faster than Approach 2.

    Cons:
    1. Adding a new website will involve creating a new field and hence the database must be shutdown.
    2. The number of fields will increase with the increase in the number of websites.

    However, we right now have only 4 websites and I am wondering if I should go with the 2nd Approach. The only thing that's hold me back is that the number of websites can definitely increase in future.

    Can Database Gurus on this forum help me choose the right design!

    Thank you!
    Joe

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Approach 1 is best. Joins aren't slow as long as you have primary keys and indexes.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Option 1, unless your goal is to be the butt of jokes for the DBA who inherits your project after you are gone.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Quote Originally Posted by blindman View Post
    Option 1, unless your goal is to be the butt of jokes for the DBA who inherits your project after you are gone.
    Thanks for putting that subtly, blindman! It taught me a good lesson that shortcuts are not worth it.

Posting Permissions

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