Solved

SQL Server 2005 : Table Design Question

Posted on 2012-12-21
3
261 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
3 Comments
 
LVL 26

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

772 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