SQL Table design question

Question

Please ignore this question if it sounds stupid to you.

I have SQL table (SQL Server) for photo albums and it has 20+ columns & it will hold millions of albums.

I need to designate some albums as Promoted and some as Featured every week. I also need a very efficient way to get these albums (page by page) when I show it to users.

How should I design this?

option 1: I can create another table just to store the ids of the promoted and featured albums like this and then join the main albums table to get the set of columns I need.

table designated_albums:

album_id  promoted_featured
    1         1
    5         0
    7         1
    15        0

The query for promoted will return 1, 7 The query for featured will return 5, 15

Option 2: I can add 1 column store 1 if promoted and 0 if featured. Otherwise it is null I can then query to check for 1 in that column for promoted albums & 0 for featured.

Option 3: I can add 2 bit columns: one for promoted (0/1) and one for featured(0/1)

Which way would perform better?

EDIT: The design should be efficient in SQL 2008 as well. Right now I have SQL 2005.

Answer 1

If the albums which are promoted and/or featured are a small subset of 'all' albums, then you should put them in a separate table.

If the promoted and/or featured albums are a significant proportion of the overall set then performance will be better if you add columns to your existing table.

I'm going to assume the first case; you have 'millions' of albums, and I'm sure you aren't promoting or featuring more than a few thousand, and definitely not tens of thousands. The lookup in this much smaller table will be very fast, and the join will also be fast provided that the 'main' table is clustered on primary key.

Answer 2

You mention a pending migration to SQL 2008. Filtered indexes were added in that release. A lot of the "use a different table" has to do with quick data access. By using a filtered index, you can get the best of both worlds (i.e. store the various statuses with your data while still being able to query a small table). It might be worth exploring when you're able to use SQL 2008 as a platform