SQL table design issue

Question

Please ignore this question if it seems silly to you.

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

I need to label some albums as featured and some as Favorite every week. I also need a very effective way to get these albums (per page) when I show them to users.

How do I create this?

option 1: I can create another table to save the identifiers of advanced and recognized albums like this, and then join the main album table to get the set of columns that I need.

tableited_albums:

album_id promoted_featured 1 1 5 0 7 1 15 0 

Promotion request will be returned 1, 7 Request for attribute will return 5, 15

Option 2: I can add 1 column store 1 if it was advanced, and 0 if it is specified. Otherwise, it is zero. Then I can request a check of 1 in this column for advanced albums and 0 for showing.

Option 3: I can add 2-bit columns: one for advanced (0/1) and one for recognized (0/1)

Which way will work better?

EDIT: Design should also be effective in SQL 2008. I now have SQL 2005.

Answer 1

If the albums that are advertised and / or submitted are a small subset of the “all” albums, you should put them in a separate table.

If advanced and / or featured albums are a significant part of the overall set, then performance would be better if you add columns to an existing table.

I am going to accept the first case; you have millions of albums, and I’m sure you don’t advertise or display more than a few thousand, and definitely not tens of thousands. A search in this much smaller table will be very fast, and a join will also be fast provided that the "main" table is clustered to the primary key.

Answer 2

You mentioned the pending migration to SQL 2008. Filtered indexes were added in this version. Most of the "use of another table" is associated with quick access to data. Using a filtered index, you can get the best of both worlds (i.e. Store different statuses with your data, while still having the ability to query a small table). It may be worth exploring when you can use SQL 2008 as a platform.

Answer 3

Create another table. A small table prompts quickly. Join PK with your main table will also be fast. In addition, a small table is easy to modify when requirements change (and not regardless of whether you know).

Think about how many values ​​in the secondary column of the main table will not be used and just keep the same “inactive” value. The more rows in the table, the slower access to the entire table.

Answer 4

I would create a main table called Status that will have the following columns.

Status_tb

 status_id status_name 0 None 1 Feautured 2 Promoted 3 Both 

Note. Both options mean both advertised and shown.

Now, in the album transaction table, I will add a column called status_id. This will be the fk link to the main table.

It is clean and efficient. (Very skewed columns and therefore also help in histograms and sections)