Data modelling question

Using SQL 7. I have a table with 2 fields in it that I wish to relate
to a lookup table. DDL for table 1:

CREATE TABLE [dbo].[tblPedometerReadings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReading] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity1Minutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity2Minutes] [smallint] NULL
) ON [PRIMARY]

DDL for table 2:

CREATE TABLE [dbo].[tlkpOtherActivities] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[EquivalentSteps] [smallint] NOT NULL
) ON [PRIMARY]

In my database diagram, I want to create a 1-to-many relationship
between tblPedometerReadings.OtherActivity1 and
tlkpOtherActivities.OtherActivity AND between
tblPedometerReadings.OtherActivity2 and
tlkpOtherActivities.OtherActivitiy. I get a long error message when
attempting the relationship between OtherActivity2 and OtherActivity.
Is there another schema that would work better?

Thanks.(manning_news@.hotmail.com) writes:
> Using SQL 7. I have a table with 2 fields in it that I wish to relate
> to a lookup table. DDL for table 1:
>
> CREATE TABLE [dbo].[tblPedometerReadings] (
> [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReadingDate] [datetime] NULL ,
> [PedometerReading] [smallint] NULL ,
> [OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity1Minutes] [smallint] NULL ,
> [OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity2Minutes] [smallint] NULL
> ) ON [PRIMARY]
> DDL for table 2:
> CREATE TABLE [dbo].[tlkpOtherActivities] (
> [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [EquivalentSteps] [smallint] NOT NULL
> ) ON [PRIMARY]

Which version of SQL Server are you really using? The above scripts
does not work in SQL7 - I can tell that from the use of COLLATE
clauses.

> In my database diagram, I want to create a 1-to-many relationship
> between tblPedometerReadings.OtherActivity1 and
> tlkpOtherActivities.OtherActivity AND between
> tblPedometerReadings.OtherActivity2 and
> tlkpOtherActivities.OtherActivitiy. I get a long error message when
> attempting the relationship between OtherActivity2 and OtherActivity.
> Is there another schema that would work better?

Designwise it sounds OK (save that varchar(50) is a tad long for a
key value). If I understand you right, you get this error message
when using the diagram functionality in Enterprise Manager? I don't
use that tool, but it would somewhat easier to say anything useful,
if you could post the error message.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Using SQL2000.

Here's the error message:

- Unable to create relationship
'FK_tblPedometerReadings_tlkpOtherActivities1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
on table 'tblPedometerReadings' may cause cycles or multiple cascade
paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
other FOREIGN KEY constraints.

OtherActivity can be "Biking-Hard", "Swimming-Easy", things like
that... If the customer doesn't make it clear what data will be
entered, then I leave it at varchar(50) until I get a better idea. I'm
toying with the idea of assigning an ID # to the activity and making
that the key.

Is a reflexive relationship what I need here?

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > Using SQL 7. I have a table with 2 fields in it that I wish to
relate
> > to a lookup table. DDL for table 1:
> > CREATE TABLE [dbo].[tblPedometerReadings] (
> > [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ReadingDate] [datetime] NULL ,
> > [PedometerReading] [smallint] NULL ,
> > [OtherActivity1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity1Minutes] [smallint] NULL ,
> > [OtherActivity2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity2Minutes] [smallint] NULL
> > ) ON [PRIMARY]
> > DDL for table 2:
> > CREATE TABLE [dbo].[tlkpOtherActivities] (
> > [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [EquivalentSteps] [smallint] NOT NULL
> > ) ON [PRIMARY]
> Which version of SQL Server are you really using? The above scripts
> does not work in SQL7 - I can tell that from the use of COLLATE
> clauses.
> > In my database diagram, I want to create a 1-to-many relationship
> > between tblPedometerReadings.OtherActivity1 and
> > tlkpOtherActivities.OtherActivity AND between
> > tblPedometerReadings.OtherActivity2 and
> > tlkpOtherActivities.OtherActivitiy. I get a long error message
when
> > attempting the relationship between OtherActivity2 and
OtherActivity.
> > Is there another schema that would work better?
> Designwise it sounds OK (save that varchar(50) is a tad long for a
> key value). If I understand you right, you get this error message
> when using the diagram functionality in Enterprise Manager? I don't
> use that tool, but it would somewhat easier to say anything useful,
> if you could post the error message.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(manning_news@.hotmail.com) writes:
> Using SQL2000.
> Here's the error message:
> - Unable to create relationship
> 'FK_tblPedometerReadings_tlkpOtherActivities1'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
> on table 'tblPedometerReadings' may cause cycles or multiple cascade
> paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
> other FOREIGN KEY constraints.

You never said that you had chosen cascading delete/update for the
relationship. You will be able to define the constraint, if you select
NO ACTION. (Which means that an attempt to delete a referenced row
in tlkpOtherActivities will result in an error.)

While SQL 2000 supports DELETE/UPDATE ON CASCADE on foreign keys,
there are many restrictions. In some cases they are necessary, in
some cases the SQL Server developers were a bit on the conservative
side when they disallow cascading.

I don't use cascading DRI myself, so I have not dug into the details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp