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
tblProjectstblStagestblMilestonestblTasks
ProjectIdStageIdMilestoneIdtaskId
ProjectIdStageIdMilestoneId
…
…
schema 2
tblProjectstblStagestblMilestonestblTasks
ProjectIdProjectIdProjectId ProjectId
StageIdStageIdStageId
MilestoneIdMilestoneId
TakskId
I am just wondering which shema would be more appropriate.
Thanks a million
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
|||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...
> milestones
>
|||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...
>
>
sql