Sean Hynes wrote:
> Thank you very much for the quick response and help!
> Maybe creating a table per state would be in order, because the data could
> easily get up to 1,000 records per city.
> If we use major metropoliton cities, there probably won't be many multiple
> city searching. That won't be a funcition of
> a query anyway.
> So what is the majic number to switch to seperate tables maybe around 1,000
> per table?
> What else would be a reason for looping across multiple tables?
> Thanks again for advice! You da man!
Most yellow page sites require that you select a state before you
proceed. If that is how you want to do it, then having separate tables
for each state is a great idea. You can even spread them across the
servers, if necessary. In fact, I would recommend starting out with this
in mind, so that you have a hash that looks at the state and determines
which server it is on, even if all states will initialy be on one
server. If you start running out of resources, then all you need to do
is get a separate server, move some tables there, and change your
hashing algorithm. This is scalable up to 50 servers. I do not think
that the US economy will ever get to the level of requiring more than 50
servers to run a yellow pages site :-), (at least in the near future)
This approach also makes room for future redundancy capability. You keep
redundant tables on your servers, and make your hash aware of it. Then
if something fails on the primary server for the state instead of just
giving up you go to the secondary.