Tasks, Assignments and Projects Database Structure.

G

Guest

You will find a strucutre for project management below in regards to real
estate.

I have purposely not included tables such as Employees Buildings etc. Here
is just the project mangement aspect. Please note that I am really not
certain yet about the relationship types and the referential integrity rules
and welcome any and all suggestions. Database, Visio Diagram and Document
available for the special members aho would like to assist etc. Thanks in
advance to all

Table: tblUnitAssignmentCategories Page: 1
Columns
Name Type Size
AssignmentCategoryID Long Integer 4
AssignmentCategoryName Text 50
AssignmentCategoryDescription Text 255

Relationships

tblUnitAssignmentCategoriestblUnitAssignmentSubCategories
tblUnitAssignmentCat tblUnitAssignmentSub
AssignmentCategoryID AssignmentCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitAssignmentCategoriestblUnitAssignments
tblUnitAssignmentCat tblUnitAssignments
AssignmentCategoryID AssignmentCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitAssignments Page: 2
Columns
Name Type Size
AssignmentID Long Integer 4
AssignmentName Text 50
SourceTaskDescription Long Integer 4
AssignmentDescription Memo -
AssignmentInstructions Memo -
AssignmentCategoryID Long Integer 4
AssignmentSubCategoryID Long Integer 4
UnitID Long Integer 4
PropertyID Long Integer 4
ProjectID Long Integer 4
UnitLifeCycleStage Long Integer 4
PriorityLevel Long Integer 4
AssignmentStatus Long Integer 4
AssignmentExpenseTotal Currency 8
AssignmentCompleted Yes/No 1
AssignedToID Long Integer 4
AssignedByID Long Integer 4
ProjectManagerID Long Integer 4
AssignedDate Date/Time 8
StartDate Date/Time 8
DueDate Date/Time 8
CompletedDate Date/Time 8
AssignmentNotes Memo -

Relationships

tblUnitAssignmentCategoriestblUnitAssignments
tblUnitAssignmentCat tblUnitAssignments
AssignmentCategoryID AssignmentCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitAssignmentSubCategoriestblUnitAssignments
tblUnitAssignmentSub tblUnitAssignments
AssignmentSubCategoryI AssignmentSubCategoryI
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitAssignments Page: 3
tblUnitstblUnitAssignments
tblUnits tblUnitAssignments
UnitID UnitID
Attributes: Not Enforced
RelationshipType: Indeterminate

tblUnitProjectstblUnitAssignments
tblUnitProjects tblUnitAssignments
ProjectID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitProjectsStatustblUnitAssignments
tblUnitProjectsStatus tblUnitAssignments
StatusID AssignmentStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskstblUnitAssignments
tblUnitTasks tblUnitAssignments
TaskID SourceTaskDescription
Attributes: Not Enforced
RelationshipType: One-To-Many

tblPriorityLevelstblUnitAssignments
tblPriorityLevels tblUnitAssignments
PriorityLevelID PriorityLevel
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitAssignmentStatus Page: 4
Columns
Name Type Size
StatusID Long Integer 4
StatusName Text 50
StatusDescription Text 255

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitAssignmentSubCategories Page: 5
Columns
Name Type Size
AssignmentSubCategoryID Long Integer 4
AssignmentCategoryID Long Integer 4
AssignmentSubCategoryName Text 50
AssignmentSubCategoryDescription Memo -

Relationships

tblUnitAssignmentCategoriestblUnitAssignmentSubCategories
tblUnitAssignmentCat tblUnitAssignmentSub
AssignmentCategoryID AssignmentCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitAssignmentSubCategoriestblUnitAssignments
tblUnitAssignmentSub tblUnitAssignments
AssignmentSubCategoryI AssignmentSubCategoryI
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitAssignmentTemplates Page: 6
Columns
Name Type Size
AssignmentID Long Integer 4
AssignmentName Text 50
AssignmentDescription Memo -
AssignmentInstructions Memo -
AssignmentCategoryID Long Integer 4
AssignmentSubCategoryID Long Integer 4
UnitID Long Integer 4
ProjectID Long Integer 4
Assignment Status Long Integer 4
AssignmentExpenseTotal Currency 8
AssignmentCompleted Yes/No 1
AssignedToID Long Integer 4
AssignedByID Long Integer 4
ProjectManagerID Long Integer 4
AssignedDate Date/Time 8
StartDate Date/Time 8
DueDate Date/Time 8
CompletedDate Date/Time 8
SourceTaskID Long Integer 4
AssignmentNotes Memo -

Relationships

tblUnitProjectTemplatestblUnitAssignmentTemplates
tblUnitProjectTemplat tblUnitAssignmentTe
ProjectTemplateID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskTemplatestblUnitAssignmentTemplates
tblUnitTaskTemplates tblUnitAssignmentTe
TaskID SourceTaskID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitProjects Page: 7
Columns
Name Type Size
ProjectID Long Integer 4
ProjectName Text 50
UnitLifeCycleStage Long Integer 4
ProjectDescription Memo -
ProjectInstructions Memo -
UnitID Long Integer 4
ProjectTotalCostEstimate Currency 8
AssignedToID Long Integer 4
AssignedByID Long Integer 4
ProjectManagerID Long Integer 4
ProjectAssignedDate Date/Time 8
ProjectStartDate Date/Time 8
ProjectDueDate Date/Time 8
ProjectCompletedDate Date/Time 8
ProjectStatus Long Integer 4
Relationships
tblUnitProjectstblUnitTasks
tblUnitProjects tblUnitTasks
ProjectID ProjectID
Attributes: Not Enforced

RelationshipType: One-To-Many
tblUnitProjectstblUnitAssignments
tblUnitProjects tblUnitAssignments
ProjectID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitProjectstblUnitTaskList
tblUnitProjects tblUnitTaskList
ProjectID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitProjectsStatus Page: 8
Columns
Name Type Size
StatusID Long Integer 4
StatusName Text 50
StatusDescription Text 255
Relationships

tblUnitProjectsStatustblUnitProjectTemplates
tblUnitProjectsStatus tblUnitProjectTemplat
StatusID ProjectStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitProjectsStatustblUnitAssignments
tblUnitProjectsStatus tblUnitAssignments
StatusID AssignmentStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitProjectTemplates Page: 9
Columns
Name Type Size
ProjectTemplateID Long Integer 4
ProjectTemplateName Text 50
UnitLifeCycleStage Long Integer 4
ProjectDescription Memo -
ProjectInstructions Memo -
UnitID Long Integer 4
ProjectTotalCostEstimate Currency 8
AssignedToID Long Integer 4
AssignedByID Long Integer 4
ProjectManagerID Long Integer 4
ProjectAssignedDate Date/Time 8
ProjectStartDate Date/Time 8
ProjectDueDate Date/Time 8
ProjectCompletedDate Date/Time 8
ProjectStatus Long Integer 4

Relationships

tblUnitProjectsStatustblUnitProjectTemplates
tblUnitProjectsStatus tblUnitProjectTemplat
StatusID ProjectStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitProjectTemplatestblUnitAssignmentTemplates
tblUnitProjectTemplat tblUnitAssignmentTe
ProjectTemplateID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitLifecyclestblUnitProjectTemplates
tblUnitLifecycles tblUnitProjectTemplat
UnitLifecycleID UnitLifeCycleStage
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitProjectTemplates Page: 10

tblUnitstblUnitProjectTemplates
tblUnits tblUnitProjectTemplat
UnitID UnitID
Attributes: Not Enforced
RelationshipType: Indeterminate

tblEmployeestblUnitProjectTemplates
tblEmployees tblUnitProjectTemplat
EmployeeID AssignedToID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTaskCategories Page: 11
Columns
Name Type Size
TaskCategoryID Long Integer 4
TaskCategoryName Text 50
TaskCategoryDescription Text 255

Relationships

tblUnitTaskCategoriestblUnitTaskSubCategories
tblUnitTaskCategories tblUnitTaskSubCatego
TaskCategoryID TaskCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskCategoriestblUnitTasks
tblUnitTaskCategories tblUnitTasks
TaskCategoryID TaskCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTaskList Page: 12
Columns
Name Type Size
TaskListID Long Integer 4
TaskID Long Integer 4
ProjectID Long Integer 4

Relationships

tblUnitTaskstblUnitTaskList
tblUnitTasks tblUnitTaskList
TaskID TaskID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitProjectstblUnitTaskList
tblUnitProjects tblUnitTaskList
ProjectID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTasks Page: 13
Columns
Name Type Size
TaskID Long Integer 4
TaskName Text 50
PropertyID Long Integer 4
UnitID Long Integer 4
UnitLifeCycleStage Long Integer 4
ProjectID Long Integer 4
TaskDescription Memo -
TaskInstructions Memo -
TaskCategoryID Long Integer 4
TaskSubCategoryID Long Integer 4
PriorityLevelID Long Integer 4
TaskStatus Long Integer 4

