SQL Server 2005 : Table Design Question
Posted on 2012-12-21
I have an application that collects articles on the web. One aspect is geographic associations with an article (such as US states).
I have a table ArticleState table:
A user can specify any number of state for an article. The user can also select "all" and every state in a scroll box becomes checked.
My question is : If a user selects "All States", should I store 50 rows for the article (1 for each state) or should I store just a single "psydo row" with a state code of say, '99' which means all states.
This is better performance as I don't have to store 50 rows in this case - but the state ID now has a special meaning and requires special consideration when inquiring the table or joining the table.
e.g. : If I have to list all states associated with an article, I first have to see if the article has state "99" and then list all states from the StateRef table.
I have a similar issue with a ArticleCountry table.
What do you think?
1) Create all rows for "all"?
2) Have a single row with a special state/country code for "all"?
Thanks in advance,