Particular name for the type of structure of this database

database-designterminology

I'm trying to set up a data structure for an internal database of skills within a company, but I'm not entirely sure what "model" this fits as I'm quite new to databases and data structuring in general.

I've made an example of what I'm trying to do in excel:

enter image description here

Basically I'd like to have entries (users, in this case) which can exist in several different branches and are recognized as the same user, but have variables distinct for each branch in which they exist. Specifically I require the option to then sort by entry and see in which branches they exist, along with that branch' specific values for that user.

I think what this boils down to is a network structure, but again, I'm really quite new to this. If anything I would love some keywords for me to start googling with, because as of right now I can't see the forest for the trees

Best Answer

I don't know if there is a name for the structure that you are displaying in Excel, but the reporting layer rarely maps to the best way to store the actual data in a relational manner.

This link discusses database normalization https://en.wikipedia.org/wiki/Database_normalization

Given your requirements above, I would use three tables, one for people, one for skills and another to map them together. The decision about where to store a skill level (for example, Spanish 4) is up to you. You could either make a seperate skill called "Spanish 4" or store the level information on the PersonSkill table in the example below.

Your reporting layer can handle displaying the data, even pivoting it based on skill name, or do whatever else you need to do with it. Example below is for MSSQL specifically but other RDBMS would be similar. 

/** CREATE TABLES */
CREATE TABLE dbo.Person
    (
    PersonID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Skill
    (
    SkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , SkillName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PersonSkill 
    (
    PersonSkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person (PersonID)
    , SkillID INT NOT NULL REFERENCES dbo.Skill (SkillID)
    )

/** GET SOME DATA **/
INSERT INTO dbo.Person
(PersonName)
VALUES ('John Smith') --1
    , ('Jane Doe') --2

INSERT INTO dbo.Skill
(SkillName)
VALUES ('Coding') --1
    , ('English') --2
    , ('Spanish') --3

INSERT INTO dbo.PersonSkill
(PersonID, SkillID)
VALUES (1,1)
    , (2,1)
    , (2,3)

/** QUERIES */
--What Skills does John have?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE P.PersonName = 'John Smith'

--Who can speak Spanish?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE S.SkillName = 'Spanish'
Related Question