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 |
|
KimJ
Starting Member
38 Posts |
 Posted - 2004-03-05 : 10:17:08
|
| Hi-I'm a little new to the design area and I'm not sure how to approach this. I'm working on an application that will track milestones & tasks for projects. I hope I can explain it clearly.A project will contain about 7 milestones, some of which have tasks associated to them, some won't. A milestone won't be complete until all associated tasks are complete, and, of course, a milestone will also have to be completed if there are no associated tasks. Each milestone/task will have a start and end date.I'm not sure how to set up the table structure to accomodate this. I planned on having a reference sort of table to have the milestone & task id's and descriptions, while the actual project will just have the id numbers. I will need the milestones & tasks created for each project as a project is entered. How would you recommend setting this up? I've only gotten as far as knowing I'll need a trigger on the primary project table, which will basically hold the project number and associated customer. I envision the trigger as looping through the reference tables and adding the milestones/steps to the customers project. At first this seemed simple enough, but once I started to design, I realized that I really don't know if I should have separate tables to accomodate milestones and tasks, or should I have a self-referencing table with a 'parent id' column and have the task related to the milestone with this column?Any suggestions? Thank you!KJ |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-05 : 18:43:58
|
| Are your milestones and tasks all pre-defined and the all appear on every project? Or are they custom defined?Here's the start of an idea, but your specific needs may change the setup:Sounds like a table for Projects, one for Milestones, and one for Tasks. The Tasks table has a Foreign Key reference to its parent Milestone. If everything is pre-defined, then you'll have a juncture table to join Projects to Milestones (many-to-many). If custom, then I assume a one-to-many between Project and Milestones, which means again a FK field in Milestones referencing its Project.I don't see a need for a trigger unless you have pre-defined milestones and want to auto-populate the juncture table whenever a new Project is created. In this case, the juncture table will probably also contain other data elements such as StartDate, CompletionDate, etc. In the One-to-Many, these fields would go in the Milestones table. Tasks have their own Start/End date fields too, probably.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-06 : 00:11:09
|
| I have another idea that might be worthwhile: how about treating a milestone as a special kind of Task? You could store it in the Tasks table and flag it as a milestone. As far as associating it with tasks, it would work with Mark's junction table exactly the same way, except that both columns would reference Tasks. There would be no need to have a separate Milestones table. You could also associate any task to any other; in effect, you can build a dependency chain that would let you track task completion before milestone completion. A single milestone with no dependent tasks can be stored in the junction table by associating it with itself. |
|
|
|
KimJ
Starting Member
38 Posts |
Posted - 2004-03-08 : 09:53:24
|
| Awesome - thank you so much for the help. The milestones and tasks are pre-defined and will be added to every project. I have never worked with junction tables before, so this is a good opportunity to learn something new (I've just been able to get out of the SQL server 6.5 arena). I will also create a trigger on the projects table to create entries in the junction table. Which leads me to my next question... would you recommend using a cursor to loop through the tables and add the rows to the junction table?So, conceptually speaking, on project creation - get each row from the milestone table, and check to see if there are tasks to go with that milestone - insert each row into the junction table.Thanks again! |
|
|
|
The Enigma
Posting Yak Master
179 Posts |
Posted - 2004-03-08 : 10:00:33
|
quote:
Originally posted by KimJ
Awesome - thank you so much for the help. Which leads me to my next question... would you recommend using a cursor to loop through the tables and add the rows to the junction table?
Thats a bad question ... nobody here would recommend using a cursor to do what can be achieved using set based queries Enigma |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-08 : 10:15:27
|
| Junction Tables are just a logical construct the define a business rule.Sounds like you need a 1 time thing to populate this new rule.Just do a select between the 2 tables and insert the value to the new tableINSERT INTO junctionTable (Col list)SELECT Col listFROM Table1 INNER JOIN Table2 ON whateverAnd there's no need for a trigger...I would do it in the sproc that generates the parental rows....Brett8-) |
|
|
|
KimJ
Starting Member
38 Posts |
Posted - 2004-03-08 : 10:18:50
|
| Yes, after I wrote that I realized I can just do a select statement. |
|
|
|
|
|
|
|
|
Need help with design...