Solved

SQL Server 2005 : Table Design Question

Posted on 2012-12-21
3
264 Views
Last Modified: 2012-12-21
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
0
Comment
Question by:hefterr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 38713234
If we are talking about a mapping between an article and states then is recommendable to insert a row for each combination you want. This way a simple join with the mapping table will give you all the rows you need otherwise you will have to add some condition in the ON clause for the case the code is 99, which will probably not make an optimal use of indexes. Make sure you have the following indexes on the ArticleState  table:

1. A unique composite index on both columns: ArticleId, StateAbbrev
2. A non unique index on StateAbbrev only


First index will used for ArticleId or ArticleId and StateAbbrev searches/joins while the second one will be used when you want to get articles per states.

The economy of space you would get in the case using the "99" method is insignificant compared to the good practice and design you will get from a complete and real mapping. If a user uses ALL then make sure you will insert all the states with that article. It is not complicated and will still be fast. It is actually simpler to just get the list of the states/countries chosen, regardless if is one, many or all, and just insert them in the table as you would not have to do any auxiliary logic based on the ALL choice.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 38713441
I suggest you stick with adding all the rows rather than an encoded row, for several reasons:

A) What happens if you add a new geographic region, say Canada, to the table ... does "All" still mean "All"?  Or will you get into "99"=all states,98=all Canada provinces,100=all states&all provinces, etc.?  That will quickly get even more complex.

B) You would have to read a different table to get "all" rows, which actually adds some slight overhead when you need all.

C) The rows are short anyway.


The clustered key should be:
(StateAbbrev, ArticleId)

If you need an index on ArticleId, it should be nonclustered.
0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 38713536
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.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question