Knowledge Management Database Design

[William McKinley]

Hello All,

I'm creating a draft Knowledge Management database structure to help my organization codify the vast amounts of information and improve the end-users ability to access this information. Ultimately, this system will be housed in something other than MS access, but I wanted to make sure I had a sound database structure intact prior to the future state solution. I've never really taken the time to set up a good relationship scheme in my databases so I thought this would be a good opportunity to rectify that. This is a rough design at the "Phase 1" information that will be included in the system. There will likely be more things down the road as we mature in our KM organization.

The main backbone behind this system is the Business Process Taxonomy, detailed with the 5 tables tblLevel1Taxonomy, etc. This is a hierarchal structure of processes that my organization executes which can help us create relationships between all the various knowledge that relates to them. Here's a rough idea of the hierarchy:

Level1: Highest Order Strategic Function (Human Resources)
Level2: "Workstream" Level Functions of Level1 (Recruiting)
Level3: "Value Stream" Level Functions of Level2 (Hiring)
Level4: "Process" Level Functions of Level3 (Interview Scheduling)
Level5: Executable Task of a Level4 Process (Fill out Interview Request Form)

For each level of these, there will be various types of information: Risks (tblRisks), Controls that mitigate those risks (tblControls), Templates and Populated Documents (tblDocuments), Metrics to guage performance (tblMetrics), and Process Owners tblResources). Initially I've used lookup fields to start creating connections between everything.

Here are some basic use cases I could see for the system:

1) What are the documents that related to Level1,2,3,4,5 Business Process?


2) Which process has the most risks associated with it? What controls are in place to mitigate those risks?
3) If I decide to outsource a Level2 Process, what all does that impact in the organization (basically a query that shows everything related to that process)?


Given that quick over view, how does this thing look? Table structure is below:

[ranman256]

Could this have worked as a self-relating table?\
rather than have 5 tables of taxonomy, youd have only 1.
Each level in the 1 table would be noted and have a [parent] field to lead to the next level up. (null would be the top)

levelid, Level,...
22, 1 , description, ProssOwner, DataOwner, null
23, 2, desc, pross, data, 22
24, 3, desc, pross, data, 23

[William McKinley]

Could this have worked as a self-relating table?\
rather than have 5 tables of taxonomy, youd have only 1.
Each level in the 1 table would be noted and have a [parent] field to lead to the next level up. (null would be the top)

levelid, Level,...
22, 1 , description, ProssOwner, DataOwner, null
23, 2, desc, pross, data, 22
24, 3, desc, pross, data, 23

I suppose it could. In that case, say I have a bunch of documentation created for varying levels of a Level 1 Process. How would I query to search for all documents related to that Level 1 process?