[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

When to separate tables and create relationships?

Posted on 2004-09-03
2
Medium Priority
?
219 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 700 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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