|
|
Posted by sTony on 12/03/06 14:05
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
Navigation:
[Reply to this message]
|