Solved

SQL Server 2005 : Table Design Question

Posted on 2012-12-21
3
259 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:
ScottPletcher 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

17 Experts available now in Live!

Get 1:1 Help Now