Relationships

tblUnitProjectstblUnitTasks
tblUnitProjects tblUnitTasks
ProjectID ProjectID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitstblUnitTasks
tblUnits tblUnitTasks
UnitID UnitID
Attributes: Not Enforced
RelationshipType: Indeterminate

tblUnitTaskCategoriestblUnitTasks
tblUnitTaskCategories tblUnitTasks
TaskCategoryID TaskCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTasks Page: 14

tblUnitTaskSubCategoriestblUnitTasks
tblUnitTaskSubCatego tblUnitTasks
TaskSubCategoryID TaskSubCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTasksStatustblUnitTasks
tblUnitTasksStatus tblUnitTasks
StatusID TaskStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskstblUnitAssignments
tblUnitTasks tblUnitAssignments
TaskID SourceTaskDescription
Attributes: Not Enforced
RelationshipType: One-To-Many

tblPriorityLevelstblUnitTasks
tblPriorityLevels tblUnitTasks
PriorityLevelID PriorityLevelID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskstblUnitTaskList
tblUnitTasks tblUnitTaskList
TaskID TaskID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTasksStatus Page: 15
Columns
Name Type Size
StatusID Long Integer 4
StatusName Text 50
StatusDescription Text 255

Relationships

tblUnitTasksStatustblUnitTasks
tblUnitTasksStatus tblUnitTasks
StatusID TaskStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTasksStatustblUnitTaskTemplates
tblUnitTasksStatus tblUnitTaskTemplates
StatusID TaskStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTaskSubCategories Page: 16
Columns
Name Type Size
TaskSubCategoryID Long Integer 4
TaskCategoryID Long Integer 4
TaskSubCategoryName Text 50
TaskSubCategoryDescription Text 255

Relationships

tblUnitTaskCategoriestblUnitTaskSubCategories
tblUnitTaskCategories tblUnitTaskSubCatego
TaskCategoryID TaskCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskSubCategoriestblUnitTasks
tblUnitTaskSubCatego tblUnitTasks
TaskSubCategoryID TaskSubCategoryID
Attributes: Not Enforced
RelationshipType: One-To-Many

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 20, 2005
v2.mdb
Table: tblUnitTaskTemplates Page: 17
Columns
Name Type Size
TaskID Long Integer 4
TaskName Text 50
PropertyID Long Integer 4
UnitID Long Integer 4
UnitLifeCycleStage Long Integer 4
ProjectID Long Integer 4
TaskDescription Memo -
TaskInstructions Memo -
TaskCategoryID Long Integer 4
TaskSubCategoryID Long Integer 4
PriorityLevelID Long Integer 4
TaskStatus Long Integer 4
Relationships

tblUnitTasksStatustblUnitTaskTemplates
tblUnitTasksStatus tblUnitTaskTemplates
StatusID TaskStatus
Attributes: Not Enforced
RelationshipType: One-To-Many

tblUnitTaskTemplatestblUnitAssignmentTemplates
tblUnitTaskTemplates tblUnitAssignmentTe
TaskID SourceTaskID
Attributes: Not Enforced
RelationshipType: One-To-Many

tblPriorityLevelstblUnitTaskTemplates
tblPriorityLevels tblUnitTaskTemplates
PriorityLevelID PriorityLevelID
Attributes: Not Enforced
RelationshipType: One-To-Many
 
V

Vincent Johns

You have identified the structure of your database, but sample data
(with any sensitive personal information changed) would be helpful, too.
The name and structure of a field don't necessarily convey how you
intend to use it, or what it means in the real world.

If you wish to email me a copy of the database file (in ZIP format), you
may do so, but I may not get back to you before next week.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
T

TC

It's impossible to comment unless you clearly state the primary key of
each table. People can guess - but they should not have to do so.

HTH,
TC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Customer, Prospect, Service Table design 4
Only one record with yes/no 1
Direct x 9 gone 1
SVCHOST, DATA EXECUTION PREVENTION, GENERIC HOST PROCESS ERRORS 1
no bytes returned on raw socket;trying to implement ping 12
Unable to use FAXCOMLib with certain TIFF formats. 10
Ranking, subqueries and update query problem 11
Question For Mr. Ferguson 3

Top