Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.
COURSE of the MONTH
13 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Modeling a Base of Knowledge
Posted on 2014-03-03
I'm involved in a project of creating a base of knowledge for human resources in projects. When doing the ER model of a little part of the problem a doubt assails me: which is the preferred way of modeling it, considering efficiency, good design practices and database normalization. The application will be developed in .NET, but It could be queried using php, for example. We have 4 levels of entities which represents our knowledge base:
1 Supertopics
1.1 Topics
1.1.1 Subtopics
1.1.1.1 knowledge
I'm among two possible relational implementations:
If using weak entities, each table will have a composed key: a unique Id plus the keys of the foreign tables of previous levels. It seems to be better to make complex reports with this model because of the replication of keys form all previous levels.
- supertopic (IDSupertopic,...)
- topic (IDSupertopic, IDTopic,...)
- subtopic (IDSupertopic, IDTopic, IDSubtopic,...)
- knowledge(IDSupertopic, IDTopic, IDSubtopic, IDKnowledge...)
If using strong entities, each table will have a simple unique key, and a foreign key from the previous level object (table). It seems easier to manage this kind of model because of the simplification of table keys.
- supertopic (ConsecutiveKey,...)
- Topic (ConsecutiveKey, IDSupertopic, ...)
- subtopic(ConsecutiveKey, IDTopic...)
- knowledge(ConsecutiveKey, IDSubtopic...).
Can anyone give me some advise related with advantages and disadvantages of each possibility?
1 Supertopics
1.1 Topics
1.1.1 Subtopics
1.1.1.1 knowledge
I'm among two possible relational implementations:
If using weak entities, each table will have a composed key: a unique Id plus the keys of the foreign tables of previous levels. It seems to be better to make complex reports with this model because of the replication of keys form all previous levels.
- supertopic (IDSupertopic,...)
- topic (IDSupertopic, IDTopic,...)
- subtopic (IDSupertopic, IDTopic, IDSubtopic,...)
- knowledge(IDSupertopic, IDTopic, IDSubtopic, IDKnowledge...)
If using strong entities, each table will have a simple unique key, and a foreign key from the previous level object (table). It seems easier to manage this kind of model because of the simplification of table keys.
- supertopic (ConsecutiveKey,...)
- Topic (ConsecutiveKey, IDSupertopic, ...)
- subtopic(ConsecutiveKey, IDTopic...)
- knowledge(ConsecutiveKey, IDSubtopic...).
Can anyone give me some advise related with advantages and disadvantages of each possibility?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
5 Comments
Active today
I'm not sure I'd attack it either way. I've never done a knowledge base, but your outline is really no different then a BOM structure in MFG.
As a start:
tblTopics - One record per topic
TopicID - AN - PK
Title - Text - CK1 - Brief title
Synopsis - Text - Overall description of the topic
Knowledge - Text
now:
tblTopicStructure - One record per Topic/SubTopic pair
TopicStructureID - AN - PK
TopicID - LONG - CK1A - FK to tblTopics
SubTopicID - LONG - CK1B - FK to tblTopics
With this, you can have any number of subtopics for a given level, any number of levels to a topic structure, with knowledge at each level (or none), and topics can be shared between structures.
For example, the same topic "poisonous frogs" might appear under a topic of "poisons" and "frogs"
You might also want to add:
tblTopicTags - One record per topic/tag
TopicTagID - AN - PK
TopicID - LONG - CK1A - FK to tblTopics
Tag - Text - CK1B
In order to include tag based searching of the knowledge base.
Jim.
As a start:
tblTopics - One record per topic
TopicID - AN - PK
Title - Text - CK1 - Brief title
Synopsis - Text - Overall description of the topic
Knowledge - Text
now:
tblTopicStructure - One record per Topic/SubTopic pair
TopicStructureID - AN - PK
TopicID - LONG - CK1A - FK to tblTopics
SubTopicID - LONG - CK1B - FK to tblTopics
With this, you can have any number of subtopics for a given level, any number of levels to a topic structure, with knowledge at each level (or none), and topics can be shared between structures.
For example, the same topic "poisonous frogs" might appear under a topic of "poisons" and "frogs"
You might also want to add:
tblTopicTags - One record per topic/tag
TopicTagID - AN - PK
TopicID - LONG - CK1A - FK to tblTopics
Tag - Text - CK1B
In order to include tag based searching of the knowledge base.
Jim.
Your solution is a very good one. Additionally I would add the attribute "level" (LONG) to the tblTopicStructure that will help for some reports. I would also set the primary key to TopicID, SubTopicID and delete the key TopicStructureID. You give me a very good idea for implementing this kind of structures similar to BOM structure.
I this particular problem--I'm sorry I was not clear--, one element of level N is associated with 1 and only 1 element of level N-1 (is the real case I'm solving). So, reading a bit about normalization, option 1 is at least in 2NF, while option 2 is not.
Considering this restriction (one element of level N is associated with 1 and only 1 element N-1 level), your solution with my little modifications will be also in 2NF.
I this particular problem--I'm sorry I was not clear--, one element of level N is associated with 1 and only 1 element of level N-1 (is the real case I'm solving). So, reading a bit about normalization, option 1 is at least in 2NF, while option 2 is not.
Considering this restriction (one element of level N is associated with 1 and only 1 element N-1 level), your solution with my little modifications will be also in 2NF.
Active today
<<Additionally I would add the attribute "level" (LONG) to the tblTopicStructure that will help for some reports.>>
You generate that on the fly.
<< I would also set the primary key to TopicID, SubTopicID and delete the key TopicStructureID. >>
That depends on if your a die hard meaningless key person or not. Personally, I'd agree with you, but many would not.
One thing you might want to add there is a display order field (or sequence number), so subtopics are given in a specific order. That would be another candidate key.
Jim.
You generate that on the fly.
<< I would also set the primary key to TopicID, SubTopicID and delete the key TopicStructureID. >>
That depends on if your a die hard meaningless key person or not. Personally, I'd agree with you, but many would not.
One thing you might want to add there is a display order field (or sequence number), so subtopics are given in a specific order. That would be another candidate key.
Jim.
<< That depends on if your a die hard meaningless key person or not. Personally, I'd agree with you, but many would not >>
To close this topic, please give me a few word about normalization in this case. From my point of view, setting the primary key to TopicID, SubTopicID and deleting the key TopicStructureID, it's not just a pleasure but an issue about normalization. Setting this composed key for tblTopicStructure really models this table as a many-to-many self-relationship of tblTopics. Now, a N to M relationship in the ER model resolve to a weak relationship (entity) with no own keys; in my opinion, this implementation will assure the integrity of the database.
To close this topic, please give me a few word about normalization in this case. From my point of view, setting the primary key to TopicID, SubTopicID and deleting the key TopicStructureID, it's not just a pleasure but an issue about normalization. Setting this composed key for tblTopicStructure really models this table as a many-to-many self-relationship of tblTopics. Now, a N to M relationship in the ER model resolve to a weak relationship (entity) with no own keys; in my opinion, this implementation will assure the integrity of the database.
Active today
<<From my point of view, setting the primary key to TopicID, SubTopicID and deleting the key TopicStructureID, it's not just a pleasure but an issue about normalization.>>
It is and properly designed, TopicStructureID is not needed. But the whole reason meaningless keys are used is because when you apply a relational design to a computer system, performance can become an issue.
Indexes would be larger for example and you'd have less keys per page read, so it would be less efficient.
Jim.
It is and properly designed, TopicStructureID is not needed. But the whole reason meaningless keys are used is because when you apply a relational design to a computer system, performance can become an issue.
Indexes would be larger for example and you'd have less keys per page read, so it would be less efficient.
Jim.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
- Document Management
- Document Imaging
- Programming
- Adobe Acrobat
- Scripting Languages
- Printers and Scanners, *Scripts, *batch, *PDF
Suggested Courses
Course of the Month13 days, 19 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.