Data modelling question

[manning_news]

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.

[Erland Sommarskog]

(ma**********@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, es****@sommarskog.se

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

[manning_news]

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:

(ma**********@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, es****@sommarskog.se

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

[Erland Sommarskog]

(ma**********@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, es****@sommarskog.se

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