Posted by Paul Lautman on 12/03/06 17:48

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.

[Back to original message]