Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  • Step 1. Find unknown velocity
  • Step 2. Find unknown time
  • Step 3. Apply acceleration formula

Each of these step 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.

share|improve this question
    
Can you clarify the relationship between Method and Knowledge? Is it (1:1): "every Method is a Knowledge but some Knowledge are not Methods". Or (1:n): "every Method belongs to a Knowledge (a method is a Knowledge step) and every Knowledge has one or more steps (Methods)" Or (n:m) "every Method can belong to one or more Knowledges and every Knowledge has one or more steps (Methods)"? –  ypercube Mar 2 '12 at 6:52
    
Or there are 2 different relationships between the two entities? –  ypercube Mar 2 '12 at 6:52
    
It could also be an optional relationship - i.e. each Knowledge has [0..n] Methods and each Method has [0..n] Knowledges. (i.e. "every" might be overly restrictive) –  Simon Righarts Mar 2 '12 at 7:47
    
Is this a bill of materials, perhaps? –  onedaywhen Mar 2 '12 at 12:58
    
I'm a bit confused as to why your "Methods" must also be in the "Knowledge" table. Is this actually necessary? How do you plan to use this data? –  FrustratedWithFormsDesigner Mar 2 '12 at 18:21

2 Answers 2

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"

share|improve this answer
    
Oh that's a good idea. So Step and Step ID are nullable too... But 1 problem is that "Find unknown velocity" can also be a step of another Method... –  zerlphr Mar 6 '12 at 8:04

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) 
share|improve this answer
    
Hi @ypercube, a Knowledge may or may not be a method. Hence, i'm thinking whether I should include KnowledgeSubType in Knowledge Table (There will be many knowledges that are not methods). Also i have 3 main types of Knowledges in Knowledge Type field (Factual, Conceptual and Procedural) but all three can be a method too, so adding Method as a value in Knowledge Type field might not work. –  zerlphr Mar 5 '12 at 6:49
    
@zelphr: I kept your KnowledgeType that would store your classification and differentiate between (Factual, Conceptual, Procedural). –  ypercube Mar 5 '12 at 7:07
    
The KnowledgeSubtype could keep M for Method, S for MethodStep, O for other (subtypes). So, you will have 2 classifications, independent of each other. –  ypercube Mar 5 '12 at 7:08
    
Hmmm those are good advice... i need some time to see if it can be applied to my database! thanks ypercube! –  zerlphr Mar 6 '12 at 7:47
    
i need some time to see if it can be applied to my database! thanks ypercube! So will that mean there are 2 relationships: Knowledge -- Method -- Method Step and Knowledge -- Method Step? Will there be any drawbacks? –  zerlphr Mar 6 '12 at 7:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.