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

Approach 1 is best. Joins aren't slow as long as you have primary keys and indexes.

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.