Please correct me if I am wrong. 
Author Message
 Please correct me if I am wrong.

Please correct me if I am wrong.
In this example there are two tables, clients, and clientInfo_editHistory.
The clients table holds all clients who have registered from the web site
and all clients who have been bulked loaded into the system.
The clientInfo_editHistory is a history table, if you will, that holds the
history of any modifications to the client information.

You will notice there is not a reference from the clients table to the
clientInfo_editHistory.
My position is that the clientid column in the clients table should
reference the createdFor column in the clientInfo_editHistory table.
Our database designer says no that just because a column in one table hold
the same data as the column in the other table is no reason to reference the
two tables.

By reference I mean that the primary key in the clients table should
reference the foreign key in the clientInfo_editHistory table.

I have included the DDL as it currently stands but if I get my way it will
soon change.

Thanks in advance for all the input.

Gary

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[clients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[clients]
GO

CREATE TABLE [dbo].[clients] (
 [clientId] [int] IDENTITY (1, 1) NOT NULL ,
 [clientStatus] [int] NOT NULL ,
 [clientType] [int] NOT NULL ,
 [createdOn] [datetime] NOT NULL ,
 [f_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [m_name] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [l_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[clients] WITH NOCHECK ADD
 CONSTRAINT [PK_clients] PRIMARY KEY  CLUSTERED
 (
  [clientId]
 )  ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[clientInfo_editHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[clientInfo_editHistory]
GO

CREATE TABLE [dbo].[clientInfo_editHistory] (
 [editHistoryId] [int] IDENTITY (1, 1) NOT NULL ,
 [createdBy] [int] NOT NULL ,
 [createdFor] [int] NOT NULL ,
 [createdOn] [datetime] NOT NULL ,
 [f_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [m_name] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [l_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [birthday] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [editComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[clientInfo_editHistory] WITH NOCHECK ADD
 CONSTRAINT [PK_clientInfo_editHistory] PRIMARY KEY  CLUSTERED
 (
  [editHistoryId]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[clientInfo_editHistory] WITH NOCHECK ADD
 CONSTRAINT [DF_clientInfo_editHistory_createdOn] DEFAULT (getdate()) FOR
[createdOn]
GO



Wed, 22 Sep 2004 07:57:32 GMT
 Please correct me if I am wrong.

Hi Gary..

In different scenarios, this may or may not be a good idea. A couple of
suggestions:

If your app/s have the functionality to display history on clients (which
apps sometimes do) then DRI between these tables is as important as anywhere
else in the database.

If apps don't use the history and insert performance is a concern, then
removing the DRI is a good idea..

HTH

Cheers,
Greg Linwood


Quote:

> Please correct me if I am wrong.
> In this example there are two tables, clients, and clientInfo_editHistory.
> The clients table holds all clients who have registered from the web site
> and all clients who have been bulked loaded into the system.
> The clientInfo_editHistory is a history table, if you will, that holds the
> history of any modifications to the client information.

> You will notice there is not a reference from the clients table to the
> clientInfo_editHistory.
> My position is that the clientid column in the clients table should
> reference the createdFor column in the clientInfo_editHistory table.
> Our database designer says no that just because a column in one table hold
> the same data as the column in the other table is no reason to reference
the
> two tables.

> By reference I mean that the primary key in the clients table should
> reference the foreign key in the clientInfo_editHistory table.

> I have included the DDL as it currently stands but if I get my way it will
> soon change.

> Thanks in advance for all the input.

> Gary

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[clients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[clients]
> GO

> CREATE TABLE [dbo].[clients] (
>  [clientId] [int] IDENTITY (1, 1) NOT NULL ,
>  [clientStatus] [int] NOT NULL ,
>  [clientType] [int] NOT NULL ,
>  [createdOn] [datetime] NOT NULL ,
>  [f_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [m_name] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [l_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[clients] WITH NOCHECK ADD
>  CONSTRAINT [PK_clients] PRIMARY KEY  CLUSTERED
>  (
>   [clientId]
>  )  ON [PRIMARY]
> GO

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[clientInfo_editHistory]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[clientInfo_editHistory]
> GO

> CREATE TABLE [dbo].[clientInfo_editHistory] (
>  [editHistoryId] [int] IDENTITY (1, 1) NOT NULL ,
>  [createdBy] [int] NOT NULL ,
>  [createdFor] [int] NOT NULL ,
>  [createdOn] [datetime] NOT NULL ,
>  [f_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [m_name] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [l_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [birthday] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [editComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[clientInfo_editHistory] WITH NOCHECK ADD
>  CONSTRAINT [PK_clientInfo_editHistory] PRIMARY KEY  CLUSTERED
>  (
>   [editHistoryId]
>  )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[clientInfo_editHistory] WITH NOCHECK ADD
>  CONSTRAINT [DF_clientInfo_editHistory_createdOn] DEFAULT (getdate()) FOR
> [createdOn]
> GO



Wed, 22 Sep 2004 09:46:46 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Please tell me that i am wrong

2. what am I doing wrong? please help

3. SQL and Date format - Am I correct?

4. Am I correct when I say........

5. What am I forgetting or doing wrong

6. what am I doing wrong in this procedure?

7. TSQL question - what am I doing wrong?

8. what am i doing wrong?

9. Simple one what am i doing wrong

10. Darn what am i doing wrong?

11. What am I doing wrong

12. What am I doing wrong?


 
Powered by phpBB® Forum Software