• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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].[tblCompetition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompetition]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEmployees]') 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_publishDate] DEFAULT (getdate()) FOR [publishDate]
GO

ALTER TABLE [dbo].[tblContactInfo] ADD
      CONSTRAINT [DF_tblContacts_publishDate] DEFAULT (getdate()) FOR [publishDate]
GO

ALTER TABLE [dbo].[tblEmployees] ADD
      CONSTRAINT [DF_tblEmployees_publishDate] DEFAULT (getdate()) FOR [publishDate],
      CONSTRAINT [DF_tblEmployees_startDate] DEFAULT (getdate()) FOR [startDate]
GO


 
0
smacca
Asked:
smacca
1 Solution
 
nexusSamCommented:
Initial thought is to at least seperate company info into its own table and link it as this is a logical one to many relationship (presumeably, you can have more than one contact within the same company?) so you want to avoid repeating company details (industry, etc) for each contact
There doesn't seem to be other logical sub-entities within your big table to require subdivision, though if you tend to report on / update only a small portion of the columns, you can create a one-2-one relationship between, say, tblContactInfo and tblContactDetail (partition vertically).
0
 
OtanaCommented:
* 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.

Could you explain this a bit please?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now