Solved

When to separate tables and create relationships?

Posted on 2004-09-03
2
212 Views
Last Modified: 2008-02-01
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
Comment
Question by:smacca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
nexusSam earned 350 total points
ID: 11970892
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
 
LVL 11

Expert Comment

by:Otana
ID: 11971400
* 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question