Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?).

One table with a self referencing parent_id column uk - london (london parent id = UK id)

or two tables, with a one to many relationship using a foreign key.

My preference is for one self-refercing table as it easily allows to extend into as many sub regions as required.

IN general do people veer away from self referencing tables, or are they A-OK ?

share|improve this question

2 Answers 2

up vote 22 down vote accepted

Nothing wrong with self-referencing tables.

It is the common database design pattern for deeply (infinity?) nested hierarchies.

share|improve this answer
    
@NimChimpsky - Like the concept of recursion, this idea is difficult for some. –  Oded Nov 30 '12 at 13:36
    
(At least) Oracle even has a special SQL contruct, the "START WITH - CONNECT BY" clause, to deal with self-referencing tables. –  user281377 Nov 30 '12 at 13:43
    
@user281377 - And SQL Server introduced the hierarchyid type. –  Oded Nov 30 '12 at 13:46
    
usign hibernate so it will have its own special sauce –  NimChimpsky Nov 30 '12 at 13:49
3  
@NimChimpsky - Consider looking at the "Nested Set Model" as an alternative to that "parent_id" column too - it provides the same functionality, but better performance and easier queries to extract the hierarchies. en.wikipedia.org/wiki/Nested_set_model Joe Celko's book series "SQL For Smarties" has some great sample SQL regarding nested sets. –  Keith Palmer Nov 30 '12 at 14:31

It's a good idea if the relationship is actually hierarchical, and not a network relationship (for example a Bill of Materials is a network relationship, not a hierarchical one).

It can be slow to query. To speed things up, you can use a Closure Table.

http://karwin.blogspot.ca/2010/03/rendering-trees-with-closure-tables.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.