Database - tables setup

Hello experts.
I have collected some data of countries , there regions and subregions.
I need help to organize these data in tables.
Below i have 3 examples of what i have in mind. I think that the second example is what i should use.(i use these for my categorytrees)
I will need these data for related selectboxes and for autocomplete text fields.

Your suggestion?

1.First example
country_id  , country , lat , log
1            Germany

country_id , region1_id , region ,lat , log
 1              2        Bayern
region1_id , region2_id , region  , lat , log
  2             3          Munich

region2_id , region3_id , region         ,lat , log
  3             4         subofmunich            

region3_id , region4_id , region ,lat , log

region_id , region      , subof  , regionpath ,lat ,lon
1         , Germany     ,   0          /
2         , Bayern      ,   1    ,   /1/
3         , Munich      ,   2    ,   /1/2/    
4         , subofmunich ,   3    ,  /1/2/3/

Country_id ,country, region1_id ,region1, region2_id ,region2 ,region3_id ,region3....
 1         ,Germany, NULL       ,NULL   ,   NULL     , NULL   ,NULL       ,NULL
 1         ,Germany,  1         ,Bayern ,   NULL     ,NULL    ,NULL       ,NULL
 1         ,Germany,  1         ,Bayern ,    1       , Munich , NULL      ,NULL
Who is Participating?
Roger BaklundConnect With a Mentor Commented:
Go for the 2. example. Are you sure you need regionpath? It can be found following the "subof" property (which should be indexed) until you reach zero (0 can be used, but NULL is preferable).
_agx_Connect With a Mentor Commented:
Related select boxes only require lookup by child/parent ID. If that's all you're using them for, I agree with cxr. Example 2 seems the most appropriate.  But also think about how else you'll be using the tables.  Add, edit, search screens...?

(Edit)         > regionpath  /1/2/3/

Storing paths is convenient sometimes. But bear in mind your app must keep it updated. ie If the app edits a parent record, it must update all child records as well.  Though like cxr said, i'm not sure if you need it.
PanosAuthor Commented:
Thank you cxr and agx.I will go on with example 2.
Cxr about your advice to use null instead of 0 for the main category. I read that for indexing i must use a default value (f.e 0) and not null .
Roger BaklundCommented:
No, you do not need NOT NULL. This index can not be unique, because there can be multiple sub-regions of the same region (i.e. same subof value).

Using 0 implies that there is a row with region_id=0 (which there isn't), while using NULL means there is no subof value for this row... which seems correct for the top-most region.
PanosAuthor Commented:
Now i understand why.
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.