Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-07 : 12:11:05
|
| Lee writes "I would appreciate any input on how to best design some db tables for the following senario which deals with a lot of zip codes to resolve the problem. First I have a number of stores in a large city but spread out enough from each other that they each have a different zip code:store1 12345store2 12355store3 12365store4 12375etc.Each of these stores are a few to several miles apart and will deliver a package within a 5 mile radius of their store. Some of the zip codes they deliver to are the same but each store has zip codes that they uniquely deliver in. How do I most efficiently set up my table(s) to show that stores have commom zip codes and yet each have certain ones which are unique. I want to be able to keep the number of zip codes in the tables to the bare minimum for if I have 50 to 100 stores in a 1000 different city, that's 100,000 stores and a very large number of zip code entries which grows large quickly, and these numbers could be on the low side to boot! I need to be able to qry and see for example, who delivers to 12360? Ideas, please. Lee" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-07 : 12:15:35
|
| You're talking about taking normalization to a level that I wouldn't suggest.In other words, you want to have a key for zip so that it's only reference in a zip tablesuch that 1 = '07052'Don't do it...it's gonna be just an extra join...And I get a sense that you might also want to look in to longitutde and latitude...Brett8-) |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-07 : 12:22:58
|
| If your Talking about limiting to exactly a 5 mile radius the Zip code will not work.You would have to convert it to something like a GPS then perform a compair.JimUsers <> Logic |
|
|
|
dleec45
Starting Member
2 Posts |
Posted - 2004-06-07 : 23:04:12
|
| Hey Jim, Brett, thanks for the reply. As for the zip codes that are needed, I already have most that cover a 15 mile area in almost all areas that I need, so I'm not worried about that. What I'm not sure about is how to put the data together the most efficient way. Does it make sense to use just one big table and put all the zips and say store numbers together ? That way I could pull out all the stores by pulling all the zip codes but it would have to be indexed, right?? Does this make sense? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-06-07 : 23:31:03
|
| 1) Zips (ZipCode as Key)2) Stores (StoreName or StoreID as Key, ZipCode as Attribute or Candidate Key)3) StoreDeliveryZips (Composite Key of the above)Create a view for common Delivery Zips and one for Unique zips..DavidM"Always pre-heat the oven" |
|
|
|
dleec45
Starting Member
2 Posts |
Posted - 2004-06-08 : 16:48:57
|
| Thanks DM for the reply. That sounds like it should cover just about everything. Regards, LC |
|
|
|
|
|
|
|
|
db table design