How to model double relationships?

Question

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:

1. Find unknown velocity
2. Find unknown time
3. 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:

1. Knowledge Table::Knowledge ID -- Method Table::Method ID relationship AND Knowledge Table::Knowledge ID --< Method Table::Step Knowledge ID

2. 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.

Answer 1

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   <---- Here's an example of "Find Unknown Velocity" being a different "Step" in a diffent "Knowledge"

Answer 2

I think you need two things:

• Break the Method into 2 tables (Method and details, say MethodStep)

• Use the supertype/subtype pattern with Knowledge as supertype and Method and MethodStep (and possibly other you may have) as subtypes.

Design details:

KnowledgeSubtype_Ref
--------------------
KnowledgeSubtype    CHAR(1)
Description
PRIMARY KEY (KnowledgeSubtype) 

INSERT INTO KnowledgeSubtype_Ref
    VALUES  
      ('M', 'Method') ,
      ('S', 'Method Step') ,
      .... ;                   --- other subtypes of Knowledge   

Knowledge
---------
KnowledgeID         INT  AUTO_INCREMENT
KnowledgeSubtype    CHAR(1)
Description 
KnowledgeType
PRIMARY KEY (KnowledgeID) 
UNIQUE KEY (KnowledgeID, KnowledgeSubtype) 
FOREIGN KEY (KnowledgeSubtype) 
    REFERENCES KnowledgeSubtype_Ref (KnowledgeSubtype) 

Method
------
MethodID            INT                 --- no  AUTO_INCREMENT
KnowledgeSubtype    DEFAULT 'M'
PRIMARY KEY (MethodID) 
FOREIGN KEY (MethodID, KnowledgeSubtype) 
    REFERENCES Knowledge (KnowledgeID, KnowledgeSubtype) 

MethodStep
----------
MethodStepID        INT                 --- no  AUTO_INCREMENT
KnowledgeSubtype    DEFAULT 'S'
MethodID
Step
PRIMARY KEY (MethodStepID)
UNIQUE KEY (MethodID, Step) 
FOREIGN KEY (MethodStepID, KnowledgeSubtype)
    REFERENCES Knowledge (KnowledgeID, KnowledgeSubtype) 
FOREIGN KEY (MethodID) 
    REFERENCES Method (MethodID)