How Many Cities?

I want to be able to track the location of things, and I've created the
following tables to help me do this, but I've got some questions. How many
cities are in the world? Which country has the longest name? How long is it?
As you can see, I'm just wondering on the space requirements. Take a look at
this....

<<<<>>>> Pseudo SQL <<<<>>>>
apt( id, lot_id, name )
lot( id, street_id,name )
street( id,area_id,name )
area( id, city_id, name )
city ( id, region_id, name )
region( id, state_id,name )
state ( id, country_id, name )
country ( id,name )

Now the id's must be unique across all tables. This is so a location field
in another table can reference any one of the location tables, allowing me
to set the location of an item to either a specific place, or a more general
one. I'm thinking I can do this by setting the default of the
auto_incrementing id's to the maximum possible id of the table it references
with its second attribute. So, if there are, lets say, 300 countries in the
world, then the default value for state would be 300. If each of those 300
countries had 50 states, then the default for region would be 300*50+300,
and so on. This will work, right? I think it will. I'm hoping so. Anyhow, as
I said, I'm wondering about space requirements. Given the size of the
world, and the fact that all my id's must be unique, what sizes should they
be? Also, how long is long enough for the place names? Any thoughts are
appreciated, especially if you think it won't work.

sTony

Re: How Many Cities?

sTony wrote:
> I want to be able to track the location of things, and I've created
> the following tables to help me do this, but I've got some questions.
> How many cities are in the world? Which country has the longest name?
> How long is it? As you can see, I'm just wondering on the space
> requirements. Take a look at this....
>
> <<<<>>>> Pseudo SQL <<<<>>>>
> apt( id, lot_id, name )
> lot( id, street_id,name )
> street( id,area_id,name )
> area( id, city_id, name )
> city ( id, region_id, name )
> region( id, state_id,name )
> state ( id, country_id, name )
> country ( id,name )
>
> Now the id's must be unique across all tables. This is so a location
> field in another table can reference any one of the location tables,
> allowing me to set the location of an item to either a specific
> place, or a more general one. I'm thinking I can do this by setting
> the default of the auto_incrementing id's to the maximum possible id
> of the table it references with its second attribute. So, if there
> are, lets say, 300 countries in the world, then the default value for
> state would be 300. If each of those 300 countries had 50 states,
> then the default for region would be 300*50+300, and so on. This will
> work, right? I think it will. I'm hoping so. Anyhow, as I said, I'm
> wondering about space requirements. Given the size of the world, and
> the fact that all my id's must be unique, what sizes should they be?
> Also, how long is long enough for the place names? Any thoughts are
> appreciated, especially if you think it won't work.
>
> sTony

Requiring the ids to be unique across all tables is a database no-no.

Since your location tables are hierarchical, why not have a companion field
to the location field in the other table, that states the level to which the
location id refers.

Re: How Many Cities?

Post removed (X-No-Archive: yes)