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 |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2004-02-05 : 11:48:14
|
| I was wondering, what is the best (most efficient) way of storing street addresses so that a list of streets can be generated when a postal code is selected, or a list of postal codes are listed when a city & state (or Province) are selected, etc...Country | State/Province | County | City | Zip/Postal | Street AddressWould the above be efficient, or would this have to be broken down into separate tables ? If separate tables, could someone provide an example. If I did an EDR on this, it looks as though splitting the tables up would be overkill.EG.tblCountries Country | AbbreviationtblTerritoriesCountry | State/ProvincetblCountiesCountry | State/Province | CountytblCitiesCountry | State/Province | County | .....Etc.... Even if you ended up using IDs, it is still redundant?Mike B |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-05 : 12:20:30
|
| Stick with Natural Keys...How many rows are you expecting in the final table with street addresses?If it's not a lot, you can probably get away with 1 table....Or at least 2...with with adresses, one without....Brett8-) |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-05 : 12:37:51
|
quote:
Originally posted by X002548
Stick with Natural Keys...How many rows are you expecting in the final table with street addresses?If it's not a lot, you can probably get away with 1 table....Or at least 2...with with adresses, one without....Brett8-)
What do you consider alot? 100,000. Would that be too much?With one table, I would have to index all the fields for faster searching correct?Mike B |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-05 : 16:21:22
|
| Well maybe use 2 tables...the first one with out the address should be smallish..and the second you would have no choice...How do you expect to get at the data?Brett8-) |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-06 : 09:31:57
|
| I decided I was not going to include addresses because, like you said, it would be smaller. I just want to use it as a lookup table to help speed up entering addresses in my application. When the user enters a city, it will show a list of provinces, enter a postal code then automatically fill in city state, etcc....Mike B |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-06 : 09:46:23
|
| Yeah...did something similar...Based on a series of combo boxes...Pick a state...County combo is limted to those in that state...Pick a county...City combo is limited to those cities in that county...Pick a City...zip code is limited to those zips in that city...(some have more than 1)Then they just type in the street address...Don't forget PO BoxGood LuckBrett8-) |
|
|
|
|
|
|
|
|
Address tables?