Avatar of hefterr
hefterr
Flag for United States of America asked on

SQL Server 2005 : Table Design Question

Hi,
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:
ArticleId    int
StateAbbrev   char(2)

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,
hefterr
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
hefterr

8/22/2022 - Mon
SOLUTION
Zberteoc

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
hefterr

ASKER
Thanks to both of you.  I had been thinking along these lines, but I wanted to get an opinion from someone with more DB expertise.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy