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].[tblPedometerRea dings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReadin g] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OtherActivity1M inutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OtherActivity2M inutes] [smallint] NULL
) ON [PRIMARY]

DDL for table 2:

CREATE TABLE [dbo].[tlkpOtherActivi ties] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_Gene ral_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 tblPedometerRea dings.OtherActi vity1 and
tlkpOtherActivi ties.OtherActiv ity AND between
tblPedometerRea dings.OtherActi vity2 and
tlkpOtherActivi ties.OtherActiv itiy. 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**********@h otmail.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].[tblPedometerRea dings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReadin g] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OtherActivity1M inutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OtherActivity2M inutes] [smallint] NULL
) ON [PRIMARY]

DDL for table 2:

CREATE TABLE [dbo].[tlkpOtherActivi ties] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_Gene ral_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 tblPedometerRea dings.OtherActi vity1 and
tlkpOtherActivi ties.OtherActiv ity AND between
tblPedometerRea dings.OtherActi vity2 and
tlkpOtherActivi ties.OtherActiv itiy. 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****@sommarsk og.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_tblPedomete rReadings_tlkpO therActivities1 '.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_tblPedomete rReadings_tlkpO therActivities1 '
on table 'tblPedometerRe adings' 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**********@h otmail.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].[tblPedometerRea dings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReadin g] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OtherActivity1M inutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OtherActivity2M inutes] [smallint] NULL
) ON [PRIMARY]

DDL for table 2:

CREATE TABLE [dbo].[tlkpOtherActivi ties] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_Gene ral_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 tblPedometerRea dings.OtherActi vity1 and
tlkpOtherActivi ties.OtherActiv ity AND between
tblPedometerRea dings.OtherActi vity2 and
tlkpOtherActivi ties.OtherActiv itiy. 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****@sommarsk og.se

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

[Erland Sommarskog]

(ma**********@h otmail.com) writes:

Using SQL2000.

Here's the error message:

- Unable to create relationship
'FK_tblPedomete rReadings_tlkpO therActivities1 '.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_tblPedomete rReadings_tlkpO therActivities1 '
on table 'tblPedometerRe adings' 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 tlkpOtherActivi ties 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****@sommarsk og.se

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