SQL Table design question
- by Projapati
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.
