Interesting Large Table Design Recommendation

Hi,

What's the most efficient way to store the following information:

* Table contains 1 million listings
* Each listing can be geo-targeted to any of the 200+ countries
* Searches return listings based on location

Storage options:

Option #1 (normalized)
* Listings (PK listingID int) [1 million rows]
* ListingLocations (listingID, locationID) [could be up to 200 million rows]

Option #2 (denormalized)
* Listings (PK listingID int, binary(32) with bit-mask consisting of 200 bits one for each location)

Usage: Usually the query will simply lookup listings based on some keywords. It will get back 50-200 listings. Then the application (C#) will filter the listings based on location.

Did anyone have experience with similar structures? Which option is more efficient?

I know that using the intersection-table in Option #1 is the "proper" relational-DB way of doing things. However, I do not like the idea of storing the listingID so many times (ones for each locationID).

Thanks,
Av

I think too the option #1 is the good solution and yourself explain this ("I do not like the idea of storing the listingID so many times (ones for each locationID)).

But i have an ideea vis-a-vis the way the users use your db: if your users should query the data using geographical criteria, let'say : country of West or Sud or East Europe, Africa etc you can use partitioned table with boundary values that geographic zones. So you'll grow the performance.

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!
I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).

Interdev, SQL Server 2000 vs. SQL Server 7; open/design problems

I run Visual Interdev 6... I have 2 web servers and 2 database
servers... (1 of each located in my home - A; and 1 of each co-located
at an ISP - B with our own firewall at each location).
Location A:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS5
SQL Server: 7
Location B:
Firewall, port 1433 opened (for specific IP#'s)
Web Server: IIS6
SQL Server: 2000
For both locations, I can edit files and open databases. For location
A, I can also DESIGN databases (create new ones, edit existing ones)
through InterDev. I can NOT do this at Location B and its driving me
nuts. While I can open databases and view the contents, I can not
create new databases through Interdev nor add fields to existing ones.
I've made sure that BUILTIN/Administrators has the same access (and I'm
an administrator on all 4 servers) on both SQL servers.
Any ideas what I am missing here? Its driving me nuts having to
terminal service into Location B's database server everytime I want to
create a new database or even a new field!I should mention that while I'm away from home, I can still design
databases from my home servers so it isn't being "inside" a firewall
vs. "outside". I have the exact same problem no matter WHERE I am
connected from (I tend to travel alot).

Intellisense in Views corrupts SQL Syntax

When selecting "Design mode" from "Views", the syntax I enter is
automatically changed on-the-fly and corrupts the syntax.
For example from pubs:
SELECT TOP 100 PERCENT *
FROM dbo.authors a INNER JOIN
dbo.publishers p ON a.city = p.city
ORDER BY a.au_lname DESC
Problems:
1. I can't remove "TOP 100 PERCENT" which is valid SQL.
2. "a INNER JOIN" should be "AS a INNER JOIN"
So, how can I code valid SQL without the code being changed?
Sparky
and the phrase "a INNER JOIN" should be:
"AS a INNER JOIN"which would be valid
EM has a lot of strange things in the query builder. I recommend developing your views in Query
Analyzer. As for TOP, you can't have ORDER BY without TOP in a view. A view is supposed to behave as
a table and a table is not ordered. A view allow ORDER BY if you have TOP as this will influence
which rows are returned, the ordering is possibly still not guaranteed. You might want to look at
this:
http://www.aspfaq.com/show.asp?id=2455
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sparky" <sparky@.boing.com> wrote in message
news:7F7B6BCF-CD0C-405B-8A11-7AC6E268E80F@.microsoft.com...
> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
> For example from pubs:
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
> So, how can I code valid SQL without the code being changed?
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid
|||The problem arises when I use Visual Studio 2003's Server Explorer tool to
explore a selected "View" from the sqlserver's treeview. I was not using EM
or QA to edit the view.
Tibor: Thnaks sooo much for the reply - saw your web site -- Great!
Sparky
================================================
"Sparky" wrote:

> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
> For example from pubs:
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
> So, how can I code valid SQL without the code being changed?
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid