How to model double relationships? Background: I have designed a solution for Filemaker to input data into a Question Database. 

After we are done with inputting data into Filemaker, we will be exporting the data as excel and transferring it into an online system probably running on ASP.NET or PHP

Part of it looks like this:


Knowledge Table:
Knowledge ID (Primary Key),
Description,
Knowledge Type

Method Table:
Method ID (Primary Key),
Step,
Step Knowledge ID


For example. The "Find Acceleration method" Knowledge can have 3 steps:


Find unknown velocity
Find unknown time
Apply acceleration formula


Each of these steps is also a record in the Knowledge Table, i.e., the steps are knowledges in Knowledge Table.

How do I model the double relationship between Knowledge Table and Method Knowledge Step Table?

Currently, I thought of 2 ways:


Knowledge Table::Knowledge ID -- Method Table::Method ID relationship AND Knowledge Table::Knowledge ID --&lt; Method Table::Step Knowledge ID
Creating another instance of Knowledge Table and link the Knowledge ID separately to Method Table: Step Knowledge ID.


I'm really not too sure of what is the best way of doing it.
Since a Knowledge might be a Method, I'm wondering if it would make more sense to store them both in the same table:



KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type
  Step
  Step_ID


Based on your example, I think the data would be stored as:


ID Description                  Parent_knowledge  Step      Step ID
-------------------------------------------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"         1               "Step 1"     S1
3 "Find unknown time"             2               "Step 2"     S2
4 "Apply acceleration formula"    3               "Step 3"     S3
5 "Land rocket ship"              



With recursive queries, it could be very easy to get data back from this kind of table.

You could have a KnowledgeSubtype or MethodSubtype column if you want, but I'm not sure if you would gain anything.



Based on comments, it sounds like a many-to-many relationship is wanted. So here's another try at it:



KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type

Methods_Steps
  Parent_Knowledge_ID - the ID of the parent Knowledge
  Step
  Step_ID - The ID local within a set of Steps
  Method_ID  - The ID of the Method that is a Knowledge


So now the previous example might look like:


KnowledgeAndMethods:

ID Description                  
--------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"       
3 "Find unknown time"           
4 "Apply acceleration formula"  

Methods_Steps:
Knowledge_ID  Step      Step_ID    Method_ID
--------------------------------------------
1             "Step 1"  1          2
1             "Step 2"  2          3
1             "Step 3"  3          4
5             "Step 9"  6          2   &lt;---- Here's an example of "Find Unknown Velocity" being a different "Step" in a diffent "Knowledge"