Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

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

closed as primarily opinion-based by jwenting, Kilian Foth, durron597, Snowman, Ixrec Sep 15 at 23:02

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers 3

up vote 25 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
@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. 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.

share|improve this answer

The correct answer is: it depends on which database engine and on which management tool.

Let's make an example:
We have a report table,
and a report can have a parent (menupoint, like category),
and that parent can itselfs have a parent (e.g. Profit center),
and so on ad infinitum.

The most simple example of a standard recursive relationship, as with any self-referencing entity / hierarchy.

The resulting SQL-Server table is:

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_T_FMS_Reports_T_FMS_Reports') AND parent_object_id = OBJECT_ID(N'dbo.T_FMS_Reports'))

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.T_FMS_Reports') AND type in (N'U'))
DROP TABLE dbo.T_FMS_Reports 

     RE_UID uniqueidentifier NOT NULL 
    ,RE_RE_UID uniqueidentifier NULL 
    ,RE_Text nvarchar(255) NULL 
    ,RE_Link nvarchar(400) NULL 
    ,RE_Sort int NOT NULL 
    ,RE_Status int NOT NULL 


-- ON DELETE CASCADE -- here, MS-SQL has a problem 


But you get a problem:
When you need to delete a menupoint with all its submenupoints, you CANNOT set delete-cascade, because Microsoft SQL-Server doesn't support recursive cascaded deletes (on the other hand, PostGreSQL does, while MySQL doesn't like this kind of table structure at all, because it doesn't support recursive CTEs).

So you kinda blow up deletion-integrity/functionality with it, making it mandatory to implement such functionality in your own code, or in a stored procedure (if your RDBMS supports stored-procedures).

This will no doubt blow up any kind of fully-automatic dynamic data import/export, because you can neither simply run a delete statement for all tables according to (non-self referencing) foreign-key relationships, nor can you do a simple select * and create an insert for every row in an arbitrary order.

For example, when you create an INSERT script using SSMS, then SSMS won't get the foreign key, and thus does indeed create insert-statements that will insert entrys with dependencies, before it inserts the dependency's parent, which will fail with an error, because the foreign-key is in place.

However, on proper database management systems (like PostgreSQL), with proper tooling, this shouldn't be a problem. Just undestand that just because you pay a lot for your RDBMS (I'm looking at you, Microsoft; Oracle = ?), and/or its tool-belt, it doesn't mean it is programmed properly. And neither does OpenSource (e.g. MySQL) make you immune to such wonderful minutiae.

The devil is in the details, as the old saying goes.

Now, not that you couldn't work-around such problems, but I really wouldn't recommend it, if your system is gonna be complex (e.g. 200+ tables).
Plus, in a usual commercial setting (as portrayed by Dilbert), you just won't be given that time.

A much better approach, though more difficult, would be a closure table.
That would have the added bonus that it also works on MySQL.
Once you implement the closure-functionality once, you'll have it working in additional places in almost no time at all.

share|improve this answer
+1 on bringing closure tables to my attention (at least the terminology, already knew the concept). Here's a good article on it for other who might be interested. – Outfast Source Sep 30 at 12:26

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