43

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 ?

Improve this question

3 Answers 3

Reset to default
43

Nothing wrong with self-referencing tables.

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

Improve this answer
8
  • @NimChimpsky - Like the concept of recursion, this idea is difficult for some. Commented Nov 30, 2012 at 13:36
  • 2
    (At least) Oracle even has a special SQL contruct, the "START WITH - CONNECT BY" clause, to deal with self-referencing tables. Commented Nov 30, 2012 at 13:43
  • 1
    @user281377 - And SQL Server introduced the hierarchyid type. Commented Nov 30, 2012 at 13:46
  • usign hibernate so it will have its own special sauce Commented Nov 30, 2012 at 13:49
  • 4
    @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. Commented Nov 30, 2012 at 14:31
7

Necromancing.
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')) ALTER TABLE dbo.T_FMS_Reports DROP CONSTRAINT FK_T_FMS_Reports_T_FMS_Reports GO 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 GO CREATE 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 ,PRIMARY KEY CLUSTERED ( RE_UID ) ); GO ALTER TABLE dbo.T_FMS_Reports WITH CHECK ADD CONSTRAINT FK_T_FMS_Reports_T_FMS_Reports FOREIGN KEY(RE_RE_UID) REFERENCES dbo.T_FMS_Reports (RE_UID) -- ON DELETE CASCADE -- here, MS-SQL has a problem GO ALTER TABLE dbo.T_FMS_Reports CHECK CONSTRAINT FK_T_FMS_Reports_T_FMS_Reports GO

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 [but only if the graph is not cyclic], 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.

Improve this answer
1
1

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.