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
> >
> >
>
>

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

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 tblT
asks
ProjectId StageId MilestoneId taskId
ProjectId StageId MilestoneId
…
…
schema 2
tblProjects tblStages tblMilestones tblT
asks
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...
> 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 DELE
TE
> CASCADE ON UPDATE CASCADE,
> .....
> ......
> )
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
>
>

Defining the Scope of SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.