deisgn question

I have the following scenario in one of my applications.
Projects->Stages->Milestones->tasks
the above means a project has stages, stages have milestones and milestones
have tasks
I have found two possible structures in which I can design the tables.
tbl... refer to table.
schema 1
tblProjects tblStages tblMilestones tblTasks
ProjectId StageId MilestoneId taskId
ProjectId StageId MilestoneId
â?¦
â?¦
schema 2
tblProjects tblStages tblMilestones tblTasks
ProjectId ProjectId ProjectId ProjectId
StageId StageId StageId
MilestoneId MilestoneId
TakskId
I am just wondering which shema would be more appropriate.
Thanks a millionMathi
It is messy in your description
CREATE TABLE Projects
(
PrjId INT NOT NULL PRIMARY KEY,
.....
.....
)
CREATE TABLE Stages
(
StadeId INT NOT NULL PRIMARY KEY
PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
CREATE TABLE Milestones
(
Milestid INT NOT NULL PRIMARY KEY,
StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
"Mathi" <Mathi@.discussions.microsoft.com> wrote in message
news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> I have the following scenario in one of my applications.
>
> Projects->Stages->Milestones->tasks
> the above means a project has stages, stages have milestones and
milestones
> have tasks
> I have found two possible structures in which I can design the tables.
> tbl... refer to table.
> schema 1
> tblProjects tblStages tblMilestones tblTasks
> ProjectId StageId MilestoneId taskId
> ProjectId StageId MilestoneId
> ?
> ?
> schema 2
> tblProjects tblStages tblMilestones tblTasks
> ProjectId ProjectId ProjectId ProjectId
> StageId StageId StageId
> MilestoneId MilestoneId
> TakskId
>
> I am just wondering which shema would be more appropriate.
> Thanks a million|||Correction
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Mathi
> It is messy in your description
> CREATE TABLE Projects
> (
> PrjId INT NOT NULL PRIMARY KEY,
> .....
> .....
> )
> CREATE TABLE Stages
> (
> StadeId INT NOT NULL PRIMARY KEY
> PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Milestones
> (
> Milestid INT NOT NULL PRIMARY KEY,
> StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
>
> "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> > I have the following scenario in one of my applications.
> >
> >
> > Projects->Stages->Milestones->tasks
> > the above means a project has stages, stages have milestones and
> milestones
> > have tasks
> >
> > I have found two possible structures in which I can design the tables.
> > tbl... refer to table.
> >
> > schema 1
> >
> > tblProjects tblStages tblMilestones tblTasks
> > ProjectId StageId MilestoneId taskId
> > ProjectId StageId MilestoneId
> > ?
> > ?
> > schema 2
> >
> > tblProjects tblStages tblMilestones tblTasks
> > ProjectId ProjectId ProjectId ProjectId
> > StageId StageId StageId
> > MilestoneId MilestoneId
> > TakskId
> >
> >
> > I am just wondering which shema would be more appropriate.
> >
> > Thanks a million
>|||Thanks Uri
Basically my question was, which of the two schemas would be more
appropriate in terms of database design...
thanks
"Uri Dimant" wrote:
> Correction
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> ......
> )
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> > Mathi
> > It is messy in your description
> >
> > CREATE TABLE Projects
> > (
> > PrjId INT NOT NULL PRIMARY KEY,
> > .....
> > .....
> > )
> > CREATE TABLE Stages
> > (
> > StadeId INT NOT NULL PRIMARY KEY
> > PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> > CREATE TABLE Milestones
> > (
> > Milestid INT NOT NULL PRIMARY KEY,
> > StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> > CREATE TABLE Tasks
> > (
> > TaskId INT NOT NULL PRIMARY KEY,
> > Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> >
> >
> > "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> > news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> > > I have the following scenario in one of my applications.
> > >
> > >
> > > Projects->Stages->Milestones->tasks
> > > the above means a project has stages, stages have milestones and
> > milestones
> > > have tasks
> > >
> > > I have found two possible structures in which I can design the tables.
> > > tbl... refer to table.
> > >
> > > schema 1
> > >
> > > tblProjects tblStages tblMilestones tblTasks
> > > ProjectId StageId MilestoneId taskId
> > > ProjectId StageId MilestoneId
> > > â?¦
> > > â?¦
> > > schema 2
> > >
> > > tblProjects tblStages tblMilestones tblTasks
> > > ProjectId ProjectId ProjectId ProjectId
> > > StageId StageId StageId
> > > MilestoneId MilestoneId
> > > TakskId
> > >
> > >
> > > I am just wondering which shema would be more appropriate.
> > >
> > > Thanks a million
> >
> >
>
>