Can SQLite support this schema?

Question

Hi, imagine I have this table called Department. Each Department can have child Departments.

I was thinking of having a column called ParentDepartmentID as foreign key to another department. If this key is null, it means it's a Parent top class department and not child of any other department.

I guess you could call this a self referencing ID. Does SQLite support this? Having a foreign key, but also allowing nulls?

How would you solve this use case?

Answer 1

Yes, sqlite does support that. You can easily set a foreign key that references the same table. The model that you describe is called the adjacency list model, and as is very popular.

However, the problem with the adjacency list model is that recursive operations are very difficult to write unless the DBMS can do recursive queries.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g¹, support recursive queries with common table expressions using the WITH keyword). This feature allows recursive queries to be written easily, but sqlite and MySQL do not support recursive queries yet.

You may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations much easier, even though the nested set model is definitely more complex than the adjacency list:

• Mike Hillyer: Managing Hierarchical Data in MySQL

In addition, I also suggest checking out the following presentation and posts by @Bill Karwin, a regular contributor on Stack Overflow²:

• Models for hierarchical data with SQL and PHP
• What is the most efficient/elegant way to parse a flat table into a tree?
• Rendering Trees with Closure Tables

The closure table model described in those links is a very valid alternative to the nested set model. Bill further describes this model in his SQL Antipatterns book (excerpt from the chapter on this topic [PDF]).

Otherwise, you could also stick to the simple adjacency list model and then do the recursive part in your application, downloading all the data, building a tree, and walking through it.

---

¹ Oracle can also do recursive queries using CONNECT BY, which is supported since version 2.

² You may also want to check out the following Stack Overflow post: Recursive data processing performance using Java and SQLite, discussing this topic.

Answer 2

Why not?

sqlite> create table selfref (id integer primary key, 
   name string, parent integer references selfref (id));
sqlite> .schema
CREATE TABLE selfref (id integer primary key, name string, 
   parent integer references selfref (id));
sqlite> insert into selfref values (null, "alice", null);
sqlite> insert into selfref values (null, "bob", null);
sqlite> insert into selfref values (null, "charlie", 
    (select id from selfref where name = "alice"));
sqlite> select * from selfref;
1|alice|
2|bob|
3|charlie|1
sqlite>