Solved

When to separate tables and create relationships?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now