Proper way of setting up database for ease of use?

Question

So i'm trying to figure out the smartest way to do this. I have a list of businesses that can have photos and videos. i've created tables for: business, photos, videos. I also created a table for specials. I want to have featured businesses on the front page. Should I create a table called featured and have the businesses id stored in the table and then query the featured table? What if I want to show on the home page that they have photos and videos?

Answer 1

the basic design would be OK, but I would just add column to businesses that specifies if it is featured or now. If you want to show if featured businesses have videos or photos, ask the database if

select count(*) from photos 
join business on photos.businessId = business.id
where id = @featuredBusinessID

for each featured one, similar for videos too.

Answer 2

Design your core database to do what you want, and query it in a straightforward manner to get the data you need. That having been said, for the featured businesses, the easiest way to do it is to put a featured field on the business table and get your list of featured businesses by querying for featured = 1. This will be both the simplest and most performant solution (indexing on featured will be faster than joining a "featured" table to your businesses table).

To get photo information, just join the results to the photos table. Ditto for video.