Solved

SQL Server 2005 : Table Design Question

Posted on 2012-12-21
3
260 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now