smacca
asked on
When to separate tables and create relationships?
I have the following situation:
* I have many tables that store the following information:
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[title] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[firstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[lastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[dob] [smalldatetime] NULL ,
[company] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[companyDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[jobTitle] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[industry] [int] NULL ,
[address1] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[address2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[suburb] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[state] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[country] [int] NOT NULL ,
[zipCode] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[homePhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[workPhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fax] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[mobile] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[url] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
Should I create a common table (tblContactInfo) that stores all this information and simply use relationships to access it?
Please consider this in the following context:
* If you use relationships to access the information, there is no way of determining which fields from the contact info table are wanted. Therefore in ALL my joins I will have to include ALL the information from the contacts table which is far to excessive.
My thoughts are if I really want to get this information on aggregate I can create a stored procedure that can go through and collect/retrieve all information and give it back.
This is a MAJOR DESIGN DECISION FOR A LARGE PROJECT so I would really like some EXPERIENCED, EXPERT ADVICE here.
Some example tables:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblComp etition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompetition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCont actInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContactInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEmpl oyees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEmployees]
GO
CREATE TABLE [dbo].[tblContactInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[title] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[firstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[lastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[dob] [smalldatetime] NULL ,
[company] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[companyDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[jobTitle] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[industry] [int] NULL ,
[address1] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[address2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[suburb] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[state] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[country] [int] NOT NULL ,
[zipCode] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[homePhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[workPhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fax] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[mobile] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[url] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblEmployees] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[department] [int] NOT NULL ,
[startDate] [smalldatetime] NOT NULL ,
[contact] [int] NOT NULL ,
[salary] [smallmoney] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblCompetition] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[contact] [int] NOT NULL ,
[comments] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompetition] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompetition] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContactInfo] WITH NOCHECK ADD
CONSTRAINT [PK_tblContacts] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEmployees] WITH NOCHECK ADD
CONSTRAINT [PK_tblEmployees] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompetition] ADD
CONSTRAINT [DF_tblCompetition_publish Date] DEFAULT (getdate()) FOR [publishDate]
GO
ALTER TABLE [dbo].[tblContactInfo] ADD
CONSTRAINT [DF_tblContacts_publishDat e] DEFAULT (getdate()) FOR [publishDate]
GO
ALTER TABLE [dbo].[tblEmployees] ADD
CONSTRAINT [DF_tblEmployees_publishDa te] DEFAULT (getdate()) FOR [publishDate],
CONSTRAINT [DF_tblEmployees_startDate ] DEFAULT (getdate()) FOR [startDate]
GO
* I have many tables that store the following information:
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[title] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[firstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[lastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[dob] [smalldatetime] NULL ,
[company] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[companyDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[jobTitle] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[industry] [int] NULL ,
[address1] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[address2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[suburb] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[state] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[country] [int] NOT NULL ,
[zipCode] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[homePhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[workPhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fax] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[mobile] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[url] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
Should I create a common table (tblContactInfo) that stores all this information and simply use relationships to access it?
Please consider this in the following context:
* If you use relationships to access the information, there is no way of determining which fields from the contact info table are wanted. Therefore in ALL my joins I will have to include ALL the information from the contacts table which is far to excessive.
My thoughts are if I really want to get this information on aggregate I can create a stored procedure that can go through and collect/retrieve all information and give it back.
This is a MAJOR DESIGN DECISION FOR A LARGE PROJECT so I would really like some EXPERIENCED, EXPERT ADVICE here.
Some example tables:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblComp
drop table [dbo].[tblCompetition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCont
drop table [dbo].[tblContactInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEmpl
drop table [dbo].[tblEmployees]
GO
CREATE TABLE [dbo].[tblContactInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[title] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[firstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[lastName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[dob] [smalldatetime] NULL ,
[company] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[companyDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[jobTitle] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[industry] [int] NULL ,
[address1] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[address2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[suburb] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[state] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[country] [int] NOT NULL ,
[zipCode] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[homePhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[workPhone] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[fax] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[mobile] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[url] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblEmployees] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[department] [int] NOT NULL ,
[startDate] [smalldatetime] NOT NULL ,
[contact] [int] NOT NULL ,
[salary] [smallmoney] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblCompetition] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[publishDate] [smalldatetime] NOT NULL ,
[contact] [int] NOT NULL ,
[comments] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompetition] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompetition] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContactInfo] WITH NOCHECK ADD
CONSTRAINT [PK_tblContacts] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEmployees] WITH NOCHECK ADD
CONSTRAINT [PK_tblEmployees] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompetition] ADD
CONSTRAINT [DF_tblCompetition_publish
GO
ALTER TABLE [dbo].[tblContactInfo] ADD
CONSTRAINT [DF_tblContacts_publishDat
GO
ALTER TABLE [dbo].[tblEmployees] ADD
CONSTRAINT [DF_tblEmployees_publishDa
CONSTRAINT [DF_tblEmployees_startDate
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you explain this a bit please?