Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-02-26 : 10:15:10
|
| Frank writes "Any suggestion on the best design to store data for resource management so that data can be displayed afterwards in something like a gantt chart.For example, you have to asign resources, say personID 1 to 100 to fill positions A, B, C, D, E and FA position must always be filled. A person can only fill one position at a time. You want to be able to detect overlaps when you do your planning.So if person 2 is in position B from 1 Jan to 15 March, he is currently unavailable, but he should be availabe in my planning for any position from 16 March onwards etc.Possible queries - 1) list all positions not filled during period XXXX to YYYY2) List all persons available to fill a certain postion during period XXXX to YYYY3) List any overlaps where a person is assigned to different postions during overlapping time frames..." |
|
|
gvphubli
Yak Posting Veteran
54 Posts |
Posted - 2008-01-30 : 23:32:24
|
| tblResourceDetails( ResourceID INT IDENTITY, ResourcefName VARCHAR(40), ResourceLName VARCHAR(40), ResouceSkill INT (Fk Skills Master table) ResourceAvailable BIT DEFAULT 'Y')tblProjects( ProjectID INT IDENTITY, ProjectManager INT (Fk tblResourceDetails.ResourceID), ProjectStartDt DATETIME, ProjectEndDt DATETIME)tblResourceAllocation( ProjectID INT (Fk tblProjects.ProjectID), ResouceId INT (Fk tblResourceDetails.ResourceID), ProjectStartDt DATETIME, ProjectEndDt DATETIME)This is the simple schema design I can think of.TechnologyYogihttp://gvphubli.blogspot.com/ |
 |
|
|
|
|
|
|
|
Best Table design for time based resource management