Tree Hierarchy in SQL With Ordered Children and Multiple Possible Parents

Question

I am building a curriculum system that is very much like a tree or hierarchy of nodes. I have a few requirements that makes it a little more complicated. I am looking for some additional suggestions on an approach.

• The order of child nodes needs to be maintained, based on how they are added to a parent. In other words, the order is not based on a title or something like that.

• The same node may be a child of multiple different parent nodes.

For example, if we have the following "curriculum" nodes...

- Course A
- Course B
- Course C
- Plan A
- Plan B
- Plan C

And we want to construct the following hierarchy / curriculum...

- Plan A
  - Course A
  - Course B
- Plan B
  - Course A
- Plan C
  - Plan B
  - Course C

I have been able to do this using two different approaches.

1. Single Table

- node_id - the id of the node
- node_name - the name of the node
- children - an array of ordered child node ids

2. A Nodes Table and Relationships Table

- Nodes Table
  - node_id
  - node_name

- Relationships Table
  - parent_id
  - child_id
  - index - the order of the child

In both of these, I was able to use WITH RECURSIVE to perform certain queries, and can continue with these approaches. However, I am looking to see if I am missing any better solution for modeling this in SQL.

I have looked at Adjacency List, Nested Sets, and Closure Table. I don't think Adjacency List will work because of the multiple parent requirements. I think my multiple table approach is similar to a Closure Table, but not exactly. I am not sure about Nested Sets.

I appreciate any suggestions!