I'm working with a Django system and the thing is pretty simple actually, but one thing got me bothered and I thought that must be a better way to do it.
Basically it's an internal tool for searching the developers the company has available to allocate them in projects based on their skills and interests.
So let's say Employee John can add a Topic React to his list of competence and select his skills levels from 1 to 5 stars, meaning 1 = total beginner and 5 = expert. He can also select his level of interest from 1 to 5. So you can have a list like below
The way this is designed at DB is like this:
Table user with all relevant user information Table knowledge_topic with columns [id, label], where label is just the name of the language/tech Table knowledge_level with columns[id, knowledge_level, interest_level, topic_id, user_id] where topic_id makes the relationship to knowledge_topic table and user_id to user table
The competence list endpoint can give a result like this for example:
[
{
"id": 2,
"topic": {
"id": 8,
"label": "Docker"
},
"interest_level": 4,
"knowledge_level": 2,
"user": 2
},
{
"id": 5,
"topic": {
"id": 9,
"label": "Flask"
},
"interest_level": 1,
"knowledge_level": 2,
"user": 2
},
{
"id": 1,
"topic": {
"id": 1,
"label": "React"
},
"interest_level": 4,
"knowledge_level": 4,
"user": 2
},
{
"id": 16,
"topic": {
"id": 3,
"label": "SCSS"
},
"interest_level": 2,
"knowledge_level": 1,
"user": 1
},
{
"id": 136,
"topic": {
"id": 2,
"label": "Django"
},
"interest_level": 1,
"knowledge_level": 1,
"user": 3
},
{
"id": 137,
"topic": {
"id": 9,
"label": "Flask"
},
"interest_level": 1,
"knowledge_level": 1,
"user": 3
},
{
"id": 138,
"topic": {
"id": 3,
"label": "SCSS"
},
"interest_level": 1,
"knowledge_level": 1,
"user": 3
}
]
As you can see, the "uniqueness" here is tied to the combination of user + knowledge. So every time I have an user add a new competence, I'll add an extra row to knowledge_competence table, which to me feels unnecessary.
Is there a better way of doing this or am I overthinking it? I was thinking perhaps creating a table interest with fields [id, level] and competence also with [id, level] and perhaps a "label" field in both tables just to have a text description what the levels mean. Then I can make a relationship chain of user -> topic -> competence and user -> topic -> interest. That way both tables have always 5 rows (the levels of competence and interest), topics always restricted to the universe of languages all employees have in that company and users to all employees. There'll be barely any data to add in DB, only when there is really a new employee or topic. Would this be a good design?
