Best way to create the relationship

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best way to create the relationship

Author

Topic

dynamorevy
Starting Member

6 Posts

Posted - 2008-03-28 : 05:17:49

Hi - SQL beginer here....

SQL2005

I have a table dbo.server

Compid - 1
Name PK - Server1
Make - HP
etc...

I have just created a new table dbo.ProcessorInfo with the following columns:

Name
BrandName
ProcessorCoreCount
etc....

This table will have more than 1 record for each name:

Server1 Intel 2
Server1 Intel 2
Server2 Intel 1
Server2 Intel 1

and so on.

What is the best way to relate the name in dbo.server and name in dbo.ProcessorInfo so this is a one to many? Obviously I can't set the name column as a PK as I have more than 1 record of the same value in it.

Help much appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 05:20:41

crete table processortype (proctypeid int identity(1, 1), proctypename varchar(20))

Create table ProcessorInfo (
CompID INT,
proctypeid int,
corecount tinyint
)

and make foreign keys contraints between the three tables.

E 12°55'05.25"
N 56°04'39.16"

dynamorevy
Starting Member

6 Posts

Posted - 2008-03-28 : 07:30:01

OK - I now have:

dbo.processortype

proctypename varchar(20) Checked
proctypeid int Unchecked (Identity spec yes, 1,1)

dbo.ProcessorInfo

CompID int Checked
proctypeid int Checked
corecount tinyint Checked
Name char(25) Checked
BrandName nvarchar(255) Checked
ProcessorCoreCount float Checked
ProcessorCurrentSpeed float Checked
ProcessorFamily nvarchar(255) Checked

dbo.server

compID int Unchecked
Name char(25) Unchecked (PK)

Is the above correct for this to work? Do I need to set any other primary keys? Please advise on setting the foreign keys..

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 07:53:31

[code]CREATE TABLE dbo.Servers
(
ServerID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ServerName VARCHAR(20) NOT NULL
)

CREATE TABLE dbo.ProcTypes
(
ProcTypeID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ProcTypeName VARCHAR(20) NOT NULL,
ProcTypeFamily VARCHAR(20) NOT NULL
)

CREATE TABLE dbo.ProcessorInfo
(
InfoID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ServerID INT NOT NULL,
ProcTypeID INT NOT NULL,
CoreCount TINYINT NOT NULL,
CoreSpeed FLOAT NOT NULL
)

ALTER TABLE dbo.ProcessorInfo
WITH CHECK
ADD CONSTRAINT FK_ProcessorInfo_ProcTypes
FOREIGN KEY (
ProcTypeID
)
REFERENCES dbo.ProcTypes
(
ProcTypeID
)

ALTER TABLE dbo.ProcessorInfo
CHECK
CONSTRAINT FK_ProcessorInfo_ProcTypes

ALTER TABLE dbo.ProcessorInfo
WITH CHECK
ADD CONSTRAINT FK_ProcessorInfo_Servers
FOREIGN KEY (
ServerID
)
REFERENCES dbo.Servers
(
ServerID
)

ALTER TABLE dbo.ProcessorInfo
CHECK
CONSTRAINT FK_ProcessorInfo_Servers[/code]And then again, you can further normalize the ProcFamily information.

See http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources