• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1050
  • Last Modified:

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:
country_id  , country , lat , log
1            Germany

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

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

Region4:
region3_id , region4_id , region ,lat , log


2.example
regions
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/



3.example
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
0
Panos
Asked:
Panos
  • 2
  • 2
2 Solutions
 
Roger BaklundCommented:
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).
0
 
_agx_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.
0
 
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 .
0
 
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.
0
 
PanosAuthor Commented:
Now i understand why.
Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now