Design Question
-
I am working on a Project Management application, and I have two data design issues I am debating.
The key element of this app, as you might expect, is Project. The project will proceed through many phases, from Planning, to Pre-Design, Design, Bid, Construction and Post Construction. All along the way there are a number of discrete tasks that must be performed and tracked.
I bounce back and forth in my mind between a single Project table that encapsulates all of these tasks, but am hesitant because I'm not a big fan of large monolithic tables. Alternatively, I could logically create separate tables for the various phases. However, this would create a series of one-to-one relationships between Project and the Phase tables, and require extra joins. The performance hit would probably not be too bad, but I would need to add extra code in either the app code or stored procedures to create an empty record in each of the phase tables when a new project is added. (Obviously, projects in planning or design will not have active records in the Construction and Post Construction tables). What are your thoughts about these choices?
Secondly, I have to manage data for a lot of individuals, which basically break down into two groups. First are internal employees who will have tasks routed to them, be invited to meetings, etc. Second are external vendors, basically contractors and consultants, who will be performing work and also be invited to meetings and such. I need to track the participants in meetings and inspections, so will have a Meeting Participant table to capture the many-to-many relationship. My issue is structuring the handling of the people. One option is to have a Person table, which basically includes everybody, with a flag field for internal or external people, and categories for their roles. Second is a table for internal folks and a table for external, or separate tables for Consultants, Contractors, and internal Employees. However, this makes capturing the meeting participants more cumbersome.
What is the collective wisdom on these? Thanks!
Jeff Little
-
For your project table why not have a PROJECT table and a TASK table. This way WHEN the PROJECT moves through the various PHASES you could have an UPDATE/INSERT trigger or SP that adds the appropriate records to the TASK table.
For the PERSON table WHY have different tables??? Why not have a COLUMN that indicates INTERNAL/EXTERNAL?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
OK, I'm trying to picture how a task table would work. Assume all projects have to complete Task A, Task B, Task C. Wouldn't I need a Project Task table that covers the many-many relationship (i.e. a Project will have many tasks, and a given task will be performed in many projects). Thus, the Project Task table would have fields for Task ID, Project ID, Completed, Date Completed.
Since we need to display tasks, whether or not they have been completed, by necessity there would have to be a record for every task for each project in the Project Task table.
Obviously, this setup would have the advantage of being able to add tasks without disrupting the overall data model, but would create some additional join issues. I'll have to mull this one over.
I am inclined to agree with you on the Person table -- that is my preference as well.
Thanks for the input.
Jeff
-
I think AJ was implying that the task table would contain the the many to many relationship inherently: the key being projectid, taskid.
It is not clear if you are intending tasks to be fixed (e.g. there are always 6 phases: design, construction, etc. ) or user defined (e.g. project 1, task 1: weed the garden, etc.)
-
actually I was just thinking "Hey, let's see if the balloon flies.... THUD", hmm lead not good to make balloon out of CHECK....
Seriously, I just threw the IDEA of a task table out for consideration. The implementation is strictly up to the beholder. I do think that the TASK table should be just a pointer between the PROJECT table and TASK_ITEMS table and includes time assigned/completed, etc.... This way you can add tasks as needed and print to your hearts content
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply