|
|
Posted by Tom on 12/04/06 01:40
On Sun, 03 Dec 2006 14:05:44 GMT, 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
>
>
Like Paul had mentioned, I don't know if it would be feasible to require all
keys/ids to be unique. I think I understood what you were trying to say though.
If you have a rough count of the total countries, you can start that index at 1
and will reach about 300 in the end if there's that many. To tackle cities you
can start you index at maybe 1100 and auto-increment from there. I don't think
you'd want to require that there's never an overlap in the ids, but if you start
their indexes at different points, you can get a rough guess if the id is for a
state, country or city.
I've seen countries with over 25 characters but tomorrow a new country can be
created with 45. Using a plain text field may be lower maintenance, but if you
use something like varchar(35) to create tour tables you can use an alter
command later to redefine that.
Tom
--
Newsguy Express 30 GB / month $9.95
http://www.newsguy.com/overview.htm
Navigation:
[Reply to this message]
|