Database - tables setup

Posted on 2012-09-02
Last Modified: 2012-09-02
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
Question by:Panos
    LVL 39

    Accepted Solution

    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).
    LVL 51

    Assisted Solution

    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.
    LVL 2

    Author Comment

    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 .
    LVL 39

    Expert Comment

    by:Roger Baklund
    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.
    LVL 2

    Author Comment

    Now i understand why.
    Thank you

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
    The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now