CODEWITHSUNDEEP
mysqlsqldatabasedata-structuresdatabase-design
CatHalsey
CatHalsey

Reputation: 25

How to improve/maximize current Database/Table Structure efficiency?

The current structure of my Database tables are as follows; I have two tables, Users, and Skills, with the following columns:

Users

user_id   f_name   l_name    biography    password   email          role
_____________________________________________________________________
1         John     Program   Short Bio    Password   johnprogram@   Programmer
2         Jeff     Analyst   Another Bio  Password   jeffanalyst@   Finance Analyst
3         Mister   Manager   My Bio       Password   mrmanager@     Project Manager

Skills

user_id   skill_1   skill_1_details      skill_2             skill_2_details     skill_3     skill_3_details
_________________________________________________________________________________
1         PHP       John writes PHP      Python              John writes Python  Perl        John writes Perl
2         Excel     I use Excel daily    SAP                 SAP Reporting       Reporting   Reports created with Excel
3         Billing   Oversee Bill. Team   Workflow Assignment I assign team acc.

Whenever I am querying the data in the tables to display on a Profile Page, the info is displayed as follows:

Name: John Program
Role: Programmer
Short Bio

Skill: PHP
Details: John writes PHP

Skill: Python
Details: John writes Python

Skill: Perl
Details: John writes Perl

Questions:

1) What re-adjustments/changes should be made to the table structures to increase efficiency/flexibility given the display requirements?

2) If I were to add a "Previous Projects Using Skill" column, for efficiency, would I add it to the second table as follows?:

user_id   skill_1   skill_1_details      projects_using_skill_1
_________________________________________________________________________________
1         PHP       John writes PHP      8                         Python              John writes Python  Perl        John writes Perl
2         Excel     I use Excel daily    12                        SAP                 SAP Reporting       Reporting   Reports created with Excel
3         Billing   Oversee Bill. Team   3                         Workflow Assignment I assign team acc.

Upvotes: 1

Views: 135

Answers (1)

Renzo
Renzo

Reputation: 27414

For the skill - user relation, you can have two different solutions: if each user has its own skills and details, than you can use a skill table to represent them, with only one skill per user per row (so if a user has four skills, the table will have four rows):

Skills (user_id, skill, detail)

with primary key the couple user_id, skill.

In this way, to find all the skills for a certain user, you can join the two table and find all the relevant information.

If instead several users can have the same skills, you should use three tables:

Users (user_id, f_name, ...)
Skills (skill_id, skill_name, skill_detail)
UsersSkills (user_id, skill_id)

or, if you want that each user has its own details about a skill, but you want to share the skills, you could do something like the following:

Users (user_id, f_name, ...)
Skills (skill_id, skill_name)
UsersSkills (user_id, skill_id,  skill_detail)

For your second question, if a user has done several projects using a certain skill, assuming that you have selected the first solution above, you can define a new table:

ProjectUsers (user_id, skill, project_id)

with primary key all the three attributes, and with user_id, skill foreign key for the Skills table.

Upvotes: 1

Related Questions