Web Development

Ask something
Question Locked

Multiple Tables

0 helpful
4 replies

by brysounds

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am just learning sql, and I need some help. This may seem basic to many but I don't know where to begin. Below I have a single table that I need to make into at least three tables.

CREATE TABLE [dbo].[SetFocus](
[EmployeeNumber] [int] NOT NULL,
[EmployeeName] [varchar](50) NOT NULL,
[Project1] [varchar](50) NULL,
[Project2] [varchar](50) NULL,
[Project3] [varchar](50) NULL,
[Team] [varchar](50) NULL,
[Hours1] [int] NULL,
[Hours2] [int] NULL,
[Hours3] [int] NULL,
[ProjectManager1] [int] NULL,
[ProjectManager2] [int] NULL,
[ProjectManager3] [int] NULL,
------------------------------------------------

1. How do I decide to brake up the data.
2. I was able to break up the data, but I am not able to get the results that I am looking for.

Please help

4 replies

by tony hopkinson

in terms of normalisation

Employees
EmployeeNumber
EmployeeName

Teams
TeamID
TeamName
TeamLeaderID

EmployeeTeams
TeamID
EmployeeID

Projects
ProjectID
ProjectName
ProjectManagerID

ProjectHours (SetFocus ????)
ProjectID
TeamID
Hours

might be one way.

What results are you looking for might give us more of a clue though!

by brysounds

I am looking to get back some of the following results.
1. Name 1 person who worked on a particular project.
2. The Total amount of hours on a project.

The thing that keeps getting me is that when i try to do things I get the entire column. Not what I am looking for

by tony hopkinson

problem

Your initial table would make that sort of query onerous top say the least, so breaking it up is the first thing you have to do.

Using the design I suggested as a possibility, fair few assumptions in there.

Select ProjectID, Sum(Hours) from ProjectHours
Group By ProjectID

If you wanted to beef that up with a bit more info as ProjectID could be fairly useless.

Select ProjectName,ProjectManager, Sum(Hours) as TotalHours
From Projects, ProjectHours
Where Projects.ProjectID = ProjectHours.ProjectID
Group By ProjectName,ProjectManager

1 Person who worked on project

Select Top 1 EmployeeName
From Employees,Projects,Teams, ProjectTeams
Where ProjectID = ?
and ProjectsTeams.ProjectID = Projects.ProjectID
and Teams.TeamID = Projects.TeamID
and Teams.EmployeeID = Employees.EmployeeID

Would be a way of doing it.

HtHs

This discussion is closed to new replies.