[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2005 : Table Design Question

Posted on 2012-12-21
3
Medium Priority
?
269 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 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 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