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
Solved

When to separate tables and create relationships?

Posted on 2004-09-03
2
203 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
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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