Creating a Consulting Database

Hey all,

I am taking a class on database concepts and am tasked with creating a database for a fictitious customer that runs a consulting business to track their consultants. has to have at least 3 entities.

I have developed the following and am looking for suggestion or ideas to improve this.

database to track their consulting staff, each staff member’s skill sets, and what projects they are working on. One employee can have many skill sets. One project can have many employees on it.


CONSULTANTS
EmpNo – PK
FirstName – data type Text
LastName – data type Text
Skill_ID – data type number
Client_ID – data type number
Project_ID – data type number

SKILLS
Skills_ID – data type autonumber
NetworkDesign – data type Text


NetworkInstallation – data type Text
DatabaseDesign – data type Text
ApplicationSupport – data type Text
ApplicationProgramming – data type Text
WebsiteDevelopment – data type Text
WebsiteMaintenance – data type Text
WebsiteHosting – data type Text

Projects
Project_ID – data type auto number
ProjectName – data type text
ProjectDetails – data type memo
Clients
Client_ID
ClientName – data type Text
ClientContact – data type Text
ClientAddress – data type Text
ClientCity – data type Text
ClientState – data type Text
ClientZip – data type Text

Your Skills table looks like "Committing to Spreadsheet". Where you are creating a new column for each skill type you should, instead, create a new row/record. Two or three columns should suffice for your "Lookup" TABLE named tblSkills. Maybe

Skills_ID
SkillDesc
SkillCode

Where skill code would be an acronym or shortened version of the full description.

I am guessing you propose to have a table Clients separate from table Projects.

When I first start to create a DB, I try to isolate a function within the business rules that I can build a DB for. In other words, I would not start by creating a DB for a business. I will start by creating a DB for one aspect or operation of the business. I will take the time to understand the business and workflow. I will identify functions within that can operate, for the most part, autonomously. I will then plan on paper, how these different and autonomous (yet the will share common denominators) aspects of the business relate to each other.

With that, I will start with the simplest aspect of operations. I will create a DB for that. I will identify tables that will be common to all aspects of operations and include these tables in my first, most simple DB. I will strive to make this first DB simple, not complicated, and no frills. All the time, I am keeping in mind the other aspects of operations. I want to make sure I do not ostracize them while creating this first, most simple DB. Although these are separate DB’s they will, in the end, share commonalities.

Consider the following which is a slight modification of your description:

The ABC Consulting Company requires a database to track various aspects of their consulting staff. Each staff member can have many skills. Each Employee can be working on 0 or many Projects. Projects are initiated by Clients. ABC wants to keep track of Employee skill sets, and what projects they are working on. One employee can have many skill sets. One project can have many employees assigned to it. A Client can initiate 1 or many Projects. Staff member/consultant and employee are synonymous terms.

The bolded nouns represent the "things/entities" involved. Each of these things will typically be the subject of a table.

So the initial tables would be

tblEmployee
tblSkill
tblClient
tblProject

and the rules

An Employee can have 1 or many Skills
An Employee can work on 0 or many Projects
A Project is initiated by a Client
A Client can initiate 1 or many Project.
A Project can involve 1 or many Employees.

In your
CONSULTANTS
EmpNo – PK
FirstName – data type Text
LastName – data type Text
Skill_ID – data type number
Client_ID – data type number
Project_ID – data type number

your structure implies that an EmpNo has 1 Skill and works on 1 Project.
I don't think there is an explanation for ClientID to be in the Consultants table.

The structure of the Skills table is incorrect in my view. I would suggest this structure

tblSkill
SkillId PK
SkillName

with records such as

1, NetworkDesign
2, NetworkInstallation
..
7, WebsiteMaintenance

Here's a tutorial that will help you - you'll have to work through it, but it does come with a solution.

Good luck with your project.

Ok,

Orange, first let me say thank you for your description.

I have decided to not use the client table because that was not part of the assignment. I was only going to add it because I think that in real life scenario you would want to also have your customer/clients in your database as well, but because it was not in the assignment I will leave it out.

I concede that you are correct here where the Skills table is concerned. However, I guess I am confused on how there can bee one employee with multiple skills in one record?

I still feel like this my employee table only allows for one Skill Id. so I am confused on how to achieve the result that I need. If I pull an sql query to say give my one record for employee X, then I think that my current design only gives me an employee with one skill when that employee X might for instance have three skills.

What am I missing?

You are correct. Your structure allows 1 Employee to have 1 Skill.
In plain English An Employee can have 1 or many Skills
and 1 Skill could be possessed by Many Employees.

To resolve this you need another table (Often termed a Linking or Junction table)
see this for more info

tblEmployeeHasSkill
EmpHasSkillID PK
EmployeeID FK to tblEmloyee
SkillId FK to tblSkill
..other fields <---------------if you have other info for this skill/employee combo ( perhaps test/course completiondate...)

You then make a Unique Composite Index of the EmployeeID and SkillID to prevent duplicates in this table.

Now, Employee 1 could have skills 1, 3,7,8 in records such as

1,1,1<---Rec1 Employee 1, Skill 1
2,1,3<---Rec2 Employee 1, Skill 3
3,1,7
4,1,8

Did you look at the tutorial I suggested previously?

Thank you all for your responses to my question.
I did review that tutorial that you had suggested, and it is good stuff.

I now have a better understanding. I have in fact added the junction table as pointed out.